Запрос SQL Server с использованием параметров для предложения IN

Я пытаюсь обновить, используя предложение IN, однако я не могу заставить его работать. My iArr создает список чисел, разделенных запятой, и теоретически, заключенный в скобки, он должен обновиться, однако он не работает. Я продолжаю получать ошибку

Не удалось выполнить преобразование значения nvarchar '111111, 222222, 333333' в тип данных int.

Любые идеи, как обойти это? Полный код ниже: 

    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 = _
            <SQL>
               UPDATE SCInv SET Inv_Transfer_Date = @UpdateCommand WHERE Inv_Num IN (@Array)
            </SQL>

    Dim cnt As New SqlConnection()
    Dim cmd As New SqlCommand()
    cnt.ConnectionString = "Data Source=SERVERNAME;Initial Catalog=CAT;User ID=USERID;Password=PASSWORD"
    Try
        cnt.Open()
        cmd.Connection = cnt
        cmd.CommandText = SQL_Arr
        cmd.Parameters.Clear()
        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
    cnt.Close()
    cnt.Dispose()
1 2
.net vb.net sql-server parameters in-clause

1 ответ:

Возможно, что SQL Server теперь поддерживает передачу массивов для параметров. Многие DBs этого не делают, и до сих пор существует открытый запрос на это в Microsoft Connect. Это стоило бы выяснить.

Вы также должны включить Option Strict. Ваш SQL_Arr объявлен как string, но вы назначаете ему XML-литерал, а затем передаете этот литерал в качестве текста команды.

Это создаст инструкцию SQL, в которой каждому элементу в списке Id будет присвоен свой собственный параметр, а затем его значение. настроился на это. Он использует некоторые случайные данные для идентификаторов, чтобы эмулировать изменяющийся список, и он использует MySQL...концепции будут те же самые.

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

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

' create the parameter placeholders
' results in a list of "@pX" items where X is a number
Dim params = Enumerable.Range(1, idList.Count).
            Select(Function(q) String.Format("@q{0}", q)).
            ToList()

' join the list of parms to make '@q1, @q2...'
' then replace @thelist with that string
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

        ' loop to supply an Id value for each IN parameter
        ' placeholder created
        For n As Int32 = 0 To params.Count - 1
            cmd.Parameters.Add(params(n), MySqlDbType.Int32).Value = idList(n)
        Next

        dbcon.Open()
        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
        dtSample.Load(cmd.ExecuteReader())
        dgv1.DataSource = dtSample
    End Using
End Using
Мы хотим, чтобы запрос оставался параметризованным, поэтому первая часть создает список заполнителей @q1 - столько, сколько идентификаторов нужно обновить. Чтобы создать заполнители в цикле: 
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()))
Next

Далее, код заменяет @theList в SQL на объединенную версию списка. Результат: 

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

Если бы предложение IN касалось с помощью строк, а не целых чисел, добавьте галочки к созданию параметров: String.Format("'@q{0}'",...

Остальное прямо: укажите идентификатор для каждого параметра: 

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

Мой образец просто установил значение a в -6, чтобы их было легко найти. Это работает:

Введите описание изображения здесь

Наконец, обратите внимание, что существует максимальная длина, разрешенная для результирующего SQL. Этот Макс зависит от поставщика БД и довольно велик, но если для вашего предложения IN Существует большое количество идентификаторов, вы можете хочу разбить его на партии.

1