sql date parameterized query asp classic

Question

I am trying to save a date to a SQL table using a parameterised query in ASP classic.

The field is set to type date and I have tried things like

Set sqlParameters(0)=cmdCreateParameterObj.CreateParameter("@DocDate", adDbDate, adParamInput, , "20160928")

The error I get is

Application uses a value of the wrong type for the current operation.

I have tried changing adDbDate to other things but no luck so far.

Any ideas?

Edit - this is the function I call which appends all of my parameters stored in the array:

Function executeSQL(sqlQuery, allParameters)
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = DB 
cmd.CommandText = sqlQuery 
cmd.CommandType = adCmdText 
cmd.NamedParameters = true 
cmd.Prepared = true 
For p=0 To UBOUND(AllParameters) 
cmd.Parameters.Append AllParameters(p) 
Next 
Set TBL=cmd.Execute() 
Set cmd=Nothing 
End Function

Depending on what SQL RDBMS you are using `adDbDate` is likely the wrong parameter type. See [Data Type Mapping](http://www.carlprothman.net/Technology/DataTypeMapping/tabid/97/Default.aspx). More likely should be `adDBTimeStamp` *(135)*, plus I doubt any RDBMS will auto convert `20160928` to a datetime value. plus your syntax is wrong try `cmdCreateParameterObj.Parameters.Append cmdCreateParameterObj.CreateParameter("@DocDate", adDBtimeStamp, adParamInput, 8, "2016-09-28")`. — user692942, Sep 28 '16 at 18:23
Thanks for this - changing to adDbTimeStamp and adding hyphens to the date did the trick. — Ed Mozley, Sep 28 '16 at 18:59
I have a function for executing my sql commands with paremeters — Ed Mozley, Sep 28 '16 at 18:59
Function executeSQL(sqlQuery, allParameters) Set cmd = Server.CreateObject("ADODB.Command") Set cmd.ActiveConnection = DB cmd.CommandText = sqlQuery cmd.CommandType = adCmdText cmd.NamedParameters = true cmd.Prepared = true For p=0 To UBOUND(AllParameters) cmd.Parameters.Append AllParameters(p) Next Set TBL=cmd.Execute() Set cmd=Nothing End Function — Ed Mozley, Sep 28 '16 at 18:59
Thought that might be it, would be useful though for others and to give some context to the question if you [added that code into your question](http://stackoverflow.com/posts/39754916/edit) rather then posting it in the comments. — user692942, Sep 28 '16 at 19:01

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

The syntax for defining a Parameter on an ADODB.Command is not ideal (having said that OP has since confirmed that they pass the parameters into another function, so this approach is fine).

Instead try this;

Dim cmd

Set cmd = Server.CreateObject("ADODB.Command")
With cmd
    'Set CommandType, ActiveConnection etc.
    '...
    'Adding parameters
    Call .Parameters.Append(.CreateParameter("@DocDate", adDBTimeStamp, adParamInput, 8, "2016-09-28"))
    '...
    'Execute and return results
End With
'...
'After use release memory
Set cmd = Nothing

^{^... - Denotes other required code to make the ADODB.Command object work, as question is quite limited on details, this information is left for the OP to workout (plenty of resources on SO that show how to do this).}

Also changed the data type named constant to adDBTimeStamp (135) as most RDBMSs use this over adDBDate (133).

Useful Links

A: ADO Command Parameter Not Passing to Stored Procedure or Stored Procedure 'Ignoring' Parameter (details using Named Constants and correctly structuring Parameters).
Data Type Mapping (Most useful link I've ever found for ADO named constants and how they map to data types in various RDBMSs).
A: Using Stored Procedure in Classical ASP .. execute and get results (Might find this useful for structuring ADODB.Command parameterised queries).

sql date parameterized query asp classic

1 Answers1

Useful Links