datetime stored procedure parameters

[davidrobin]

I have a stored procedure that has a datetime SQL data type. How do I pass a date to the parameter?

cmd.Parameters.Append cmd.CreateParameter("mydate", adDate,, "'" &
CStr(Year(datInd)) & " - " & CStr(Month(datInd)) & " - " & CStr(Day(datInd))
& "'")

The above code comes up with a type mismatch

any ideas? David
Visual Basic 6 Ent

 

[zemp]

I had to play this this a little and this is what I came up with. I have SQL Server 2000 as my database and I am passing a date value to a stored procedure that is looking for a parameter of type'datetime'.

Here is how I create my parameter in VB

.Parameters.Append .CreateParameter("mydate", adVarChar, adParamInput, 20)
.Parameters("mydate&quot.value = CStr(DTPicker1.value)

I am using ADO 2.6. The value supplied by 'DTPicker1.value' can also be replaced by a variable of the type 'date'.
Thanks and Good Luck!

zemp

 

[MarkSweetland]

The problem with your original statement is that you missed a comma in the CreateParameter argument list and were trying to pass the string representing your date in the position reserved for the Size argument. Therefore the type mismatch error. But, that corrected, you'll run into a "Application uses a value of the wrong type ..." error when you concatenate the single quotes to the date parts as you formulate the date as a string and pass to the Createparameter. Because the type is set to adDate, it expects a variable of type Date to be passed in order to assign it to the parameter value.

Remove the single quotes and pass a converted date variable to the CreateParameter:


strDate = CStr(Year(datInd)) & " - " & CStr(Month(datInd)) & " - " & CStr(Day(datInd))
dtDate = CDate(strDate)
cmd.Parameters.Append cmd.CreateParameter("mydate", adDate, , , dtDate)


Mark