What is wrong with this Update Query statement vb.net

Question

when I run this update query this error is occurred: System.InvalidCastException: 'Conversion from string "UPDATE Tbl_User_Privilges_Forms " to type 'Long' is not valid.'

"UPDATE Tbl_User_Privilges_Forms SET [F_View]= " & rw.Cells(4).Value & ", [F_Edit]= " & rw.Cells(5).Value & " Where [FormID] =" & rw.Cells(2).Value And "[UserID] =" & H

when I use with one Where condition it works fine but together with using "And" it gives the error.

The first major problem is that you are not using parameters — Ňɏssa Pøngjǣrdenlarp, Aug 19 '18 at 20:02
What is wrong? A lot, values not enclosed in single quotes, not using parameters, difficult to maintain code/formatting, Bad names, Inconsistent case, not using proper typed parameters, my opinions are worth what you paid for them. — Mark Schultheiss, Aug 20 '18 at 03:11

score 1 · Answer 1 · answered Aug 19 '18 at 23:53

The compiler thinks you want to do a bit-wise And operation, which can't be done on a string. Your intention is for the And operation to be part of the SQL query. Therefore it needs to be positioned inside double-quotes; not outside. Like so:

    "UPDATE Tbl_User_Privilges_Forms SET [F_View]= " & rw.Cells(4).Value & ", [F_Edit]= " & rw.Cells(5).Value & " Where [FormID] =" & rw.Cells(2).Value & " And [UserID] =" & H

And as already mentioned, best practice is to parameterize such operations, otherwise you may be laying out the red carpet for a SQL injection attack. Example here

jmcilhinney · Accepted Answer · 2018-08-20T02:40:16.303

You absolutely should be using parameters to insert values into SQL code, as has already been suggested. That said, any time you want to perform multiple concatenations like this, it is always best to use String.Format or string interpolation. You made this mistake because using lots of concatenation operators (&) makes your code hard to read. If you had used String.Format or string interpolation then it would have been impossible to put that And operator in the VB code instead of the SQL code:

String.Format("UPDATE Tbl_User_Privilges_Forms SET [F_View] = {0}, [F_Edit] = {1} WHERE [FormID] = {2} AND [UserID] = {3}",
              rw.Cells(4).Value,
              rw.Cells(5).Value,
              rw.Cells(2).Value,
              H)

or:

$"UPDATE Tbl_User_Privilges_Forms SET [F_View] = {rw.Cells(4).Value}, [F_Edit] = {rw.Cells(5).Value} WHERE [FormID] = {rw.Cells(2).Value} AND [UserID] = {H}"

Note that this does solve your issue but it's not a direct answer to the question of what you did wrong. Danski has provided an explanation of that so I haven't repeated it here. I would have posted this information in a comment but the volume of code I wanted to provide made that impractical.

What is wrong with this Update Query statement vb.net

2 Answers2