I have an SQL error [SQL Server]Incorrect syntax near

Question

I have an SQL error [SQL Server]Incorrect syntax near

David Eisenbacher-375338

SSC Enthusiast

Points: 197
More actions
October 16, 2006 at 8:43 am

#97881

Hello
I am running an ASP page that writes to a DB the part of the ASP page is
rsLogObj.Fields("IssueDate") = Date()
rsLogObj.Fields("IssueTime") = Time()
THe SQL DB has the columns of
IssueDate (datetime,null)
IssueTime (Char(50),null)
And the error I am getting is
E000 exec sp_cursor 180150025,4,1,N'',@ProfileId='{1324B9A5-A67A-414B-9389-3B2C4D1F48D7} ',@NumPlays=-1,@NumBurns=0,@NumTransfers=0,@NumSDMITransfers=0,@Expiration=' ',@ExpirationOnStore=-1,@ExpirationOnPlay=720,@DeleteOnRollback=1,@DisableOnRollback=1,@MinAppSecurity=500,@IssueDate=''2006-10-16 00:00:00:000'',@IssueTime='9:58:46 AM ',@AllowBackup=0,@KID='EZV2WW37eJseEGw6FGhBbkVw ',@LicenseVersion='10 ',@acct_type='Pro ',@lic_type='post ' 1 [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '2006'.
Any help would be lovely.

DE

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff-345961 SSC Veteran Points: 233 More actions · Answer 1

You have an extra quote before and after ''2006-10-16 00:00:00:000'' , check your format before you pass it to the proc......

David Eisenbacher-375338 SSC Enthusiast Points: 197 More actions · Answer 2

But how is it getting the extra quote, my ASP code does not have an extra quote.

Jeff-345961 SSC Veteran Points: 233 More actions · Answer 3

Sounds like you need to concatenate with single quotes, something like this or however it applies to what you are doing.

("select ? from ? where ??=" & " ' " & ? & " ' " & "and ? =" & " ' " & ? & " ' ")

David Eisenbacher-375338 SSC Enthusiast Points: 197 More actions · Answer 4

Hello

OK i belive you are thinking this is harder then it is,

In asp I have a bit of code

rsLogObj.Fields("IssueDate") = Date()

rsLogObj.Fields("IssueTime") = Time()

all this does is take the current date and time and writes it into my DB, under the colums of IssueDate and IssueTime.

I dont get an error per say, in the DB I can see the vaules getting inserted just fine.

BUt when I run sql server profiler, in SQL 2005, it tells me there is bad systax.

I dont see where persay, it just tells me "Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '2006'. "

But I dont know how its getting the extra quote.

I belive its more ASP issue then SQL, but since my column is in datetime and I am just giving it date is that why its saying the error?

DE

donne andres Valued Member Points: 52 More actions · Answer 5

Try using single quotes instead of double quotes....

@IssueDate= '2006-10-16 00:00:00:000'

Just like what you did with @IssueTime.....

,@IssueTime='9:58:46 AM'

vadba SSChampion Points: 11133 More actions · Answer 6

Actaully, the error message originally posted surrounded the date with two single quotes on each end, not a one double-quote. Maybe ASP or the driver (ADO?) is adding them for some reason. I checked for related bugs, but didn't find anything. Without knowing more about the code, I would look for problems converting the date string to datetime.

What do you get if after the rsLogObj.Fields("IssueDate") = Date()

you execute Response.Write rsLogObj.Fields("IssueDate") ?

As far as I know, ASP does not add quotes automatically. Can you post a little bit more of the ASP code?