Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    Senior Coder BarrMan's Avatar
    Join Date
    Feb 2005
    Location
    Israel.
    Posts
    1,644
    Thanks
    69
    Thanked 83 Times in 82 Posts

    I need help , urgent

    Hi

    Here are the details of this error:

    Error Type:
    Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
    [Microsoft][ODBC Microsoft Access Driver] Syntax error in string in query expression ''1'.
    /bookstore/joinclub2.asp, line 38




    code:

    <%@ Language=VBScript %>

    <%
    dim visitor_name, email, address, phone, credit_card_id, id
    dim category_id, sql
    visitor_name=Request.Form("visitor_name")
    email=Request.Form("email")
    address=Request.Form("address")
    phone=Request.Form("phone")
    id=Request.Form("id")
    category_id=Request.Form("category_id")
    radio1=Request.Form("radio1")

    if visitor_name = nil or email=nil or address=nil or phone=nil then
    Response.Write "<center><h2>"
    Response.Write "חלק מהפרטים שהקשת חסרים או שגויים" & "<br>"
    Response.Write "חזור לדף הקודם, תקן, ובצע שנית"
    Response.Write "</h2></center>"
    else
    dim conn
    set conn=server.CreateObject("ADODB.connection")
    conn.Open "dsn=bookstore"

    sql="update visitors set visitor_name='" & visitor_name
    sql=sql & "', email='" & email
    sql=sql & "', address='" & address
    sql=sql & "', id='" & id
    sql=sql & "', phone='" & phone
    sql=sql & "', category_id='" & category_id
    sql=sql & " where visitor_id=" & Session("visitor_id") 'The error might be on this line too...
    conn.Execute sql 'line 38

    conn.Close
    set conn=nothing

    End if
    %>














    Last edited by BarrMan; 02-05-2005 at 01:26 PM.

  • #2
    Regular Coder
    Join Date
    Jul 2004
    Location
    France
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ''''''
    sql=sql & " where visitor_id=" & Session("visitor_id")

    Response.Write(sql)

    conn.Execute sql 'line 38

    ----------------------------------


    and write here the sql

  • #3
    Senior Coder
    Join Date
    Apr 2003
    Location
    England
    Posts
    1,192
    Thanks
    5
    Thanked 13 Times in 13 Posts
    *scratches head* how did someone make a potentially 10 line (or less) pirce of code take up so many lines

    doesnt conn.Execute() need parenthesis, and does dsn=bookstore need to be dsn=bookstore; ? (i use dsnless a lot)

    aside from that, you are checking against nil (id check against "", and nil as well if this is just the default value in the form elements) and it might not be able to cope with unicode characters (im assuming people may be entering them from the message generated there)

    however, im pretty sure the lines throwing the error are
    sql=sql & "', id='" & id
    sql=sql & "', phone='" & phone
    sql=sql & "', category_id='" & category_id
    (and the ' on the next line isnt here, so you actually have 'category_id not 'category_id' or category_id)

    as i expect it should be
    Code:
    sql="update visitors set visitor_name='" & visitor_name & "', email='" & email & "', address='" & address & "', id=" & id & ", phone='" & phone & "', category_id=" & category_id & " where visitor_id=" & Session("visitor_id")
    if you are using a non-text datatype in the database you cant enclose with 's
    Last edited by ghell; 02-04-2005 at 02:56 PM.

  • #4
    Regular Coder
    Join Date
    Aug 2002
    Location
    Texas
    Posts
    287
    Thanks
    0
    Thanked 0 Times in 0 Posts
    response.write sql
    response.end


    and examine the sql you are building - all the values there and everything properly formatted, quoted, etc???

    fv

  • #5
    Regular Coder
    Join Date
    Oct 2004
    Location
    In front of this computer. (Where else?)
    Posts
    442
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Seems you're are a newbie and you'll probably not understands what these "expert oldbies" are talking about. So here's it in newbie language.

    Replace line 38 with
    Code:
    Response.write sql
    Then run it. When you do, an sql statement is displayed on the webpage. Check all your syntax and make sure they're all correct. You can verify in a query analyzer window. From there, you'll discover your mistakes.
    Tomorrow is the first day of the rest of your life... What have you done today?
    Tutorials
    Web: w3schools, htmldog General tips: Hardware, Networking

  • #6
    Senior Coder BarrMan's Avatar
    Join Date
    Feb 2005
    Location
    Israel.
    Posts
    1,644
    Thanks
    69
    Thanked 83 Times in 82 Posts

    Thx for the replies

    Can you just write the code I need to do because I don't really know what to do?

    My problem is conn.execute, what do I need to do for not getting the error?

    I have another problem with my "addtobasket.asp" page, the code is:

    <%
    dim book_id
    Request.QueryString("book_id")

    dim conn
    set conn=server.CreateObject("ADODB.connection")
    conn.Open "dsn=bookstore"

    conn.Execute "insert into basket (book_id, visitor_id) select " & book_id & ", " &_
    Session("visitor_id")

    conn.Close
    set conn=nothing

    Response.Redirect "showbasket.asp"
    %>

    the error i get is:

    Error Type:
    Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
    [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
    /bookstore/addtobasket.asp, line 11


    Thx again.
    Last edited by BarrMan; 02-04-2005 at 06:21 PM.

  • #7
    Regular Coder
    Join Date
    Aug 2002
    Location
    Texas
    Posts
    287
    Thanks
    0
    Thanked 0 Times in 0 Posts
    No, your problem is with the SQL. It attempts to execute the sql with the
    conn.execute , and database returns with a syntax error in the sql.

    Do as tboss132 said, and post the value it wrote out. There is no other way.

    fv

  • #8
    Senior Coder BarrMan's Avatar
    Join Date
    Feb 2005
    Location
    Israel.
    Posts
    1,644
    Thanks
    69
    Thanked 83 Times in 82 Posts
    But "Response.Write sql" will write it on the page only, I need to write it on my database.

    Like this:

    BookStore
    Visitor_id: | Visitor_name: | Email: | Address: | Phone: | Id:

    ******** | **** ** | ***@**.** | *** ******** | ***** | ****

    ******** | **** *** | ******@** | *** ******* | ***** | ***
    Last edited by BarrMan; 02-05-2005 at 01:23 PM.

  • #9
    Regular Coder
    Join Date
    Aug 2002
    Location
    Texas
    Posts
    287
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes, we know that. However, that will not happen until you figure out the error with your SQL!!! That is why it was suggested to simply response.write out the sqlstring you are building, to debug it. Do this and post what you get written out to the page. Then, and only then can someone assist you with the error.

    All you error message says:
    -------------
    Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
    [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
    /bookstore/addtobasket.asp, line 11
    -----------------------

    Is that there is an error with the insert statement somewhere...

    fv

  • #10
    Senior Coder BarrMan's Avatar
    Join Date
    Feb 2005
    Location
    Israel.
    Posts
    1,644
    Thanks
    69
    Thanked 83 Times in 82 Posts

    I'm so sorry for misunderstanding

    Alright, here it is.

    UPDATE visitors SET [visitor_name]='*********', email='******', address='********', id='16513216958164', phone='*********', category_id='1' WHERE visitor_id='434625853'

    That what it writes (of course the "*****" chars are on perpose).

  • #11
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    10,964
    Thanks
    0
    Thanked 236 Times in 233 Posts
    Quote Originally Posted by BarrMan
    Alright, here it is.

    UPDATE visitors SET [visitor_name]='*********', email='******', address='********', id='16513216958164', phone='*********', category_id='1' WHERE visitor_id='434625853'

    That what it writes (of course the "*****" chars are on perpose).
    Are you sure category_id='1' has really a closing single quote in the output? Because in your posted code, there is nothing.
    Quote Originally Posted by BarrMan
    sql=sql & "', category_id='" & category_id
    sql=sql & " where visitor_id=" & Session("visitor_id")
    That's where the error was, there should have been a closing single quote in category_id value.

    sql=sql & "', category_id='" & category_id
    sql=sql & "' where visitor_id=" & Session("visitor_id")

    The value of the visitor_id field has no single quotes in your code but it shows in your output. Are you really running your original code? Looking at the generated sql statement, I cannot find anything that will cause the error you're getting. It looks fine.

  • #12
    Senior Coder
    Join Date
    Apr 2003
    Location
    England
    Posts
    1,192
    Thanks
    5
    Thanked 13 Times in 13 Posts
    *pulls his hair out... then pulls everyone elses out*

    although the error is with only one ', hes probably trying to put a string into a numerical datatype (unles hes a REAL newbie and his datatypes are all string anyway )
    Code:
    id='16513216958164', phone='*********', category_id='1 WHERE visitor_id='434625853'
    should probably show up as
    Code:
    id=16513216958164, phone='*********', category_id=1 WHERE visitor_id=434625853
    (provided phones are text, they usually are to cope with formatting etc)

    try this for your sql line:
    Code:
    strSQL = "UPDATE visitors SET visitor_name='" & visitor_name & "', email='" & email & "', address='" & address & "', id=" & id & ", phone='" & phone & "', category_id=" & category_id & " WHERE visitor_id=" & Session("visitor_id")
    conn.Execute(strSQL)
    also provided they are all the right datatype when you enter them, eg the session is a int or a lng or whatever

    also, i said all this before.. but did anyone listen to me.. nooo (jk)
    Last edited by ghell; 02-07-2005 at 08:38 AM.


  •  

    Posting Permissions

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