Jamie Tilma Jamie Tilma - 1 month ago 11x
Vb.net Question

SQL Server query using Parameters for an IN Clause

I am trying to update using the

clause, however I can't seem to get it to work. My
creates a list of numbers separated with a comma and in theory once enclosed in brackets it should update however it doesn't appear to work. I keep getting an error

Conversion failed when converting nvarchar value '111111, 222222, 333333' to datatype int.

Any idea how to get round this? Full code below:

Dim iArr As String
iArr = ""
For i As Integer = 0 To ufReview.InvoiceGrid.Rows.Count - 1
iArr = iArr & ufReview.InvoiceGrid.Rows(i).Cells(0).Value & ", "
Next i
iArr = Left(iArr, Len(iArr) - 2)

Dim SQL_Arr As String = _
UPDATE SCInv SET Inv_Transfer_Date = @UpdateCommand WHERE Inv_Num IN (@Array)

Dim cnt As New SqlConnection()
Dim cmd As New SqlCommand()
cnt.ConnectionString = "Data Source=SERVERNAME;Initial Catalog=CAT;User ID=USERID;Password=PASSWORD"
cmd.Connection = cnt
cmd.CommandText = SQL_Arr
cmd.Parameters.AddWithValue("@UpdateCommand", UpdateCommand)
cmd.Parameters.AddWithValue("@Array", iArr)
Dim RACount As Integer = cmd.ExecuteNonQuery()
MsgBox("The following Summary Numbers are now marked as " & UpdateFeedback & ": " & vbCrLf & iArr _
& vbCrLf & vbCrLf & "(" & RACount & " row(s) affected)", vbOKOnly + vbInformation, "Tesseract")
Catch ex As Exception
MsgBox("Failed to update, please try again" & vbCrLf & vbCrLf & ex.Message, vbOKOnly + vbCritical, "SQL Error")
End Try


It is possible that SQL Server now supports passing arrays for parameters. Many DBs do not, and there is an open request on Microsoft Connect for this. It would be worth finding out.

You should also turn on Option Strict. Your SQL_Arr is declared as string, but you assign an XML literal to it, then pass that literal as the command text.

This will create a SQL statement where each item in the Id List is given its own parameter and then the value it set for it. It uses some random data for the Ids to emulate a varying list and it uses MySQL...the concepts will be the same.

' random set of IDs to update
Dim idList = Enumerable.Range(1, 25).OrderBy(Function(r) RNG.Next()).Take(8).ToList()

Dim sql As String = <sql>
            "UPDATE SampleX SET `Value`= @v WHERE Id IN (@theList)"

' create the parameter placeholders
Dim params As New List(Of String)
For n As Int32 = 0 To idList.Count - 1
    params.Add(String.Format("@q{0}", (n + 1).ToString()))

' expand @thelist to 'IN (@q1, @q2...)
sql = sql.Replace("@theList", String.Join(",", params))

' run the query
Using dbcon As New MySqlConnection(MySQLConnStr)
    Using cmd As New MySqlCommand(sql, dbcon)

        ' specify the value
        cmd.Parameters.Add("@v", MySqlDbType.Int32).Value = -6

        ' specify the Id values
        For n As Int32 = 0 To params.Count - 1
            cmd.Parameters.Add(params(n), MySqlDbType.Int32).Value = idList(n)

        Dim rows = cmd.ExecuteNonQuery()
    End Using

    ' debug/demo: show the results
    sql = "SELECT Name, Descr, Value, Country FROM SampleX WHERE Value=-6"
    Using cmd As New MySqlCommand(sql, dbcon)
        dtSample = New DataTable
        dgv1.DataSource = dtSample
    End Using
End Using

We want the query to remain parameterized, so the first part creates a list of "@q1" placeholders - as many of them as there are Ids to update. Then it replaces @theList in the SQL with the joined version of the list. The result:

"UPDATE SampleX SET `Value`= @v WHERE Id IN (@q1,@q2,@q3,@q4,@q5,@q6,@q7,@q8)"

If the IN clause was dealing with strings rather than integers, add ticks to the parameters creation: String.Format("'@q{0}'",...

The rest is straight forward: specify the Id for each parameter:

' params(n) like "q1"  and idList(n) = the actual value like 11111
cmd.Parameters.Add(params(n), MySqlDbType.Int32).Value = idList(n)

My sample just set the a value to -6 to make them easy to find. It works:

enter image description here