Syntax error in INSERT INTO statement 1

[KingElvis]

When trying to insert a record into an Access database, I get the following error message:

strSQL:INSERT INTO shiur (username,slanguage,shiurFor,shiurType,sTitle,mTitle,mFirst,mLast,shiurFreq,date,time,sduration,venue,city,contact,comments,add_date,status) VALUES('smith',2,1,3,'Something interesting',4,'PJ','Smith',5,'2/26/26','1230',2,'home',1,'Mr Jones','Will begin promptly','9/26/2004 4:04:18 PM',3)
Error:-2147217900|[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

I've double checked that each ACCESS field is correctly labelled as a "TEXT" or "NUMBER" field. I have also confirmed that only those fields to be entered as TEXT have the necessary single-quotation marks.

Where is the error?? Thanks in advance for your help.

My code is:

strSQL = "INSERT INTO shiur (username,slanguage,shiurFor,shiurType,sTitle,mTitle,mFirst,mLast,shiurFreq,date,time,sduration,venue,city,contact,comments,add_date,status) VALUES(" _
& "'" & Trim(Request.Cookies(consts_CollectionName_User)("CurrentUser")) & "'," _
& Request.Form("slanguage") & "," _
& Request.Form("shiurFor") & "," _
& Request.Form("shiurType") & ",'" _
& Trim(Request.Form("sTitle")) & "'," _
& Request.Form("mTitle") & ",'" _
& Trim(Request.Form("mFirst")) & "','" _
& Trim(Request.Form("mLast")) & "'," _
& Request.Form("shiurFreq") & ",'" _
& wholeDate & "','" _
& Trim(Request.Form("stime")) & "'," _
& Request.Form("sDuration") & ",'" _
& Trim(Request.Form("venue")) & "'," _
& Request.Form("vcity") & ",'" _
& Trim(Request.Form("contact")) & "','" _
& Replace(Trim(Request.Form("comments")),"'","`") & "','" _
& Now() & "'," _
& constSF_Status_Active _
& ")"

Call DAL_ExecuteSQL(strSQL)

 

[onpnt]

before looking to far into it, dates must be enclosed in # with jet

try the following steps to see where the other errors may be
faq333-4896

___________________________________________________________________
[sub]
onpnt.com
SELECT * FROM programmers WHERE clue > 0
(0 row(s) affected) -->faq333-3811
[/sub]

 

[KingElvis]

Yes, but the ACCESS database has the field defined and TEXT and I'm entering it as text... not as a date!

 

[Genimuse]

Some thoughts:

1. Is the field mTitle really numeric? Just sounds like a string.

2. Are all three of your date/time fields strings? That's the one called "date", the one called "time", and the one called "add_date".

3. As there are both Date and Time commands, Access may not be understanding those field names properly. Put square brackets around them in your sql, like this:

shiurFreq,[date],[time],sduration

4. Do all of your strings fit in their fields? For example, noting that the field "date" seems to be expecting a fairly short date, is "add_date" expecting something so long?

It's a bummer that your error message contains nothing more than the fact that there's a syntax error.

 

[KingElvis]

The square brackets did it!

Two hours of struggling completed! Thanks

 

[onpnt]

glad to hear you got it working with Genimuse's help.

my lag is the fact of the same thing being asked over and over and it's kind of like the wolf and the boy story. I guess you get so used to the same question with the same old answer you don't read to far into it.



___________________________________________________________________
[sub]
onpnt.com
SELECT * FROM programmers WHERE clue > 0
(0 row(s) affected) -->faq333-3811
[/sub]

 

[Genimuse]

Good to see you around again, onpnt!

And I completely understand what you mean.