使用Vb.net中的一个IN子句，使用SQL将某些内容保存到数据库中。

I have a textbox and a button on a form.

我在表格上有一个文本框和一个按钮。

I wish to run a query (in Vb.Net) that will produce a query with the IN Values.

我希望运行一个查询(在Vb.Net中)，该查询将生成一个带有in值的查询。

Below is an example of my code

下面是我的代码示例

myConnection = New SqlConnection("Data Source=sqldb\;Initial Catalog=Rec;Integrated Security=True")
myConnection.Open()
myCommand = New SqlCommand("UPDATE dbo.Recordings SET Status = 0 where RecID in ('" & txtRecID.Text & "') ", myConnection)
ra = myCommand.ExecuteNonQuery()
myConnection.Close()
MsgBox("Done!", _
MsgBoxStyle.Information, "Done")

When I enter a single value it works but when I enter values with commas it throws an error:

当我输入一个值时，它可以工作，但当我输入值时，它会抛出一个错误:

"Conversion failed when converting the varchar value '1234,4567' to data type int."

“当将varchar值'1234,4567'转换为数据类型int时，转换失败。”

Could someone please help me to solve this or if there is an alternative way?

有人能帮我解决这个问题吗?或者有别的办法吗?

Many Thanks

非常感谢

2 个解决方案

#1

Try removing the single-quotes you're wrapping the IN values in:

试着去掉你所包装的单引号:

myCommand = New SqlCommand("UPDATE dbo.Recordings SET Status = 0 WHERE RecID IN (" & txtRecID.Text & ") ", myConnection)

#2

If you were testing a STRING variable for multiple values, then those multiple values would need to be in quotes.

如果您正在为多个值测试一个字符串变量，那么这些多个值将需要用引号括起来。

The reason your first attempt 'almost' worked was because you could also have generated your SqlCommand with each individual value in quotes. I.E.
UPDATE dbo.Recordings SET Status = 0 where RecID IN ('1234', '5678')

第一次尝试“almost”成功的原因是您还可以生成带有引号的每个单独值的SqlCommand。即更新dbo。记录设置状态为0，其中RecID为('1234'，'5678')

In that case, T-SQL would have done an implicit conversion of each of the string values to INT, which is what it was attempting to do when you gave it '1234, 5678' but that isn't decipherable as an INT.

在这种情况下，T-SQL会隐式地将每个字符串值转换为INT，这就是当你输入1234 5678时它试图做的事情，但那不是作为INT类型的。

#1