Dynamically Binding parameters to push to SQL Server-CodePudding

I have a lot of historical data I'm pushing to SQL. As a stop gap I'm coding this in VBA first. I open the .xlsx file, put the headers into an array to determine what SQL table the data goes into. Then I'm using solution #3 from INSERT INTO statement from Excel to SQL Server Table using VBA to base my SQL string on. I'm throwing an automation error at the .parameters.append line. Is there another way to dynamically append the parameters? Or is this just incorrect syntax? I appreciate any help!

Code:

'creates db connection
Set conn = CreateObject("ADODB.Connection")
With conn
    .Provider = "Microsoft.ACE.OLEDB.12.0;"
    .ConnectionString = "Data Source=" & wbk.FullName & ";" & "Excel 8.0;HDR=Yes;IMEX=0;Mode=ReadWrite;"
    .Open
End With

sqlStr = "INSERT INTO DB_Name." & tblname & " ("

For i = 1 To UBound(hdrary)
    If i <> UBound(hdrary) Then
        sqlStr = sqlStr & hdrary(i, 1) & ", "
    Else
        sqlStr = sqlStr & hdrary(i, 1) & ") VALUES ("
    End If
Next i

For i = 1 To UBound(hdrary)
    If i <> UBound(hdrary) Then
        sqlStr = sqlStr & "?, "
    Else
        sqlStr = sqlStr & "?)"
    End If
Next i

'Statement follows this example:
'strSQL = "INSERT INTO " & Database_Name & ".[dbo]." & Table_Name & _
'         "    ([Audit], [Audit Type], [Claim Received Date], [Date Assigned], [Date Completed]," & _
'         "     [Analyst], [Customer], [ID], [Affiliate], [Facility], [DEA], [Acct Number], [Wholesaler]," & _
'         "     [Vendor], [Product], [NDC], [Ref], [Claimed Contract], [Claimed Contract Cost]," & _
'         "     [Contract Price Start Date], [Contract Price End Date], [Catalog Number], [Invoice Number], [Invoice Date]," & _
'         "     [Chargeback ID], [Contract Indicator], [Unit Cost],[WAC], [Potential Credit Due]," & _
'         "     [Qty], [Spend],[IP-DSH indicator Y/N], [DSH and/or HRSA Number], [Unique GPO Code]," & _
'         "     [Comment],[ResCode],[Correct Cost],[CRRB CM],[CRRB Rebill],[CRRB Date])" & _
'         " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?," _
'         "         ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

lastrow = wks.Cells(Rows.count, "a").End(xlUp).Row

sys.Close

For i = 2 To lastrow
    Set cmd = CreateObject("ADODB.Command")

    With cmd
      .ActiveConnection = conn   ' CONNECTION OBJECT
      .CommandText = sqlStr        ' SQL STRING
      .CommandType = adCmdText

      ' BINDING PARAMETERS
        For j = 1 To UBound(hdrary)
            .Parameters.Append .CreateParameter("s" & hdrary(j, 1), adVarChar, adParamInput, 255, wks.Cells(i, j))
            .Execute
        Next j
    End With

    Set cmd = Nothing
Next i

UPDATED: Based on @joel-coehoorn's answer, I updated the command and deleted the wbk.close. I'm throwing a "Item cannot be found in the collection corresponding to the requested name or ordinal." on the line cmd.Parameters(j).Value = wks.Cells(i, j).Value

'create command object
Set cmd = CreateObject("ADODB.Command")

With cmd
    .ActiveConnection = conn   ' CONNECTION OBJECT
    .CommandText = sqlStr        ' SQL STRING
    .CommandType = adCmdText
End With

'pre-binds parameters
For j = 1 To UBound(hdrary)
    Set k = cmd.CreateParameter(Chr(34) & hdrary(j, 1) & Chr(34), adVarChar, adParamInput, 255)
    cmd.Parameters.Append k
Next j

'loops through mm worksheet by row
For i = 2 To lastrow

    'SET PARAMETER VALUES
    For j = 1 To UBound(hdrary)
        cmd.Parameters(j).Value = wks.Cells(i, j).Value
    Next j
    
    'RUN THE SQL COMMAND
    cmd.Execute
Next i

Set cmd = Nothing

CodePudding user response：

My VBA is more than a little rusty, so there's likely a mistake in the above, but I do believe this will get you to a better place.

That disclaimer out of the way, a . is just another binary operator, and so I think the space in

.Parameters.Append .CreateParameter

is not doing all the work you think it is, in that it's not equivalent to

cmd.Parameters.Append cmd.CreateParameter

but rather

cmd.Parameters.Append.CreateParameter

which of course is not a thing.

You probably need to do something like this instead:

Dim p
For j = 1 To UBound(hdrary)
    Set p = .CreateParameter("s" & hdrary(j, 1), adVarChar, adParamInput, 255, wks.Cells(i, j))
    .Parameters.Append p
Next j
.Execute

Note we don't call .Execute until we finish creating all the parameters.

Additionally, this code is itself inside a loop. You really don't need to recreate cmd or all those parameters again on every loop iteration. Rather, you should create the command and parameters once, and then only update the parameters' .Value properties inside the loop.

' ...

sys.Close
wbk.Close 

Set cmd = CreateObject("ADODB.Command")
cmd.ActiveConnection = conn   ' CONNECTION OBJECT
cmd.CommandText = sqlStr        ' SQL STRING
cmd.CommandType = adCmdText

' PRE-BINDING PARAMETERS
Dim p
For j = 1 To UBound(hdrary)
    Set p = cmd.CreateParameter("s" & hdrary(j, 1), adVarChar, adParamInput, 255)
    cmd.Parameters.Append p
Next j

' LOOP THROUGH EACH ROW
For i = 2 To lastrow

    'SET PARAMETER VALUES
    For j = 1 To UBound(hdrary)   
        cmd.Parameters(j).Value = wks.Cells(i, j)
    Next j
    
    'RUN THE SQL COMMAND
    cmd.Execute
Next i
Set cmd = Nothing