sql查询更新值在数组中的行 [英] sql query to update rows where value is in an array

问题描述

请帮我写一个查询来更新值在数组中的数据库字段：

这是我的代码，但是它给出了错误（arr1不是内置函数）

Please help me write a query to update database fields where the values are in an array:
this is my code but it gives error (arr1 not a built in function)

'get array items from listbox
        Dim arr1()
        ReDim arr1(ListBox1.Items.Count - 1)
        ListBox1.Items.CopyTo(arr1, 0)


        Dim query3 As String = "UPDATE   tblcand   SET votes = votes +1  where id = ( arr1() )"
        Using cmd1 As New SqlCommand(query3, con)
            con.Open()
            cmd1.ExecuteNonQuery()
            con.Close()
        End Using

如果在存储过程中使用它，我也会感激

谢谢

I Would also appreciate if its used in a stored procedure
Thanks

推荐答案

将你的=改为in子句。

然后使用string.join（）格式化查询的值。

Change your = to an in clause.
Then use string.join() to format the values for the query.

Dim arr1 As Array
ReDim arr1(ListBox1.Items.Count - 1)
ListBox1.Items.CopyTo(CType(arr1, Object()), 0)

Dim query3 As String = "UPDATE   tblcand   SET votes = votes +1  where id IN (SELECT * FROM SplitIntCsv(@IdsInArray))"
Using cmd1 As New SqlCommand(query3, con)
    cmd1.Parameters.AddWithValue("@IdsInArray", String.Join(",", arr1))
    con.Open()
    cmd1.ExecuteNonQuery()
    con.Close()
End Using

添加此功能以拆分CSV进入一张桌子。

Add this function to split your CSV into a table.

CREATE FUNCTION dbo.SplitIntCsv
(
    @String NVARCHAR(4000)
)
RETURNS TABLE 
AS
RETURN 
(
    WITH Split(stpos,endpos) 
    AS(
        SELECT 0 AS stpos, CHARINDEX(',',@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(',',@String,endpos+1)
            FROM Split
            WHERE endpos > 0
    )
    SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1))
    FROM Split
)

这篇关于sql查询更新值在数组中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！