Problem in building dynamic sql update statement

[Jack]

Hi,
I am trying to update database from asp. However, the finalupdate field is
not
working here. The finalupdate field is a Access Database field of type
yes/no. Any help/advise is appreciated. Regards

THE DYNAMIC SQL STATEMENT:

strsql = "UPDATE main SET main.FirstName ='" &l_firstname& "', " & _
"main.LastName = '" &l_lastname& "', " & _
"main.ColorPreference = '" &l_colorpreference& "', " & _
"main.FoodPreference = '" &l_foodpreference& "', " & _
"main.FinalUpdate = &l_finalupdate& where " & _
"main.SocialSecurityNumber)='" &l_ss& "'

THE TRANSLATED SQL STATEMENT:


UPDATE main SET main.FirstName ='Cocoa', main.LastName = 'Jones',
main.ColorPreference = 'Choco', main.FoodPreference = 'Lamb',
main.FinalUpdate = &l_finalupdate& where
main.SocialSecurityNumber)='222-22-2222'

 

[Ray Costanzo [MVP]]

You have your literal variable name in your SQL [non]query.

strsql = "UPDATE main SET main.FirstName ='" & l_firstname & "', " & _
"main.LastName = '" & l_lastname & "', " & _
"main.ColorPreference = '" & l_colorpreference & "', " &
_
"main.FoodPreference = '" & l_foodpreference & "', " &
_
"main.FinalUpdate = & " & CBool(l_finalupdate) & "
where " & _
"main.SocialSecurityNumber)='" & l_ss & "'

Also, notice I put spaces on either side of the & characters above. That is
a good habit to develop, because you'll wind up pulling your hair out one
day trying to figure out why this:

<%
haircolor="brown"
Response.write "<option value="""&haircolor&""">"&haircolor&"</option>"
%>

returns this:

Expected end of statement

Ray at work

 

[Jack]

Thanks again for your help Ray. And appreciate your last piece of advise
regarding the habit of having spaces on either side of & character. Regards.

You have your literal variable name in your SQL [non]query.

strsql = "UPDATE main SET main.FirstName ='" & l_firstname & "', " & _
"main.LastName = '" & l_lastname & "', " & _
"main.ColorPreference = '" & l_colorpreference & "', " &
_
"main.FoodPreference = '" & l_foodpreference & "', " &
_
"main.FinalUpdate = & " & CBool(l_finalupdate) & "
where " & _
"main.SocialSecurityNumber)='" & l_ss & "'

Also, notice I put spaces on either side of the & characters above. That is
a good habit to develop, because you'll wind up pulling your hair out one
day trying to figure out why this:

<%
haircolor="brown"
Response.write "<option value="""&haircolor&""">"&haircolor&"</option>"
%>

returns this:

Expected end of statement

Ray at work

Hi,
I am trying to update database from asp. However, the finalupdate field is
not
working here. The finalupdate field is a Access Database field of type
yes/no. Any help/advise is appreciated. Regards

THE DYNAMIC SQL STATEMENT:

strsql = "UPDATE main SET main.FirstName ='" &l_firstname& "', " & _
"main.LastName = '" &l_lastname& "', " & _
"main.ColorPreference = '" &l_colorpreference& "', " & _
"main.FoodPreference = '" &l_foodpreference& "', " & _
"main.FinalUpdate = &l_finalupdate& where " & _
"main.SocialSecurityNumber)='" &l_ss& "'

THE TRANSLATED SQL STATEMENT:


UPDATE main SET main.FirstName ='Cocoa', main.LastName = 'Jones',
main.ColorPreference = 'Choco', main.FoodPreference = 'Lamb',
main.FinalUpdate = &l_finalupdate& where
main.SocialSecurityNumber)='222-22-2222'