why, WHY the syntax errors!?

[calliepeck]

I'm getting this error, and I don't know why!:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
/HIRC/adminfiles/update_hearings.asp, line 34


I miss php/mysql

<%
Dim sql, headline, title, m, d, y, thedate, thetime, fulltime, location, thetype, committee, committee2, info

title=request.Form("title")
head=request.Form("head")
thetime=request.Form("time")
am_pm=request.Form("am_pm")
fulltime=thetime&" "&am_pm
m=request.Form("mo")
d=request.Form("day")
y=request.Form("year")
thedate=m&"/"&d&"/"&y
thetype=request.Form("type")
committee=request.Form("headline")
committee2=request.Form("committee2")
info=request.Form("info")

sql="INSERT INTO hearings SET"_
&"title='title',"_
&"headline='head',"_
&"time='fulltime',"_
&"date='thedate',"_
&"location='location',"_
&"type='thetype',"_
&"committee='committee',"_
&"committee2='committee2',"_
&"info='info'"

Set con = Server.CreateObject("ADODB.Connection")
con.Open newsConn

' Executing the sql insertion code
con.Execute sql

' Done. Now Close the connection
con.Close
Set con = Nothing


%>

[schizo]

You're using the UPDATE syntax instead of INSERT. Check out http://www.devguru.com/technologies/jetsql/16884.asp

[JayM]

sql="INSERT INTO hearings (title, headline, time, date, location, type, committee, committee2, info) VALUES (title, head, fulltime, thedate, location, thetype, committee, committee2, info)"

[calliepeck]

I get the same syntax errors with regular insert syntax as well. This is the same syntax I use with php and it works beautifully...

[calliepeck]

jay M...still no good. I cannot figure out what's up.

[JayM]

You are using reserved words in your table, which is likely why it's giving you these errors. For example, time is a reserved word. You may not use that. Change the names to non-reserved words.

Here are your reserved words:
Time
Date
Type


Here's the complete list of reserved words (http://support.microsoft.com/default.aspx?scid=kb;en-us;286335).

Cheers

[schizo]

Make sure your spacing is correct too, in your example you had SET running next to title. Also, you seem to be passing string values instead of your variable values. Here's an example with 1 field.


sql = "INSERT INTO hearings (title) VALUES ('" & title & "')"

[JayM]

Actually, what he has done is correct. The request.form("title") retrieves a string, and inserts that string as a value. If title was a number on the other hand, you would remove the quotes.