syntax error

Adam H-W · January 23rd, 2004, 11:15 AM

Hi there

I'm getting the following error with my code

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

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

I'm not sure why this is happening:

I've got a sproc on the line with the error:

set strSQL = con.execute("[up_insert_enq] '" & strTitle & "' , '" & strName & "' , '" & strEmail & "' , '" & strTel & "' , '" & strContact & "' , '" & strEnquiry & "', '" & strDate & "'")

and above I've declared strDate as

strDate = now()

Hoping someone can help

thanks

Adam

jrwlkn · January 23rd, 2004, 11:23 AM

"#' & strDate & '#"

I believe this is correct to pass a date

J

pgtips · January 23rd, 2004, 11:28 AM

# is only for Access dates. SQL Server uses '

Sounds like a date format problem. Can you do a Response.Write strSQL so we can see what date format is being sent to the db?

Personally, I always use a command object to run the stored proc, coz then you can set a parameter's type as a datetime and pass it a date variable and all goes swimmingly.

hth
Phil

jrwlkn · January 23rd, 2004, 11:31 AM

Thanks for the correction...on other thought...is now() valid syntax or should strDate=Date

where Date is retrieved from the server...particularly if the database date field is a date only and not a date/time

JW

Adam H-W · January 23rd, 2004, 11:55 AM

All sorted now - thanks for your input guys.

Phil, how do you use a command object to run the sproc?

thanks

Adam

pgtips · January 26th, 2004, 06:23 AM

Hi Adam, here's an example of using a command object to run a stored proc

Code:

    '...code here to open a connection
    Set adoCommand = Server.CreateObject("ADODB.Command")
    With adoCommand
        .ActiveConnection = adoConnection ' connection object here
        .CommandType = adCmdStoredProc
        .CommandText = "stored procedure name"

        ' parameters go here 
        '(ParamName, DataType, Direction, Size (only for string types), value)
        .Parameters.Append .CreateParameter("AnInt", adInteger, adParamInput, , 0)
        .Parameters.Append .CreateParameter("AChar", adChar, adParamInput, 8, "ABCDEFGH")
        .Parameters.Append .CreateParameter("ADate", adDBTimeStamp, adParamInput, , Now)
        .Parameters.Append .CreateParameter("AVarChar", adVarChar, adParamInput, 80, "x")

        ' now run the stored proc - use adExecuteNoRecords for INSERT/UPDATE where
        ' no recordset is returned.  For other types like SELECT, first create a 
        ' Recordset object, then use
        ' Set rsObj = .Execute

        .Execute , , adExecuteNoRecords
    End With

By passing dates in this way you avoid the problem of the date being converted to a string and back to a date again.

hth
Phil

Adam H-W · January 26th, 2004, 09:10 AM

Great, thanks Phil.