Syntax error with an Insert command Please Help

[HakanAzaklioglu]

The error message I am getting is:
===========================================
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
===========================================



Here is my code:
===========================================
set RSEmails = conn.Execute("select EmailAddress from CIEmpDir " _
& "where SSN = '" & REQUEST.FORM("ddIND") & "'")
DO UNTIL RSEmails.EOF
Dim objCDO3
Set objCDO3 = Server.CreateObject("CDONTS.NewMail")
objCDO3.From = REQUEST.FORM("ddFROM")
objCDO3.To = RSEmails("EmailAddress")
objCDO3.CC = ""
objCDO3.Subject = REQUEST.FORM("txtSUBJ")
objCDO3.Body = REQUEST.FORM("msgMSG")
objCDO3.Send()
Set objCDO3 = Nothing
RSEmails.MOVENEXT
LOOP
conn.Execute "insert into MAILBLASTLOG (FROM, SENDTO, SUBJECT, MESSAGE, DATE, TIME, IP) " _
& "values (" _
& "'" & Request.Form("ddFROM") & "', " _
& "'" & Request.Form("ddIND") & "', " _
& "'" & Request.Form("txtSUBJ") & "', " _
& "'" & Request.Form("msgMSG") & "', " _
& "'" & DATE() & "', " _
& "'" & TIME() & "', " _
& "'" & REQUEST.SERVERVARIABLES("REMOTE_HOST") & "')"
Response.Redirect "http://www.hy-tech.net"
===========================================

Kind Regards,
Hakan

Just a wild thought, but is it possible that one of your text fields has a single quote character in it? This might throw off your SQL statement. I get a different kind of error message when I test this but I'm using a different kind of connection.

Good luck,
Paul

[HakanAzaklioglu]

Nope... just plain text... thanks for the insight... I thought it might be an error with sytax but I can't find it...

Kind Regards,
Hakan

Ah well. I don't see a syntax error either. Another thing you can do is build up your SQL as a string - that way you can write it to your page and see exactly what you're trying to execute. You might see something with the completely built SQL string that you're not seeing in the code.

Good luck,
Paul

[Kagey]

I know what your problem or part of your problem is. Instead of putting single quotes around your date and time, put a # sign. for access the date delimeter is # for sql server it is a single quote.

of course, i am assuming the datatype of the DATE and TIME fields are date and time. if not, i have no clue.


good luck.

Kyle

I thought of that too but I think single quotes are fine for an INSERT INTO for Access. I tested it with VBScript/ADO/ASP and it worked fine with my test database. Couldn't hurt to try though.

Paul

[HakanAzaklioglu]

Like this??
=======================
conn.Execute "insert into MAILBLASTLOG (FROM, SENDTO, SUBJECT, MESSAGE, DATE, TIME, IP) " _
& "values (" _
& "'" & Request.Form("ddFROM") & "', " _
& "'" & Request.Form("ddIND") & "', " _
& "'" & Request.Form("txtSUBJ") & "', " _
& "'" & Request.Form("msgMSG") & "', " _
& "'# & DATE() & #', " _
& "'# & TIME() & #', " _
& "'" & REQUEST.SERVERVARIABLES("REMOTE_HOST") & "')"
=======================

Thank you

Wrap your date field like this:

"#" & recordset("SomeDateField") & "#"

The #'s go where the single quotes would go for a text field.

Paul

[Kagey]

You just have to replace the single quotes with #'s. That should do the trick

[HakanAzaklioglu]

I still get this error message:
==============================================
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
==============================================

And here is the code change as suggested. Please verify that this is what you were telling me to change...

Oh another thing I change the insert to use just the txt boxes on the page, what I mean by that is that I took out the date and time and the ip request that you will find on the last three lines of the insert command with just plain boxes to see if that was my problem but when I run the page I still get the same error message. It acts like it is not even getting down to that area of the insert command.

Code:
==============================================
elseif Request.Form("SendTo") = "Individual" then
set RSEmails = conn.Execute("select EmailAddress from CIEmpDir " _
& "where SSN = '" & REQUEST.FORM("ddIND") & "'")
DO UNTIL RSEmails.EOF
Dim objCDO3
Set objCDO3 = Server.CreateObject("CDONTS.NewMail")
objCDO3.From = REQUEST.FORM("ddFROM")
objCDO3.To = RSEmails("EmailAddress")
objCDO3.CC = ""
objCDO3.Subject = REQUEST.FORM("txtSUBJ")
objCDO3.Body = REQUEST.FORM("msgMSG")
objCDO3.Send()
Set objCDO3 = Nothing
RSEmails.MOVENEXT
LOOP
conn.Execute "insert into MAILBLASTLOG (FROM, SENDTO, SUBJECT, MESSAGE, DATE, TIME, IP) " _
& "values (" _
& "'" & Request.Form("ddFROM") & "', " _
& "'" & Request.Form("ddIND") & "', " _
& "'" & Request.Form("txtSUBJ") & "', " _
& "'" & Request.Form("msgMSG") & "', " _
& "#" & DATE() & "#, " _
& "#" & TIME() & "#, " _
& "'" & REQUEST.SERVERVARIABLES("REMOTE_HOST") & "')"
Response.Redirect "http://www.hy-tech.net"
end if
==============================================

Originally posted by HakanAzaklioglu
Like this??
=======================
conn.Execute "insert into MAILBLASTLOG (FROM, SENDTO, SUBJECT, MESSAGE, DATE, TIME, IP) " _
& "values (" _
& "'" & Request.Form("ddFROM") & "', " _
& "'" & Request.Form("ddIND") & "', " _
& "'" & Request.Form("txtSUBJ") & "', " _
& "'" & Request.Form("msgMSG") & "', " _
& "'# & DATE() & #', " _
& "'# & TIME() & #', " _
& "'" & REQUEST.SERVERVARIABLES("REMOTE_HOST") & "')"
=======================

Thank you

Wow, I've been here many times! I've had -many- INSERT statements fail and usually it was something as simple as the date delimiter you guys mentioned. Sometimes it was something dumb like a field name error. Sometimes it was me trying to put the wrong type of data into a database field. Are you positive all your field names are correct and that you're putting string data into string fields, date data into date fields, numeric data into numeric fields? Also, are you trying to put a very long string into a field that is too small?

Post a reply when you figure it out. I'm interested.

[HakanAzaklioglu]

I can't find anything wrong with it... I even tried to just post simple data from the form like somebodys name with no special character in to every field of the insert command and no luck it acts like it doesn't make it past the first line of the insert command always the same damn sytax error message...

As far as the database goes it is a Access 2000 database and all the columns are set to text. The reason for that is I have had fewer problems with inserts if I set all db columns to text...

I dunno... any help is apreciated and all of you that have posted... Thanks alot for your time...

Kind Regards,
Hakan

Originally posted by HakanAzaklioglu
I can't find anything wrong with it... I even tried to just post simple data from the form like somebodys name with no special character in to every field of the insert command and no luck it acts like it doesn't make it past the first line of the insert command always the same damn sytax error message...

As far as the database goes it is a Access 2000 database and all the columns are set to text. The reason for that is I have had fewer problems with inserts if I set all db columns to text...

I dunno... any help is apreciated and all of you that have posted... Thanks alot for your time...

Kind Regards,
Hakan

Did you check your text field lengths? Are you trying insert something big in small field? Also, do you have any fields set to 'NOT allow zero length' and they are -not- getting any data?

[HakanAzaklioglu]

I will check again... Thank you for your time... I will reply back in a few hours...

Kind Regards,
Hakan