Oracle SQL Looping VBA for number不起作用

oxosxuxt 于 2023-03-29 发布在 Oracle

关注(0)|答案(1)|浏览(133)

因此，我们有一个简单的查询，我们可以将8位数字的列格式化为CRN值，这很好，但是如果将数字放在Excel工作表中并尝试在VBA中运行循环查询，我们不会得到任何结果。
我做错了什么或错过了什么？

Dim DB_CONNECTION           As ADODB.Connection
Dim DB_RECORDSET            As ADODB.Recordset
Dim CELLADDRESS             As Variant
Dim CRN                     As Variant
Dim ANSWER                  As String
Dim SQL_STRING              As String

Set DB_CONNECTION = New ADODB.Connection
Set DB_RECORDSET = New ADODB.Recordset


DB_CONNECTION.ConnectionString = "Driver={Oracle in OraClient18Home1_32bit}; Dbq=XXXXXX; Uid=XXXXX; Pwd=XXXXXX;QTO=F;"

DB_CONNECTION.Open

Sheets("Data").Activate

Range("A6").Select

Do Until IsEmpty(ActiveCell)

CELLADDRESS = ActiveCell.Address
CRN = ActiveCell.Value

SQL_STRING = ""
SQL_STRING = SQL_STRING + " SELECT CUSTIMA.BCUSTCLS.U##CLASS_CODEC"
SQL_STRING = SQL_STRING + " FROM CUSTIMA.BCUSTCLS"
SQL_STRING = SQL_STRING + " WHERE CUSTIMA.BCUSTCLS.U##CUST_REF = 'CRN'"
SQL_STRING = SQL_STRING + " AND CUSTIMA.BCUSTCLS.U##CLASS_CODEC = 'DO' "

DB_RECORDSET.Open SQL_STRING, DB_CONNECTION

Sheets("Data").Range(CELLADDRESS).Offset(0, 2).CopyFromRecordset DB_RECORDSET

        DB_RECORDSET.Close

        ActiveCell.Offset(1, 0).Select

Loop
End Sub

oracle

来源：https://stackoverflow.com/questions/75818171/oracle-sql-looping-vba-for-number-not-working

1条答案

按热度按时间

hivapdat1#

类似这样的方法应该可以奏效：

Sub RunQueries()
    Dim DB_CONNECTION As ADODB.Connection, DB_RECORDSET As ADODB.Recordset
    Dim c As Range, SQL_STRING As String
    
    Set DB_CONNECTION = New ADODB.Connection
    Set DB_RECORDSET = New ADODB.Recordset
    DB_CONNECTION.ConnectionString = "Driver={Oracle in OraClient18Home1_32bit}; " & _
                                     "Dbq=XXXXXX; Uid=XXXXX; Pwd=XXXXXX;QTO=F;"
    DB_CONNECTION.Open
    
    Set c = ThisWorkbook.Sheets("Data").Range("A6")
    
    Do While Len(c.Value) > 0
    
        SQL_STRING = " SELECT CUSTIMA.BCUSTCLS.U##CLASS_CODEC " & _
                     " FROM CUSTIMA.BCUSTCLS " & _
                     " WHERE CUSTIMA.BCUSTCLS.U##CUST_REF = '" & c.Value & "' " & _
                     " AND CUSTIMA.BCUSTCLS.U##CLASS_CODEC = 'DO' "
    
        DB_RECORDSET.Open SQL_STRING, DB_CONNECTION
        If Not DB_RECORDSET.EOF Then
            c.Offset(0, 2).Value = DB_RECORDSET.Fields(0).Value
        Else
            c.Offset(0, 2).Value = "{no match}"
        End If
        
        DB_RECORDSET.Close
        Set c = c.Offset(1)
    Loop
End Sub

赞(0）回复(0）举报 2023-03-29

我来回答

Oracle SQL Looping VBA for number不起作用

1条答案

相关问题

热门标签

最新问答