Loop to insert multiple values into SQL Server
<p>I am trying to insert a lot of values into SQL Server in one SQL insert statement. So for example.. </p>

<pre><code>Insert Into someDataBase
Values(value1, value2, value3,..., value120, value121)
</code></pre>

<p>All these values will be in the correct order on a sheet in Excel. I already created a loop to go through all the 121 values but I can't think of a loop for the inserting the values without writing each value individually. </p>

<p>Can anyone help me out? </p>

<p>My code looks like this:</p>

<pre class="lang-vb prettyprint-override"><code>Sub AddRows()
' In VBE you need to go Tools References and check Microsoft Active X Data Objects 2.x library

Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String


Dim comm As ADODB.Command
Set comm = New ADODB.Command
Dim param As ADODB.Parameter
Dim getType As String

SQLStr = "Insert Into [DB] Values("
For i = 1 To 121
    'Get type
    getType = CellType(i, Cells(2, i))
    SQLStr = SQLStr &amp; "?,"
    Set param = New ADODB.Parameter
    param.Name = "Param" &amp; i
    Set param = comm.CreateParameter("param" &amp; i, getType, adParamInput, 1000)
    param.Attributes = adParamNullable
    Debug.Print (param.Type)
    comm.Parameters.Append param
Next i
SQLStr = Left(SQLStr, Len(SQLStr) - 1) &amp; ");"

Set Cn = New ADODB.Connection
Cn.CommandTimeout = 0
Cn.Open "Driver={SQL Server};Server=" &amp; Server_Name &amp; ";Database=" &amp; Database_Name &amp; _
";Uid=" &amp; User_ID &amp; ";Pwd=" &amp; Password &amp; ";"

comm.ActiveConnection = Cn
comm.CommandText = SQLStr
comm.CommandType = adCmdText


Dim test As String
lastrow = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row
'For x = 2 To lastrow
    For i = 1 To 121
        Debug.Print (Cells(2, i).Value)
        comm.Parameters.Item("Param" &amp; i).Value = Cells(2, i).Value
    Next
    comm.Execute
'Next x

Cn.Close
Set Cn = Nothing
</code></pre>

<p>End Sub</p>

<p>This is a portion of my CellType Function:</p>

<pre><code>Private Function CellType(Num) As MultipleValues
Application.Volatile
CellType.CellNum = 50
Select Case True
    Case Num = 1
        CellType.CellType = adInteger
    Case Num = 2
        CellType.CellType = adInteger
    Case Num = 3
        CellType.CellType = adVarWChar
        CellType.CellNum = 255
    Case Num = 4
        CellType.CellType = adCurrency
    Case Num = 5
        CellType.CellType = adVarWChar
        CellType.CellNum = 255
</code></pre>

<p>The values for the first 5 values are: 0,   null,   6071/1, 44.5,   Biltmore Fleur De Lis Collection Authentic Wrought Iron</p>

<p>It breaks on the second value which is a null but should be an integer.</p>