sql - VBA - 执行 ADODB.CommandText

Question

我向自己保证我不会发布这个，因为我有一种妄想的想法，认为我太适合程序员了，但我们到了。

我已经更改了上周早些时候发布的内容，试图弄清楚如何编写一个 VBA 函数，该函数将数据从 Excel 范围写入 MS SQL 表。那行得通。

程序快结束时，不知道如何构造代码的最终执行；我已经尝试了所有使用 Command.Text在上层，将其设置为记录集，然后执行记录集，但没有什么能让这个小 VBA 巨魔高兴。这是我目前写的:

Sub Connection()
    Dim Tbl As String

    Dim InsertQuery As New ADODB.Command
    InsertQuery.CommandType = adCmdText

    Dim xlRow As Long, xlCol As Integer
    Dim DBconnection As New ADODB.Connection
    Dim ConnString As String
    Dim rst As New ADODB.Recordset
    Dim a As Integer, sFieldName As String
    Dim db As DAO.Database
    Dim CurrentDb As Database
    Dim ConnectionStr

    ConnectionStr = "Provider=sqloledb;Server="";Inital Catalog="";Integrated Security=SSPI;User ID="";Password="""

    DBconnection.Open ConnectionStr

    xlRow = 1   'only one row being used *as of now*, and that is the top row in the excel sheet
    xlCol = 119 'First column of misc. data
    While Cells(xlRow, xlCol) <> ""
        If LH = True Then
            Tbl = "Info.CaseBLH"
            InsertQuery.CommandText = "INSERT INTO " & Tbl & " VALUES('"                
        ElseIf RH = True Then
            Tbl = "Info.CaseBRH"
            InsertQuery.CommandText = "INSERT INTO " & Tbl & " VALUES('"
        Else
            MsgBox ("No available sheets")
            'Application.Quit        
        End If

        NK21Data.TableDefs(Tbl).Fields.Count

        For a = 1 To Fields.Count - 1
            'For xlCol = 119 To 230 'columns DO1 to HV1
            Fields.Item(a) = Replace(Cells(xlRow, xlCol), "'", "''") & "', '" 'Includes mitigation for apostrophes in the data

            If Cells(xlRow, xlCol) = "" Then
                rst.Fields.Item(a) = "NULL"
            End If

            xlCol = xlCol + 1
        Next a
        a = a + 1

        Fields.Item(a) = (Format(Now(), "M/D/YYYY") & "')" & vbCrLf)
    Wend

    'On Error GoTo ErrorHandler
    DBconnection.Execute (InsertQuery.CommandText)

    DBconnection.Close
    Set DBconnection = Nothing

    ErrorHandler:
    If Err.Number <> 0 Then
       Msg = "Error # " & Str(Err.Number) & " was generated by " _
       & Err.Source & Chr(13) & "Error Line: " & Erl & Chr(13) & Err.Description
       MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
    End If
End Sub

我得到的错误是:

Command text was not set for the command object.

此错误发生在:

DBconnection.Execute (InsertQuery.CommandText)

如果我尝试使用以下内容:

InsertQuery = DBconnection.Execute

我会收到以下错误:

Argument not optional

我已经为此工作了大约(给予或接受)三天，现在我正在做噩梦，所以如果有人可以帮助我弄清楚该怎么做，我将不胜感激。

Answer 1

我修复并清理了我之前的答案中的代码，测试了它的工作原理:



这是代码:

Option Explicit


Sub DoItThen()

    Dim i As Integer, sqlIns As String, sqlVals As String
    Dim InsertQuery As New ADODB.Command
    Dim firstRow As Long, firstCol As Integer, lastCol As Integer, currRow As Integer
    Dim DBconnection As New ADODB.Connection
    Dim ConnString As String

    ConnString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Example;Data Source=MYMACHINENAME"

    DBconnection.Open ConnString

    InsertQuery.ActiveConnection = DBconnection
    InsertQuery.CommandType = adCmdText

    ''build the command text side by side, named columns and values with param placeholders
    sqlIns = "INSERT INTO person("
    sqlVals = " VALUES("

    ''i could work these out by scanning the sheet i suppose. hardcoded for now
    firstRow = 2
    firstCol = 3
    lastCol = 5

    ''generate the SQL - its this that lets the column names come in any order in the sheet
    For i = firstCol To lastCol
        sqlIns = sqlIns & Cells(firstRow, i) & ","
        sqlVals = sqlVals & "?,"
        InsertQuery.Parameters.Append InsertQuery.CreateParameter("p" & i - firstCol, adVarChar, adParamInput, 255)
    Next i

    ''chop off the extra trailing commas and form a syntax correct command
    InsertQuery.CommandText = Left$(sqlIns, Len(sqlIns) - 1) & ")" & Left$(sqlVals, Len(sqlVals) - 1) & ")"

    ''iterate the data part of the sheet and execute the query repeatedlty
    currRow = firstRow + 1
    While Cells(currRow, firstCol) <> ""

       For i = firstCol To lastCol
           InsertQuery.Parameters("p" & i - firstCol).Value = Cells(currRow, i)
       Next i

       InsertQuery.Execute , , adExecuteNoRecords ''dont return a resultset

       currRow = currRow + 1
    Wend


    DBconnection.Close
    Set DBconnection = Nothing

ErrorHandler:
    If Err.Number <> 0 Then
       MsgBox Err.Description
    End If
End Sub

它将第一行作为 db 表中列的名称 - 任何顺序都可以

它构建一个命令并填充参数

它重复填充值并执行查询，填充表