syntax for a string

[Rani]

the data type in the data base for the field order number is varchar 50.

on the page i have a session variable like so to grab the ordernumber
Session("id") = Session("ordernumber")

now when i want to query/update the database like so,
Dim sql1 As String = "update DDR set registrar = 1 where ccid = " & ds.Tables(0).Rows(0).Item("ccid") & " and ordernumber = " & Session("id")

if the data for the field ordernumber has 756 in the database just the number with out any special type character this works fine.

but one of the rows has the data like 12345-69 and i get the following error which is very obvious. but i can't fix the syntax. I am still on the learning curve. please help me with this. Thanks.

Syntax error converting the varchar value '13706-69' to a column of data type int.

 

[zekeman]

Long shot, try placing quotes around the session in the select

 

[Rani]

can you show me how to do that. I knew i had to place quotes around Session("id"). I don;t know how to.

Dim sql1 AsString = "update DDR set registrar = 1 where ccid = " & ds.Tables(0).Rows(0).Item("ccid") & " and ordernumber = " & Session("id")

 

[Rani]

Disregard the second request. i figured it out. thanks for your time.

 

[cjard]

the data type in the data base for the field order number is varchar 50.

on the page i have a session variable like so to grab the ordernumber
Session("id") = Session("ordernumber")

now when i want to query/update the database like so,
Dim sql1 AsString = "update DDR set registrar = 1 where ccid = " & ds.Tables(0).Rows(0).Item("ccid") & " and ordernumber = " & Session("id")

if the data for the field ordernumber has 756 in the database just the number with out any special type character this works fine.

but one of the rows has the data like 12345-69 and i get the following error which is very obvious. but i can't fix the syntax. I am still on the learning curve. please help me with this. Thanks.

Syntax error converting the varchar value '13706-69' to a column of data type int.

use parameterised queries..

if the data is 1234-56 then the database will see:

select * from tblWhatever where someField = (1234 - 56)

1234 minus 56 is 1178, so youll get the 1178 record if it exists.



Using parameterised queries solves all this, and should be used whenever possible.. failure to use them leaves your app prone to sql injection hacking, but on an even more ordinary level, if you dont use them than you cant even insert a word like o'clock into a database table without it breaking (or you having to write special handling code)

Simply put - you use and re-use variables in your code all the time; why not apply the same to database queries? (they are, after all, a programming lanugage)

 

[cjard]

Also, if youre concatenating a lot of strings you can make your code a lot tidier like this:

strSQL = String.Format("update tblTable set field1 = {0}, field2 = {1}, field3 = {2}", col1Value, col2Value, col3value)

note this is NOT a parameterised query (which in sql server looks more like this:

sqlCommand.CommandText = "update tblTable set field1 = @in_field1, field2 = @in_field2, field3 = @in_field3"
'after parameter creation code if it isnt done automatically
sqlCommand.Parameters("in_field1").Value = "a value"
sqlCommand.Parameters("in_field2").Value = "another value"
sqlCommand.Parameters("in_field3").Value = "some value"


the best thing?
you can reuse the statement just by setting new values and executing it again.. get used to doing this, and youll find stored procedures easier to deal with.