Most annoying sql error in the world

[mxs00u]

Hi, I have the follwing asp code which is supposed to move a row from one database into another database, and then delete it from the source database:

<%
Dim conn2,rs2,strsql2,strsql3,rs3
set conn2 = server.CreateObject("ADODB.Connection")
set rs2 = server.CreateObject("ADODB.Recordset")

'DSN less connection
conn2.Provider = "Microsoft.Jet.OLEDB.4.0"
conn2.ConnectionString = "Data Source=" & Server.MapPath("Databases/Acad1.mdb")
conn2.open

strsql2 = "select * from markregister where username = '" & Request.Form("username") & "'"
set rs2 = conn2.Execute(strsql2)
'response.write(rs2("firstname"))

strsql3 = "INSERT INTO Personal_Info (Firstname, Lastname, Addr1, Addr2, Addr3, Addr4, Postcode, EmailAddr, Username, ID) VALUES ('"& rs2("firstname") & "', '"& rs2("surname") & "', '"& rs2("address1") & "','"& rs2("address2") & "', '"& rs2("address3") & "', '"& rs2("address4") & "', '"& rs2("postcode") & "', '"& rs2("email") & "', '"& rs2("username") & "', '"& rs2("username") & "')"
strsqldel = "DELETE FROM markregister WHERE username = '" & Request.Form("username") & "'"


response.write(strsql3)
conn2.Execute(strsql3)
conn2.Execute(strsqldel)
'response.write(Request.Form("username"))

rs2.close
set rs2 = nothing


'close the connection
conn2.close
set conn2 = nothing
response.Redirect("admin.asp")

%>



My problem is that the code above works totally fine until I add my password field. So:

strsql3 = "INSERT INTO Personal_Info (Firstname, Lastname, Addr1, Addr2, Addr3, Addr4, Postcode, EmailAddr, Username, ID) VALUES ('"& rs2("firstname") & "', '"& rs2("surname") & "', '"& rs2("address1") & "','"& rs2("address2") & "', '"& rs2("address3") & "', '"& rs2("address4") & "', '"& rs2("postcode") & "', '"& rs2("email") & "', '"& rs2("username") & "', '"& rs2("username") & "')"


becomes:

strsql3 = "INSERT INTO Personal_Info (Firstname, Lastname, Addr1, Addr2, Addr3, Addr4, Postcode, EmailAddr, Username, Password, ID) VALUES ('"& rs2("firstname") & "', '"& rs2("surname") & "', '"& rs2("address1") & "','"& rs2("address2") & "', '"& rs2("address3") & "', '"& rs2("address4") & "', '"& rs2("postcode") & "', '"& rs2("email") & "', '"& rs2("username") & "', '"& rs2("password") & "', '"& rs2("username") & "')"

This gives the following error:




INSERT INTO Personal_Info (Firstname, Lastname, Addr1, Addr2, Addr3, Addr4, Postcode, EmailAddr, Username, Password, ID) VALUES ('Joe', 'Blogs', '169 the farthings','far road', 'lenton', 'nottingham', 'NG7 2BA', 'kevin@hotmail.com', 'kevin', '12345', 'kevin')
Microsoft JET Database Engine error '80040e14'

Syntax error in INSERT INTO statement.

/processadmin.asp, line 29

As you can see from the document.write line, the sql statement looks fine, and there is definitely a Password text field in the target database.

I just don't understand why adding this field makes so much difference because without it it writes to the database just fine!!!!

[fredmv]

Moved to ASP.

[russell]

whats the data type of the password field? tried wrapping [Password] and [id] in brackets?

[mxs00u]

Password is just a text file. Exactly the same as the username which writes fine....

[russell]

what happens when you try to execute this:

INSERT INTO Personal_Info (Firstname, Lastname, Addr1, Addr2, Addr3, Addr4, Postcode, EmailAddr, Username, Password, ID) VALUES ('Joe', 'Blogs', '169 the farthings','far road', 'lenton', 'nottingham', 'NG7 2BA', 'kevin@hotmail.com', 'kevin', '12345', 'kevin')

directly in msaccess query window?

did you try wrapping [Password] and [id] like this?

what is the length of the text field?

[mxs00u]

if I put it in the query window it works...

I'll try the [].

[mxs00u]

right I've nailed it. I ended up doing a seperate update statement but in the end it was fixed by changing the "Password" field to "Pw". I don't know why the name of the field caused a problem but its working now which is the main thing!

Cheers for the help

[heavenly_blue]

password is a reserved word in SQL statements. Although you can have a column in a table named 'password', trying to execute a query on it will result in this error. Same thing happened to me... :rolleyes:

[russell]

wrapping it in [] allows you to name it whatever you want:

SELECT [Password] FROM myTable
SELECT [ID] FROM myTable
SELECT [UserName] FROM myTable