vb.net sql parameter incorrect syntax near 'value'

Question

I'm new here, I'm trying to convert an integer into varbinary to insert into an already made SQL table. I've included the code, I get incorrect syntax near "523641" which is the HOUSE_ID I am trying to convert.

I also converted the int to byte array and added a parameter to the command but same result

Dim varbin As String = " convert(varbinary, '" & houseid & "')"
obj = objCon.CreateCommand()
strSQL = "insert into " & tbl & " (hello, HOUSE_ID, world) VALUES ('" & hello & "','" & varbin & "','" & world & "')"
obj.CommandText = strSQL
obj.ExecuteNonQuery()

Expected result is putting that 523641 into the varbinary(50) column.

You are putting single quotes around the varbin variable, which itself is a call to CONVERT. But rather than making that change, you should seriously look into using a prepared statement. — Tim Biegeleisen
– Tim Biegeleisen, Commented Mar 24, 2019 at 10:08
table name: tbl columns: hello: varchar(50), null HOUSE_ID: varbinary(50), null world: varchar(50), null i'm giving an example cause the table is too big what if you have an int and you want to insert it in an sql column which is varbinary? — OctaCode
– OctaCode, Commented Mar 24, 2019 at 10:09
Print out your strSQL string to the console, and you should immediately see the problem. — Tim Biegeleisen
– Tim Biegeleisen, Commented Mar 24, 2019 at 10:12

Dan Guzman · Accepted Answer · 2019-03-24 10:45:52Z

1

Not sure why you would want to store an integer in a varbinary column but you can use BitConverter along with a parameterized query. Always use parameters instead of string concatenation for values that vary by execution as parameters have a number of benefits for security, performance, and ease of use.

Dim varbin As Byte() = BitConverter.GetBytes(houseid)
obj = objCon.CreateCommand()
strSQL = "insert into " & tbl & " (hello, HOUSE_ID, world) VALUES (@hello, @varbin, @world);"
obj.Parameters.Add("@hello", SqlDbType.VarChar, 50).Value = hello
obj.Parameters.Add("@varbin", SqlDbType.VarBinary, 50).Value = varbin
obj.Parameters.Add("@world", SqlDbType.VarChar, 50).Value = world
obj.CommandText = strSQL
obj.ExecuteNonQuery()

answered Mar 24, 2019 at 10:45

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

OctaCode Over a year ago

would like to make both answers useful but i can't since i'm new, but thank you both and have a great day :)

Dan Guzman Over a year ago

@DCON, you should have enough cred to mark answers. See What should I do when someone answers my question?.

Collectives™ on Stack Overflow

vb.net sql parameter incorrect syntax near 'value'

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related