Dynamically Binding parameters to push to SQL Server

Question

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

How is wks defined? You close workbook at wbk.Close before parameter looping. — Parfait
– Parfait, Commented May 24, 2022 at 20:19

Joel Coehoorn · Accepted Answer · 2022-05-25 14:46:52Z

My VBA is more than a little rusty, so there's likely a mistake in here, 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

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-1).Value = wks.Cells(i, j)
    Next j
    
    'RUN THE SQL COMMAND
    cmd.Execute
Next i
Set cmd = Nothing

This makes a lot of sense. I have updated my code (i used k instead of p since I already had that variant available). I noticed I was referencing wks which was in a closed workbook. I removed the wbk.close statement. I am throwing an "Item cannot be found in the collection corresponding to the requested name or ordinal." I'll update the OP with the latest, Thank you so much
@ChrisH. it's possible cmd.Parameters is a 0-indexed collection.
using a zero based index populates the parameters in the collection with the correct values. That's awesome! Thank you!!! Google tells me that that error typically is a missing value/field in the database. So I'm looking there next to make sure the columns are what they are supposed to be.
Updated the answer to use the 0-index, since that was confirmed.

Chris H. · Accepted Answer · 2022-05-25 13:29:48Z

Thanks to @Joel-Coehoorn here is the final 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 & " ("

'puts in columns
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

'placeholders for VALUES
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
Debug.Print sqlStr

sys.Close

'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 worksheet by row
For i = 2 To lastrow

    'sets parameter values and accounts for 0 based array
    For j = 0 To cmd.Parameters.count - 1
        cmd.Parameters(j).Value = wks.Cells(i, j + 1).Value
    Next j
    
    'RUN THE SQL COMMAND
    cmd.Execute
Next i

Set cmd = Nothing

Collectives™ on Stack Overflow

Dynamically Binding parameters to push to SQL Server

2 Answers 2

4 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Linked

Related