...

View Full Version : 0x80040E14 Syntax error in string in query expression ''1'.



BarrMan
02-04-2005, 03:00 PM
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
%>

gwendaal
02-04-2005, 03:38 PM
''''''
sql=sql & " where visitor_id=" & Session("visitor_id")

Response.Write(sql)

conn.Execute sql 'line 38

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


and write here the sql

ghell
02-04-2005, 03:48 PM
*scratches head* how did someone make a potentially 10 line (or less) pirce of code take up so many lines :confused:

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

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

fractalvibes
02-04-2005, 04:24 PM
response.write sql
response.end


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

fv

tboss132
02-04-2005, 06:00 PM
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

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.

BarrMan
02-04-2005, 07:05 PM
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.

fractalvibes
02-04-2005, 08:23 PM
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

BarrMan
02-05-2005, 08:50 AM
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:

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

******** | **** *** | ******@** | *** ******* | ***** | ***

fractalvibes
02-05-2005, 06:38 PM
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

BarrMan
02-06-2005, 01:08 PM
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).

glenngv
02-07-2005, 06:15 AM
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.


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.

ghell
02-07-2005, 09:31 AM
*pulls his hair out... then pulls everyone elses out* :p

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 :p)
id='16513216958164', phone='*********', category_id='1 WHERE visitor_id='434625853'should probably show up as
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:
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 :p (jk)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum