Using an IN clause in Vb.net to save something to the database using 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.
Below is an example of my c开发者_C百科ode
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."
Could someone please help me to solve this or if there is an alternative way?
Many Thanks
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)
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')
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.
精彩评论