insert checkbox value into sql server

[mary555]

i am using a stored procedure from an asp page to insert values into a table. i have an attributes whose data type is 'bit'. i keep getting the error Incorrect syntax near the keyword 'on'.
when i go to insert this value into a table with the code:
SQLStmt.CommandText="InsertRecord '" & PC&"','"&Riv&"','"&Pl&"','"&Ge&"','"&Lo&"', '"&Ca&"', '"&Am&"', '"&Ty&"','"&SD&"','"&ED&"','"&OPH&"','"&OfPH&"','"&OPM&"','"&OfPM&"','"&EVTemp&"','"&AMTemp&"','"&CATemp&"','"&Cause&"','"&Amp&"','"&Eventt&"','"&Unit&"',"&Spill


the problem is the very last value being inserted. if i simply insert a 0 or 1, it doesn't give me the error but when 0 or 1 is in the variable then it doens't work

 

[Sheco]

for debugging only, do a response.write of the command text before you execute it... sometimes it isnt what you think it should be... perhaps there are quotes around your 1 or 0

 

[gmmastros]

Yes, bit fields expect 0 or 1. You could create another variable to represent this, and then use the variable in the sql statement.

Dim iSpill

If Spill = "on" Then
iSpill = 1
Else
iSpill = 0
End If

Then, change your query to use iSpill instead of Spill.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mary555]

hmm my command text is
.....h','DE','971',on,
im not sure where the extra comma at the end is coming from. george i will try your way, i thought of that but i was doing it wrong. ill let you know, thanks!

 

[XgrinderX]

I bet the problem is that you are expecting your checkbox value to be a 1 or 0 when in fact it will come across as "on" or "" instead. That is why you are getting the error saying it is near the keyword 'on'.

What you can do is something like this before creating that command text:

if trim(Spill) = "on" then
Spill = 1
else
Spill = 0
end if

 

[XgrinderX]

Hehe sorry about posting the same thing gmmastros did.

As for the extra comma, if you have two checkboxes with the same name, then it will pass their values as a comma separated list. So make sure this is not the case unless in fact it is what you want.