asp error help

[jcs1987]

Hi guys ive been working on an asp page to enter data into a database. I've spent the whole morning troubleshooting one error after another but I have an error, which i just cant find a resolution for.

Here is the asp code and the error im getting is:

Microsoft JET Database Engine error '80040e14'
Syntax error in INSERT INTO statement.

The line that the is specified in the error is the blank line inbetween the highlighted code.

<%
dim Customer, Member_id, S_name, F_name, Address_line1, Address_line2, Address_line3, City, Postcode, Contact_number, Date_of_birth, Date_registered

Dim objConnection
Dim objRecordset

Set objConnection= Server.CreateObject("ADODB.Connection")
Set objRecordset= Server.CreateObject("ADODB.Recordset")
objConnection.Provider="Microsoft.Jet.OLEDB.4.0"
objConnection.Open "c:\sites\content\j\c\s\jcs1987\db\The Picture House.mdb"
objRecordset.open "Customer", objConnection

sql="INSERT INTO " & Customer & " (" & Member_id & "," & S_name & "," & F_name & "," & Address_line1 & "," & Address_line2 & ", " & Address_line3 & "," & City & "," & Postcode & "," & Contact_number & "," & Date_of_birth & "," & Date_registered & ")"
sql=sql & " VALUES "
sql=sql & "('" & Request.Form("memberid") & "',"
sql=sql & "'" & Request.Form("surname") & "',"
sql=sql & "'" & Request.Form("firstname") & "',"
sql=sql & "'" & Request.Form("address1") & "',"
sql=sql & "'" & Request.Form("address2") & "',"
sql=sql & "'" & Request.Form("address3") & "',"
sql=sql & "'" & Request.Form("city") & "',"
sql=sql & "'" & Request.Form("postcode") & "',"
sql=sql & "'" & Request.Form("contactnumber") & "',"
sql=sql & "'" & Request.Form(#"dateofbirth"#) & "',"
sql=sql & "'" & Request.Form(#"dateregistered"#) & "',"

[COLOR="Red"]Set objRecordset=objConnection.Execute(sql)

If memberid<>"" Then[/COLOR]
      Response.Write ("Member added")
end if

objRecordset=nothing
objConnection.close

%>

can anyone help?

Thanks

 

[Gman]

easiest thing to do is to add a response.write before your execute the sql string, so:

response.write(sql)
response.end()

now you should have the proper sql statement, copy thing and past it into a query window for your database and that should give you a more accurate mesage.

 

[SimonCHere]

Just looking at it, if that's all you code, the problem is this line:

sql="INSERT INTO " & Customer & " (" & Member_id & "," & S_name & "," & F_name & "," & Address_line1 & "," & Address_line2 & ", " & Address_line3 & "," & City & "," & Postcode & "," & Contact_number & "," & Date_of_birth & "," & Date_registered & ")"

You defined the variable customer, member_id, s_name etc but didn't set them to anything!

should be

sql="INSERT INTO Customer  (Member_id, S_name, F_name, Address_line1, Address_line2, Address_line3, City, Postcode, Contact_number, Date_of_birth, Date_registered) "

and if it's not, you're doing it wrong!

Oh and you don't need to do the

objRecordset.open "Customer", objConnection

unless you are reading the "Customer" table into the recordset to do something with. Which you're not.

 

[jcs1987]

OK ive made these changes.

sql=sql & "'" & Request.Form("dateregistered") &"';"

response.write(sql)
response.end()

when i click submit it displays the following

INSERT INTO (,,,,, ,,,,,) VALUES ('C004','blogs','joe','smith street','address','address','address','postcode','0000000000','01/01/1980','07/10/2008';

if i take both response.write and response.end out it then gives me the previous error but points at the following line

Set objRecordset=objConnection.Execute(sql)

Im just going to have a look at simons suggestion in the meantime

 

[SimonCHere]

INSERT INTO (,,,,, ,,,,,) VALUES ('C004','blogs','joe','smith street','address','address','address','postcode',' 0000000000','01/01/1980','07/10/2008';

isn't valid SQL
That's your problem. See my post for the solution to that. not necessarily that the rest of your code will work!
Oh and you'll need to change the ";" to ")" on the end.

sql=sql & "'" & Request.Form("dateregistered") &"')"

by the way. I hope you sanitize the input before sending it to that page. You wouldn't want someone to have an address as:

;DELETE FROM CUSTOMER;--

 

[jcs1987]

altered the code to

sql="INSERT INTO " & Customer & " (Member_id ,S_name, F_name, Address_line1,Address_line2, Address_line3, City, Postcode, Contact_number, Date_of_birth, Date_registered )"

and added the response.write and response.end code again and now it displays

INSERT INTO (Member_id ,S_name, F_name, Address_line1,Address_line2, Address_line3, City, Postcode, Contact_number, Date_of_birth, Date_registered ) VALUES ('C004','blogs','joe','smith street','address','address','address','postcode','0000000000','01/01/1980','07/10/2008')

If i take response.write and .end out it errors with the same syntax error, which points at this line

 Set objRecordset=objConnection.Execute(sql)

I will look into sanitizing input once i get it to enter details into my database.

 

[jcs1987]

Its sorted it now updates my database sucessfully. Thanks for the help

 

[SimonCHere]

No problem, glad it works.