Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[breeze76]

This is teh complete error I am getting:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

/orderform.asp, line 319

I have read the post from April 2004 but it still does not help me solve my problem.

Here is the code at issue:

<%' this is where the information finally gets submitted to the database
DIM Conn1, strConn, SQLstmt, RS1
set Conn1 = server.createobject("adodb.connection")
strConn = "driver={Microsoft Access Driver (*.mdb)};;dbq=Estate.mdb"
Conn1.open strConn
SQLstmt = "INSERT INTO onlineorder (date, firm_name, contact_name, phone_number, email, request, hardcopy, pdfocr, pdfonly, multitiff, singletiff, summationocr, summationonly, webdownload, othertype1, othertype2, instructions )"
SQLstmt = SQLstmt & " VALUES ("
SQLstmt = SQLstmt & "'" & date & "',"
SQLstmt = SQLstmt & "'" & firm_name & "',"
SQLstmt = SQLstmt & "'" & contact_name & "',"
SQLstmt = SQLstmt & "'" & phone_number & "',"
SQLstmt = SQLstmt & "'" & email & "',"
SQLstmt = SQLstmt & "'" & request & "',"
SQLstmt = SQLstmt & "'" & hardcopy & "',"
SQLstmt = SQLstmt & "'" & pdfocr & "',"
SQLstmt = SQLstmt & "'" & pdfonly & "',"
SQLstmt = SQLstmt & "'" & multitiff & "',"
SQLstmt = SQLstmt & "'" & singletiff & "',"
SQLstmt = SQLstmt & "'" & summationocr & "',"
SQLstmt = SQLstmt & "'" & summationonly & "',"
SQLstmt = SQLstmt & "'" & webdownload & "',"
SQLstmt = SQLstmt & "'" & othertype1 & "',"
SQLstmt = SQLstmt & "'" & othertype2 & "',"
SQLstmt = SQLstmt & "'" & instructions & "'"
SQLstmt = SQLstmt & ")"

Set RS1 = Conn1.execute(SQLstmt) this is line 319

Any ideas?

Thanks
breeze76

[Iguana]

You've called one of your database fields 'date ' and this is a reserved word

[breeze76]

Aahhhh.. Ok.. let me try that then.. Thanks for teh quick heads up..

breeze76

[mrMister]

you can still use teh field name without altering the database, but when referencing teh field name, you should enclose it in square brackets eg.

"SELECT [date] FROM myTable"

I hope teh advice I have given you helps.

[Easy_Coder]

On line 318 write this (SQLstmt) value out and debug your query... your variables are populated right?

[Easy_Coder]

Make sure the variables are all set, I don't see that in the code you posted. Here's an example:
date = "2005-08-24 12:00:00"
firm_name = "me"
contact_name = "me too"

SQLstmt = SQLstmt & " VALUES ("
SQLstmt = SQLstmt & "'" & date & "',"
SQLstmt = SQLstmt & "'" & firm_name & "',"
SQLstmt = SQLstmt & "'" & contact_name & "',"
SQLstmt = SQLstmt & "'" & phone_number & "',"
SQLstmt = SQLstmt & "'" & email & "',"
SQLstmt = SQLstmt & "'" & request & "',"
SQLstmt = SQLstmt & "'" & hardcopy & "',"
SQLstmt = SQLstmt & "'" & pdfocr & "',"
SQLstmt = SQLstmt & "'" & pdfonly & "',"
SQLstmt = SQLstmt & "'" & multitiff & "',"
SQLstmt = SQLstmt & "'" & singletiff & "',"
SQLstmt = SQLstmt & "'" & summationocr & "',"
SQLstmt = SQLstmt & "'" & summationonly & "',"
SQLstmt = SQLstmt & "'" & webdownload & "',"
SQLstmt = SQLstmt & "'" & othertype1 & "',"
SQLstmt = SQLstmt & "'" & othertype2 & "',"
SQLstmt = SQLstmt & "'" & instructions & "'"
SQLstmt = SQLstmt & ")"

Response.Write (SQLstmt) <-- check the statement once the values are set

Set RS1 = Conn1.execute(SQLstmt) this is line 319

[breeze76]

The reason for not seeing the variables being populated is that I get that from a form just above that code, but the error I get is just when I load the page with out even putting a thing in the form... I will check the above suggestions from everyone this morning when I get to work...

Will keep you posted

breeze76

[Easy_Coder]

One other thing...

You need to change this variable name: request

Request is an intrinsic object that's built into ASP so you can't overload it with your own value. Attempting to do this:

request = Request.Form("somefield")

will throw an error. As would attempting to dump it into a Sql Statement:
SQLstmt = SQLstmt & "'" & request & "',"

[breeze76]

OK everything seems to be working now, thanks for the help.. BUT, I have another issue... in the text boxes I am using, if someone uses an apostraphe anywhere in it, I get an error and it will not run the sql statement. Is there a way to remove the apostraphes or make it so the sql will take them without an error?

Here is the error message:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''Let's see if this works')'.

/orderform.asp, line 436

Any ideas..

Thanks again

breeze76

[Iguana]

write a small function that replaces all the single apostrophes with 2 apostrophes and wrap this function around every variable as you build up your SQL statement

e.g.

SQLstmt = SQLstmt & "'" & CleanSQL(contact_name) & "',"