INSERT INTO statement

[SuperKlutz]

On an .asp page, I have a form set up to add users to a database. The form includes fields for username and password. When I try to add the user, I get the following error statement:

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in INSERT INTO statement.
/manage/confirmuser.asp, line 37

If I just insert the username, it works fine, but when I add the password, I get the error. Please look at my code and tell me what's wrong!

Code:

dim myConn
dim cnString
dim filePath

dim username, password, confirmpassword

username = Request.Form("username&quot
password = Request.Form("password&quot
confirmpassword = Request.Form("confirmpassword&quot


If username = "" or password = "" then
Response.Write(&quot;<h5><font color=&quot;&quot;red&quot;&quot;>You must enter a username and password.&quot; _
& &quot; Click your browser's Back button and enter all the&quot; _
& &quot; required information.</font></h5>&quot

elseif password <> confirmpassword then
Response.Write(&quot;<h5><font color=&quot;&quot;red&quot;&quot;>The password and confirm password fields&quot; _
& &quot; do not match. Click your browser's Back button and&quot; _
& &quot; re-enter the information.</font></h5>&quot

else
Set myConn = server.CreateObject(&quot;ADODB.Connection&quot
filePath = Server.MapPath(&quot;partners.mdb&quot
cnString = &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source= &quot; & filePath
dim strSQL

strSQL = &quot;INSERT INTO tblUsers(userName, password) Values('&quot; & username & &quot;', '&quot; & password & &quot;');&quot;

myConn.Open cnString

myConn.Execute strSQL,,adExecuteNoRecords

Response.Write(&quot;<h5><font color=&quot;&quot;GREEN&quot;&quot;>User &quot; & username & &quot; has been added.</font></h5>&quot
Response.Write(&quot;Click <a href=&quot;&quot;adduser.asp&quot;&quot;>here</a> to add&quot; _
& &quot; another user, or <a href=&quot;&quot;managemenu.asp&quot;&quot;>here</a>&quot; _
& &quot; to return to the menu.&quot

myConn.Close
Set myConn = nothing


end if

%>

 

[Golom]

The INSERT INTO statement itself looks OK. Check the actual values of the &quot;username&quot; and &quot;password&quot; variables. Does one (or both) of them contain a single quote? If it does then you may have unmatched quotes and that's causing the error.

 

[swampBoogie]

password is a reserved word in Jet SQL.

 

[SuperKlutz]

Thanks. I have resolved the problem. Apparently, MS-Access doesn't like any fields named &quot;password&quot; (undocumented keyword??). I changed the field name to strPassword, and it worked perfectly. Go figure!