p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Classic ASP Databases (http://p2p.wrox.com/forumdisplay.php?f=62)
-   -   Syntax error in INSERT INTO statement. (http://p2p.wrox.com/showthread.php?t=14003)

askaggs June 9th, 2004 11:33 PM

Syntax error in INSERT INTO statement.
 
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
[8]Strive for Perfection[8]

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
[8]Strive for Perfection[8]

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
[8]Strive for Perfection[8]


All times are GMT -4. The time now is 03:20 PM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.