0x80040E14: Syntax error in INSERT INTO statement

garenk posted this at 14:20 — 30th May 2005.

They have: 3 posts

Joined: May 2005

I've created an online form (ASP) which writes into an access 2000 database. I'm getting the following error when trying to save:

"Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement."

Here is my code:
-----------------------------------

<?php
 language="VBScript"
?>

<?php
Set Conn = Server.CreateObject("ADODB.Connection")
DSNtest="DRIVER={Microsoft Access Driver (*.mdb)}; "
DSNtest=dsntest & "DBQ=" & Server.MapPath("/SSO/fpdb/sso.mdb")
Conn.Open DSNtest
?>

<?php
Function toSQLString(sql)
    toSQLString = Replace(sql,"'","''")
end function



SQL = "insert into Users ([FullName],[Pin],[Assettag],[MachineType],[LaptopHomeConnect],[SecPCHomeConnect])" & _
" values ('" & toSQLString(Request("FullName")) & _
"','" & toSQLString(Request("Pin")) & _
"','" & toSQLString(Request("AssetTag")) & _
"','" & toSQLString(Request("MachineType")) & _
"'," & toSQLString(LapHomeConn) & _
"," & toSQLString(SecPCHomeConn) & _
")"
'Response.Write SQL
Conn.execute SQL




conn.Execute "delete from Applications where Pin= '" & toSQLString(request("Pin")) & "'"
for i= 1 to 5
    if request("UserApplication" & i) <> "" then
        ' Insert Applcations
        sql = "Insert into Applications (Pin, UserApplication)" & _
        " values('" & toSQLString(Request("Pin")) & _
            "','" & toSQLString(request("UserApplication" & i)) & _
            "')"
        conn.Execute sql
    end if
next
conn.close
?>

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

Can you guys please help me with this problme. Much appreciated.

chrishirst posted this at 14:43 — 30th May 2005.

He has: 379 posts

Joined: Apr 2005

1/ Which line errors out? there are two executes in there

There looks to be some ' (single quotes) missing on the last two variables in the first insert

write out the SQL string and post that

Chris

Indifference will be the downfall of mankind, but who cares?
Venue Capacity Monitoring
Code Samples

garenk posted this at 15:04 — 30th May 2005.

They have: 3 posts

Joined: May 2005

Line 26 is where the error occurs. The first "Conn.execute SQL".
The last two variables are boolean variables. They don't require single quotes.

Here is the SQL string that might be causing the problem:

SQL = "insert into Users ([FullName],[Pin],[Assettag],[MachineType],[LaptopHomeConnect],[SecPCHomeConnect])" & _
" values ('" & toSQLString(Request("FullName")) & _
"','" & toSQLString(Request("Pin")) & _
"','" & toSQLString(Request("AssetTag")) & _
"','" & toSQLString(Request("MachineType")) & _
"'," & toSQLString(LapHomeConn) & _
"," & toSQLString(SecPCHomeConn) & _
")"

Let me know if this is sufficient. Thanks again for your help.

chrishirst posted this at 15:21 — 30th May 2005.

He has: 379 posts

Joined: Apr 2005

it's the output of the SQL string that will show the error

dhotchin posted this at 15:29 — 30th May 2005.

He has: 183 posts

Joined: Nov 2003

Usefull site: http://www.xlinesoft.com/asprunner/docs/error_syntax_error_in_insert_into_statement.htm

I think "Pin" is a reserved word. Try another word in your database.

garenk posted this at 16:59 — 30th May 2005.

They have: 3 posts