Trouble with datetime Insert and Update

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trouble with datetime Insert and Update

Author

Topic

LanceOng
Starting Member

1 Post

Posted - 2006-04-07 : 13:30:59

I am trying to Insert or Update a record in MSSQL with a datetime variable which is modified using the DateAdd function.

Basically, I have a table of Coupons which need to expire 'x' days in the future. When I use GetDate() for the Issue Date, I have no problems. But then, when I use DateAdd to return a date in the future, I can not Insert or Update this result into the record.

I get various errors having to do with type mismatch or function not found, etc.

Can you see what I might be doing wrong?

Here's the code snippit:
<%
if(Recordset2.Fields.Item("SerialNo").Value <> "") then UpdateExpire__SerNo = Recordset2.Fields.Item("SerialNo").Value
if(DateAdd("d",2,Now) <> "") then UpdateExpire__XD = DateAdd("d",2,Now)
%>
<%
set UpdateExpire = Server.CreateObject("ADODB.Command")
UpdateExpire.ActiveConnection = MM_FreeVB_STRING
UpdateExpire.CommandText = "UPDATE dbo.Coupon SET ExpireDate = " + Replace(UpdateExpire__XD, "'", "''") + " WHERE SerialNo = " + Replace(UpdateExpire__SerNo, "'", "''") + ""
UpdateExpire.CommandType = 1
UpdateExpire.CommandTimeout = 0
UpdateExpire.Prepared = true
UpdateExpire.Execute()
%>

This produces this error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

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

And it errors at the UpdateExpire.Execute() line.

The code was generated by Dreamweaver.

Thanks.

Lance

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-07 : 14:00:18

Lance,

If u r talking about MS SQL Server's DateAdd function, ur syntax is incorrect.

I think that u mixed both SQL & ASP syntaxes.
The advice I can give u is
The command text after assigning all variables etc, should confirm to T-SQL syntax.

U can check that by
Placing a Label in the page and assign the Command Text string in to that and copy & paste that in Query Analyzer, run it and check

Srinika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources