Syntax error on SQL INSERT

[ghetto_banjo]

Here is my SQL string:
strNoteSQL = "INSERT INTO tblNotes (ClaimID, Note, NoteDATE) VALUES ("
& intClaim & ", '" & strNote & "', '" & dteCurrentDate & "'); "


ClaimID is integer, Note is text, NoteDate is date/time

There is also a NoteID field in tblNotes which is primary key. I am
excluding this because it is an autonumber.


I get the "syntax error in INSERT-INTO Statement" error.

Any ideas?

 

[Stefan Hoffmann]

Here is my SQL string:
strNoteSQL = "INSERT INTO tblNotes (ClaimID, Note, NoteDATE) VALUES ("
& intClaim & ", '" & strNote & "', '" & dteCurrentDate & "'); "
ClaimID is integer, Note is text, NoteDate is date/time
I get the "syntax error in INSERT-INTO Statement" error.

You need to specify a correct Date/Time literal:

http://allenbrowne.com/ser-36.html

Also ensure, that strNote doesn't contain a single quote.

mfG
--> stefan <--

 

[Dirk Goldgar]

Here is my SQL string:
strNoteSQL = "INSERT INTO tblNotes (ClaimID, Note, NoteDATE) VALUES ("
& intClaim & ", '" & strNote & "', '" & dteCurrentDate & "'); "


ClaimID is integer, Note is text, NoteDate is date/time

There is also a NoteID field in tblNotes which is primary key. I am
excluding this because it is an autonumber.


I get the "syntax error in INSERT-INTO Statement" error.

Any ideas?

Try:

strNoteSQL = _
"INSERT INTO tblNotes " & _
"(ClaimID, Note, NoteDATE) VALUES (" & _
intClaim & ", """ & strNote & """, #" & _
Format(dteCurrentDate, "mm\/dd\/yyyy") & "#)"

I changed the embedded text delimiter to the double=quote ("), just in case
strNote might contain the apostrophe/single-quote, and I put date delimiters
(#) around the date/time value, which I formatted to MM/DD/YYYY format, to
avoid any ambiguity.

 

[ghetto_banjo]

Ok I placed #s around the date instead of single quotes and still
getting an error.

strNoteSQL = "INSERT INTO tblNotes (ClaimID, Note, NoteDATE) VALUES ("
& intClaim & ", '" & strNote & "', #" & dteCurrentDate & "#); "


In fact, I get the same error with this:
strNoteSQL = "INSERT INTO tblNotes (ClaimID, Note, NoteDATE) VALUES
(15, 'test', #2/16/2009#); "


Now I am at a complete loss. Where should I troubleshoot?

 

[Stefan Hoffmann]

hi,

In fact, I get the same error with this:
strNoteSQL = "INSERT INTO tblNotes (ClaimID, Note, NoteDATE) VALUES
(15, 'test', #2/16/2009#); "

Now I am at a complete loss. Where should I troubleshoot?

http://sqlserver2000.databases.aspfaq.com/what-are-reserved-access-odbc-and-sql-server-keywords.html

Use [Note].


mfG
--> stefan <--

 

[ghetto_banjo]

Stefan!!!!!! you did it!

i should have realized it was something so obvious. Thanks to
everyone for the help!!! I needed the # # around the date and I needed
the [ ] around the (obviously?) reserved word of Note!




Thanks again all.