Problem in building dynamic sql update statement

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
Wed Dec 15 13:30:51 CST 2004

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" <Jack@discussions.microsoft.com> wrote in message
news:1E9713DA-FFB3-4366-93EA-F98CA9AD63B0@microsoft.com...
> 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'

Jack
Wed Dec 15 14:07:04 CST 2004

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.

"Ray Costanzo [MVP]" wrote:

> 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" <Jack@discussions.microsoft.com> wrote in message
> news:1E9713DA-FFB3-4366-93EA-F98CA9AD63B0@microsoft.com...
> > 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'
>
>
>