excel - 将字符串参数附加到 ADODB 命令时出现问题

Question

我在将字符串参数附加到 VBA 中的命令对象时遇到问题。我的代码适用于我的 excel 工作簿中的所有非字符串数据类型，但在尝试将字符串值附加到命令参数时会引发以下错误文本：

“事务不能有多个具有此游标类型的记录集。更改游标类型、提交事务或关闭其中一个记录集。”

如果我使用“Usage_ID”命令并注释掉“Location”命令，此代码将成功运行。为什么是这样？为什么使用字符串会导致失败？

我正在将数据插入 Redshift 数据库，并且表列设置为使用我在下面的查询中使用的相同类型的数据 VARCHAR(256)。

Public Sub newInsert()
    Dim firstRow As Long
    Dim lastRow As Long
    Dim currentRow As Long
    Dim sh As Worksheet

    Set sh = ThisWorkbook.Worksheets("Sheet1")
    firstRow = 5
    lastRow = 5 '##Just using one row for the test##

    Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection
    conn.Open '##using a DSN file here##

    On Error GoTo CleanFail
    conn.BeginTrans

    Dim sql As String
    '## This is the full query I want to execute, 
    '   but it hangs, so I am running with one parameter
    '   at a time for testing. Usage_ID will run with the code below, but 
    '   not location. ##
    'sql = "INSERT INTO new_regions.bw_test (usage_id, location) " _
        '& "VALUES (?, ?)"

    sql = "INSERT INTO new_regions.bw_test (location) " _
         & "VALUES (?)"

    For currentRow = firstRow To lastRow
        Set cmd = New ADODB.Command
        Set cmd.ActiveConnection = conn
        cmd.CommandType = adCmdText
        cmd.CommandText = sql

        ' ## The first one will run successfully, not the second ##    
        'cmd.Parameters.Append cmd.CreateParameter("usage_id", adBigInt, adParamInput, , sh.Cells(1, "Q").value)
        cmd.Parameters.Append cmd.CreateParameter("location", adVarChar, adParamInput, 256, "test")

        cmd.Execute

    Next

    conn.CommitTrans

CleanExit:
    conn.Close
    Exit Sub

CleanFail:
    conn.RollbackTrans
    MsgBox Err.Description & vbNewLine & "Transaction was rolled back, no changes were made", vbExclamation
    Resume CleanExit

End Sub

标签： excelvbaamazon-redshift