asp error help

jcs1987

jcs1987

jcs1987

Associate
Joined
26 May 2004
Posts
103
Location
staffordshire
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.

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
 
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.
 
Just looking at it, if that's all you code, the problem is this line:
Code: 
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
Code: 
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.
 
Last edited:
OK ive made these changes.

Code: 
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

Code: 
Set objRecordset=objConnection.Execute(sql)

Im just going to have a look at simons suggestion in the meantime
 
Code: 
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.
Code: 
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:
Code: 
;DELETE FROM CUSTOMER;--
 
altered the code to

Code: 
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

Code: 
 Set objRecordset=objConnection.Execute(sql)

I will look into sanitizing input once i get it to enter details into my database.
 
You must log in or register to reply here.
Back
Top Bottom