ERROR: Incorrect syntax...

[A1Pat]

Could someone help me understand where this problem come from.

my code:
mySQL = "UPDATE tblNewsLetters " _
& "SET newsDate = " & addDateDel(now()) & " , " _
& " newsDateInt= '" & dateInt(now()) & "', " _
& " newsSubj = '" & replace(newsSubj,"'","''")& "', " _
& " newsBody = '" & replace(newsBody,"'","''")& "' " _
& "WHERE idNews = " & idNews
set rs = objConn.Execute(mySQL)


This is how the code read:
UPDATE tblNewsLetters SET newsDate = #11/1/2006 5:09:22 PM# , newsDateInt= '20061101170922', newsSubj = 'TESTING', newsBody = 'testing newsletter email' WHERE idNews = 1

and, this is the ERROR:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '5'.

/admin/newsletters/news_exec.asp, line 195

thank for the help.

 

[onpnt]

[red]Reference 8[/red]
faq333-3048

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

 

[petermeachem]

Almost certainly the date?
Sql Server wants single quotes round dates, Access likes hashes.

 

[A1Pat]

Thank you both onpnt and petermeachem for your input, before I show you an error I got for using the single quotes around dates, I'd like to show you this function addDateDel I had as a template

function addDateDel(dateVal)
if dbType = 0 then 'MS Access
dateVal = "#" & dateVal & "#"
else 'SQL Server
dateVal = "'" & dateVal & "'"
end if
addDateDel = dateVal
end function

Therefore, when I add the single quotes to the date, this is the error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting datetime from character string.

/admin/newsletters/news_exec.asp, line 195

With or without single quotes give me ERROR, I wonder there's another way to go around this.

 

[petermeachem]

Well that is a different error isn't it?
I can't see anything wrong with the date particularly. Check the datatype of the column.
Your function seems to be altering the input value as well as the return? IS that ok?

 

[A1Pat]

Thank you all for helping out.

I tried not to use the function and I got passed the error. Not really sure why, though. I really want to have that function to be useful, but not sure what's caused such problem.

Again, thank you all.