Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2006
    Posts
    1

    Unanswered: Problem with Code Need Help

    I am a newbie and I am updating some code for a client, I am trying to pass a form onto the code below but I get an error (below).

    ERROR:

    Microsoft JET Database Engine error '80040e14'

    Syntax error in UPDATE statement.

    /createaccountexe.asp, line 30


    CODE:

    Code:
    <%
    							vFirstName = replace(request("x_first_name"),"'","''")
    							vLastName = replace(request("x_last_name"),"'","''")
    							vAddress1 = replace(request("x_address"),"'","''")
    							vCity = replace(request("x_city"),"'","''")
    							vState = replace(request("x_state"),"'","''")
    							vZip = replace(request("x_zip"),"'","''")
    							vPhone = replace(request("x_phone"),"'","''")
    							vEmailAddress = replace(request("x_email"),"'","''")
    							UserName = replace(request("UserName"),"'","''")
    							Password = replace(request("Password"),"'","''")
    							vPhone = replace(request("x_phone"),"'","''")
    							vFax = replace(request("x_fax"),"'","''")
    														
    sql="update tblCustomer set FirstName = '"&vFirstName&"', LastName = '"&vLastName&"', "&_
    " Address1 = '"&vAddress1&"', City = '"&vCity&"', "&_
    " State = '"&vState&"', Zip = '"&vZip&"', Bank_MICR = '"&vFax&"', Phone = '"&vPhone&"', "&_
    " EmailAddress = '"&vEmailAddress&"',UserName = '"&UserName&"', Password = '"&Password&"' where User_ID="&session("User_ID")
    'response.Write(sql)
    set rs=conn.execute(sql)
    
    response.Redirect("ordercheckout.asp")
    %>

  2. #2
    Join Date
    Mar 2006
    Location
    south jersey, usa
    Posts
    53
    user_id in the where clause is not wrapped in single quotes
    "They say Moses split the Red Sea
    I split the blunt and rolled the fat one, I'm deadly"
    -- Tupac 'Blasphemy'

  3. #3
    Join Date
    Jul 2006
    Posts
    56
    That would only be a problem if the user_id were actually non-number. if that's the case, problem solved. otherwise, I suggest you organize your code! It's easier to solve problems.

    Furthermore, using the replace on your request inputs do not provide you with SQL injection protection. Get protected! I'm not fond of ASP nor do I know it that much, but I do remember there's a server html encode or something like that. That would help you with unwanted 's (although it doesn't secure you from SQL Injections...)

    Code:
    vFirstName = replace(request("x_first_name"),"'","")
    vLastName = replace(request("x_last_name"),"'","")
    vAddress1 = replace(request("x_address"),"'","")
    vCity = replace(request("x_city"),"'","")
    vState = replace(request("x_state"),"'","")
    vZip = replace(request("x_zip"),"'","")
    vPhone = replace(request("x_phone"),"'","")
    vEmailAddress = replace(request("x_email"),"'","")
    UserName = replace(request("UserName"),"'","")
    Password = replace(request("Password"),"'","")
    vPhone = replace(request("x_phone"),"'","")
    vFax = replace(request("x_fax"),"'","")
    
    sql = "update tblCustomer set "
    sql = sql & "FirstName = '" & vFirstName & "', "
    sql = sql & "LastName = '" & vLastName & "', "
    sql = sql & "Address1 = '" & vAddress1 & "', "
    sql = sql & "City = '" & vCity & "', "
    sql = sql & "State = '" & vState & "', "
    sql = sql & "Zip = '" & vZip & "', "
    sql = sql & "Bank_MICR = '" & vFax & "', "
    sql = sql & "Phone = '" & vPhone & "', "
    sql = sql & "EmailAddress = '" & vEmailAddress & "', "
    sql = sql & "UserName = '" & UserName & "', "
    sql = sql & "Password = '" & Password & "' " 
    sql = sql & "where "
    sql = sql & "User_ID = " & session("User_ID")
    
    'response.clear()
    'response.Write(sql)
    'response.flush()
    'response.end()
    
    set rs = conn.execute(sql)
    
    response.Redirect("ordercheckout.asp")
    Use these four commented lines above to get you SQL statement on screen. Run it on your database SQL Window to get an error message, and check what's wrong.

    By the way, I do not think that a syntax error has anything to do with a wrong datatype. Would that be the error generated by the server or the database if there were no single quotes wrap on a string datatype input? I don't think so, but, anywayz...

    Oh, one more thing... Replace the single quotes in the input by empties or spaces, if you DO have to use replace...

    Lemme know if this helped
    Last edited by igordonin; 07-21-06 at 17:15.

  4. #4
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    I agree with igordonin
    organize your code!
    Another good tip which I like to use myself is to create and run the query separately. If you can run it through some kind of query analyzer (MS SQL 2000) or something first, and add you own WHERE clause values to make sure that query is written correctly and giving you the results you want. This will eliminate one possible error.

    Another good way of debugging is output ALL your variables right before you assign them to your query to double check nothing happened to them. A lot of times they get out of scope or you find they weren't assigned correctly.

    By using the tips suggested above the only other place an error could occur is in your case a syntax error in creating and executing the string query.

    These are just some general tips
    good luck
    Reghardt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •