Syntax error in INSERT INTO statement

[Gérard Leclercq]

ACCESS
First fields are TEXT, last 2 are Numbers
The name of the fields are correct.


Dim MyConn
Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath
("/xdata/sc.mdb")

Sql = "INSERT INTO sc (" & _
"session,sessionLanguage,sessionProductReferentie," & _
"sessionProductGroep,sessionProductSubGroep," & _
"sessionProductTaal1,sessionProductTaal2,sessionProductTaal3," & _
"sessionProductAantal,sessionProductWKP)" & _
" VALUES (" & _
Chr(34) & Application("id") & Chr(34) & "," & _
Chr(34) & Request.form("taal") & Chr(34) & "," & _
Chr(34) & Request.form("referentie") & Chr(34) & "," & _
Chr(34) & Request.form("groep") & Chr(34) & "," & _
Chr(34) & Request.form("subgroep") & Chr(34) & "," & _
Chr(34) & Replace(Request.form("taal1"),Chr(34)," ") & Chr(34) & "," & _
Chr(34) & Replace(Request.form("taal2"),Chr(34)," ") & Chr(34) & "," & _
Chr(34) & Replace(Request.form("taal3"),Chr(34)," ") & Chr(34) & "," & _
Request.form("aantal") & "," & _
Replace(Request.form("wkp"),",",".") & ")"

MyConn.Execute sql
Set MyConn=Nothing

If i print the SQL string it looks like:

INSERT INTO sc
(session,sessionLanguage,sessionProductReferentie,sessionProductGroep,sessionProductSubGroep,sessionProductTaal1,sessionProductTaal2,sessionProductTaal3,sessionProductAantal,sessionProductWKP)
VALUES
("81.165.236.141-1/02/2005-20:00:34-804105034","N","22102","22","10HA","GEKLOVEN
RIET","ROSEAU PAR 1/2 KG","",1,9.8)

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

Can somebody point me what i'm doing wrong?

Thx, Gérard.

 

[Paxton]

ACCESS
First fields are TEXT, last 2 are Numbers
The name of the fields are correct.


Dim MyConn
Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath
("/xdata/sc.mdb")

Sql = "INSERT INTO sc (" & _
"session,sessionLanguage,sessionProductReferentie," & _
"sessionProductGroep,sessionProductSubGroep," & _
"sessionProductTaal1,sessionProductTaal2,sessionProductTaal3," & _
"sessionProductAantal,sessionProductWKP)" & _
" VALUES (" & _
Chr(34) & Application("id") & Chr(34) & "," & _
Chr(34) & Request.form("taal") & Chr(34) & "," & _
Chr(34) & Request.form("referentie") & Chr(34) & "," & _
Chr(34) & Request.form("groep") & Chr(34) & "," & _
Chr(34) & Request.form("subgroep") & Chr(34) & "," & _
Chr(34) & Replace(Request.form("taal1"),Chr(34)," ") & Chr(34) & "," & _
Chr(34) & Replace(Request.form("taal2"),Chr(34)," ") & Chr(34) & "," & _
Chr(34) & Replace(Request.form("taal3"),Chr(34)," ") & Chr(34) & "," & _
Request.form("aantal") & "," & _
Replace(Request.form("wkp"),",",".") & ")"

MyConn.Execute sql
Set MyConn=Nothing

If i print the SQL string it looks like:

INSERT INTO sc
(session,sessionLanguage,sessionProductReferentie,sessionProductGroep,sessionProductSubGroep,sessionProductTaal1,sessionProductTaal2,sessionProductTaal3,sessionProductAantal,sessionProductWKP)

("81.165.236.141-1/02/2005-20:00:34-804105034","N","22102","22","10HA","GEKLOVEN

RIET","ROSEAU PAR 1/2 KG","",1,9.8)

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

Can somebody point me what i'm doing wrong?

Thx, Gérard.

Text fields in Access should be delimited by single quotes, not double
quotes.

Paxton

 

[Mark Schupp]

Use single quotes to delimit your character strings.

('81.165.236.141-1/02/2005-20:00:34-804105034',

If that doesn't help try removing columns from the statement to see which
one the system doesn't like.

 

[Gérard Leclercq]

Double Quotes are not the reason, if have a lot of INSERTs with "". If i
alter the string without the fields it works fine. So i think, have not find
time this morning to test, that maybe the field 'session' is a reservate
word?? Gérard.

PS: Double quotes are for the namy Last Names with ' in my region, like
D'Haese or D'Hondt.

 

[Bob Barrows [MVP]]

Double Quotes are not the reason, if have a lot of INSERTs with "". If i
alter the string without the fields it works fine. So i think,
have not find time this morning to test, that maybe the field
'session' is a reservate word?? Gérard.

http://aspfaq.com/show.asp?id=2080

A good test is to open your database in Access, create a new query in design
view without choosing a table from the dialog, switch to SQL View, paste
your sql statement in and try to run it. If it runs with no problem, then
you probably have a reserved keyword issue.

You should consider using a saved parameter query so you don't have to worry
about delimiters and apostrophes:
http://www.google.com/groups?hl=en&[email protected] Barrows--Microsoft MVP - ASP/ASP.NETPlease reply to the newsgroup. This email account is my spam trap so Idon't check it very often. If you must reply off-line, then remove the"NO SPAM"

 

[Gérard Leclercq]

Thx Bob, as i suspected 'session'.
The other link gives me and error to the page for the saved parameters.
Gérard.

 

[Bob Barrows [MVP]]

Try this:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&[email protected]

Bob