title: Dynamically Binding parameters to push to SQL Server
body: <p>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 <code>.xlsx</code> file, put the headers into an array to determine what SQL table the data goes into. Then I'm using solution #3 from <a href="https://stackoverflow.com/questions/52897825/insert-into-statement-from-excel-to-sql-server-table-using-vba">INSERT INTO statement from Excel to SQL Server Table using VBA</a> to base my SQL string on. I'm throwing an automation error at the <code>.parameters.append</code> line. Is there another way to dynamically append the parameters? Or is this just incorrect syntax? I appreciate any help!</p>
  <p>Code:</p>
  <pre><code>'creates db connection
  Set conn = CreateObject(&quot;ADODB.Connection&quot;)
  With conn
      .Provider = &quot;Microsoft.ACE.OLEDB.12.0;&quot;
      .ConnectionString = &quot;Data Source=&quot; &amp; wbk.FullName &amp; &quot;;&quot; &amp; &quot;Excel 8.0;HDR=Yes;IMEX=0;Mode=ReadWrite;&quot;
      .Open
  End With
  
  sqlStr = &quot;INSERT INTO DB_Name.&quot; &amp; tblname &amp; &quot; (&quot;
  
  For i = 1 To UBound(hdrary)
      If i &lt;&gt; UBound(hdrary) Then
          sqlStr = sqlStr &amp; hdrary(i, 1) &amp; &quot;, &quot;
      Else
          sqlStr = sqlStr &amp; hdrary(i, 1) &amp; &quot;) VALUES (&quot;
      End If
  Next i
  
  For i = 1 To UBound(hdrary)
      If i &lt;&gt; UBound(hdrary) Then
          sqlStr = sqlStr &amp; &quot;?, &quot;
      Else
          sqlStr = sqlStr &amp; &quot;?)&quot;
      End If
  Next i
  
  'Statement follows this example:
  'strSQL = &quot;INSERT INTO &quot; &amp; Database_Name &amp; &quot;.[dbo].&quot; &amp; Table_Name &amp; _
  '         &quot;    ([Audit], [Audit Type], [Claim Received Date], [Date Assigned], [Date Completed],&quot; &amp; _
  '         &quot;     [Analyst], [Customer], [ID], [Affiliate], [Facility], [DEA], [Acct Number], [Wholesaler],&quot; &amp; _
  '         &quot;     [Vendor], [Product], [NDC], [Ref], [Claimed Contract], [Claimed Contract Cost],&quot; &amp; _
  '         &quot;     [Contract Price Start Date], [Contract Price End Date], [Catalog Number], [Invoice Number], [Invoice Date],&quot; &amp; _
  '         &quot;     [Chargeback ID], [Contract Indicator], [Unit Cost],[WAC], [Potential Credit Due],&quot; &amp; _
  '         &quot;     [Qty], [Spend],[IP-DSH indicator Y/N], [DSH and/or HRSA Number], [Unique GPO Code],&quot; &amp; _
  '         &quot;     [Comment],[ResCode],[Correct Cost],[CRRB CM],[CRRB Rebill],[CRRB Date])&quot; &amp; _
  '         &quot; VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,&quot; _
  '         &quot;         ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)&quot;
  
  lastrow = wks.Cells(Rows.count, &quot;a&quot;).End(xlUp).Row
  
  sys.Close
  
  For i = 2 To lastrow
      Set cmd = CreateObject(&quot;ADODB.Command&quot;)
  
      With cmd
        .ActiveConnection = conn   ' CONNECTION OBJECT
        .CommandText = sqlStr        ' SQL STRING
        .CommandType = adCmdText
  
        ' BINDING PARAMETERS
          For j = 1 To UBound(hdrary)
              .Parameters.Append .CreateParameter(&quot;s&quot; &amp; hdrary(j, 1), adVarChar, adParamInput, 255, wks.Cells(i, j))
              .Execute
          Next j
      End With
  
      Set cmd = Nothing
  Next i
  </code></pre>
  <p>UPDATED: Based on @joel-coehoorn's answer, I updated the command and deleted the <code>wbk.close</code>. I'm throwing a &quot;Item cannot be found in the collection corresponding to the requested name or ordinal.&quot; on the line <code>cmd.Parameters(j).Value = wks.Cells(i, j).Value</code></p>
  <pre><code>'create command object
  Set cmd = CreateObject(&quot;ADODB.Command&quot;)
  
  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) &amp; hdrary(j, 1) &amp; 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
  </code></pre>