Syntax error in INSERT INTO statement.

askaggs · June 9th, 2004, 11:33 PM

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

Here's the code up to the point that error occurs. (Red marks are my edits for this post)

<%
dim oConn
dim oRs
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)}"
oConn.ConnectionString = oConn.ConnectionString & ";DBQ=" & server.MapPath("login.mdb")
oConn.Open
line 9 -> set oRs = oConn.Execute("INSERT INTO tblLoginInfo(usrnm,psswrd,rlnm,eml,qstn,answr) VALUES('" & request.Form("usrnm") & "','" & request.Form("psswrd") & "','" & request.Form("rlnm") & "','" & request.Form("eml") & "','" & request.Form("qstn") & "','" & request.Form("answr") & "'")
%>

As you can see, it's pretty standard stuff...although perhaps not standard enough...stupid syntax error. I've condensed down the field names in the Access database and in my form to try and avoid any reserved words. I've also tried replacing all the request.Form statements with static values for testing purposes, but that didn't seem to work either. I've been searching forums for hours and can't seem to come up with a solution. Any ideas?

Andrew Skaggs

happygv · June 9th, 2004, 11:38 PM

Hi Andrew

Try this.

Code:

strSQL = "INSERT INTO tblLoginInfo(usrnm,psswrd,rlnm,eml,qstn,answr) VALUES('" & request.Form("usrnm") & "','" & request.Form("psswrd") & "','" & request.Form("rlnm") & "','" & request.Form("eml") & "','" & request.Form("qstn") & "','" & request.Form("answr") & "'"

response.write strSQL
response.end

'''set oRs = oConn.Execute(strSQL)

Then execute output of the above response.write in your DB directly, and see what is missing. Should be some missing QUOTE error.

Hope that helps.
Cheers!

_________________________
-Vijay G

Strive for Perfection

askaggs · June 9th, 2004, 11:56 PM

Well, I'm getting there... I missed a parenthesis. I needed one right before the last double quotes. Now...on to the next error. :)

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.

Line 14 is the same code that was line 9 (I added your suggested debugging code above and just commented it out) with the addition of the parenthesis.

set oRs = oConn.Execute("INSERT INTO tblLoginInfo(usrnm,psswrd,rlnm,eml,qstn,answr) VALUES('" & request.Form("usrnm") & "','" & request.Form("psswrd") & "','" & request.Form("rlnm") & "','" & request.Form("eml") & "','" & request.Form("qstn") & "','" & request.Form("answr") & "')")

Thanks already for your help.

Andrew Skaggs

happygv · June 10th, 2004, 12:14 AM

The "IUSR_your_machine" has not enough permissions to write into your database. IUSR privileges must be set to "read/write".

That will solve this.

Cheers!

_________________________
-Vijay G

Strive for Perfection

askaggs · June 10th, 2004, 12:18 AM

Yeah, thanks a bunch! I had just figured that out and got one more issue worked out and everything seems to be working beautifully now.

*pant* Time to get some sleep...it's 12:18 AM... Man, I love programming. :)

Again, thanks a ton.

Andrew Skaggs

happygv · June 10th, 2004, 12:21 AM

Great!

Good night then. Have a sound sleep;)

_________________________
-Vijay G

Strive for Perfection