Syntax Error for INSERT???

[yapjiwen]

Hi all, Im trying to insert some data into the Access Database.

Is there any problem with the syntax for the codes below?

conn.Execute "insert into Expense(date,title,po_number,po_amount,vendor,acct_code,date_paid,remarks,emp_id) values (" _
& "'" & Request.Form("dateToday") & "', " _
& "'" & Request.Form("title") & "', " _
& "'" & Request.Form("po_number") & "', " _
& "'" & Request.Form("po_amount") & "', " _
& "'" & Request.Form("vendor") & "', " _
& "'" & Request.Form("acct_code") & "', " _
& "'" & Request.Form("remarks") & "', " _
& "'" & Request.Form("date_paid") & "', " _
& "'" & Request.Form("emp_id") & "')"

why do this always occur whenever i activate the form in my asp:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

Please help if you can.. any help will be appreciated.

[Roy Sinclair]

You're getting a syntax error because the SQL statement you're building isn't valid. The way you're building that statement, you're not able to take a look at it either.

Dim sSQL
sSQL = "insert into Expense(date,title,po_number,po_amount,vendor,acct
_code,date_paid,remarks,emp_id) values (" _
& "'" & Request.Form("dateToday") & "', " _
& "'" & Request.Form("title") & "', " _
& "'" & Request.Form("po_number") & "', " _ 
& "'" & Request.Form("po_amount") & "', " _ 
& "'" & Request.Form("vendor") & "', " _ 
& "'" & Request.Form("acct_code") & "', " _ 
& "'" & Request.Form("remarks") & "', " _ 
& "'" & Request.Form("date_paid") & "', " _ 
& "'" & Request.Form("emp_id") & "')"
response.write sSQL & "<hr />"
conn.Execute sSQL

Try this code so you can see what the SQL statement looks like, maybe then the problem will be visible. Of course it may be as simple as replacing "date" with "[date]" because I'm pretty sure "date" is a reserved word.

[yapjiwen]

Hey thanks dude...Its really as simple as changing the "date" to another name...guess date is reserved as mentioned by you.

thanks!

[Roy Sinclair]

You can use reserved words as data names in SQL but when you do you have to put them in brackets. So while DATE alone is invalid, [DATE] in that form is perfectly acceptable.

It's still better to just avoid using reserved words entirely but sometimes that's not an option so you should definitely be aware of how to deal with them.