Problem in building dynamic sql update statement

Discussion in 'ASP General' started by Jack, Dec 15, 2004.

  1. Jack

    Jack Guest

    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, Dec 15, 2004
    #1
    1. Advertising

  2. 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" <> wrote in message
    news:...
    > 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], Dec 15, 2004
    #2
    1. Advertising

  3. Jack

    Jack Guest

    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" <> wrote in message
    > news:...
    > > 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, Dec 15, 2004
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. dna
    Replies:
    1
    Views:
    1,308
  2. Craig Feise via .NET 247

    Server Controls/SQL Update Statement

    Craig Feise via .NET 247, Jun 2, 2005, in forum: ASP .Net Web Controls
    Replies:
    0
    Views:
    127
    Craig Feise via .NET 247
    Jun 2, 2005
  3. Jack

    Problems with building sql statement

    Jack, Mar 30, 2005, in forum: ASP General
    Replies:
    3
    Views:
    140
  4. Jack
    Replies:
    4
    Views:
    184
  5. Maximus

    Syntax Error in SQL Update Statement

    Maximus, Feb 26, 2007, in forum: ASP General
    Replies:
    3
    Views:
    145
    Bob Barrows [MVP]
    Feb 26, 2007
Loading...

Share This Page