Error converting data type datetime to smalldatetime

[MichaelaLee]

Hi Everyone,
I'm having a little problem adding a record that contains 3 smalldatatime fields. I'm getting the following error:
Error converting data type datetime to smalldatetime.

Here is the code:
Set mCommand = New ADODB.Command
mCommand.CommandType = adCmdStoredProc
mCommand.CommandText = "nf_AddOrder"
Set mParam = mCommand.CreateParameter("@OrderId", adInteger, adParamInput)
mParam.Value = nOrderid
mCommand.Parameters.Append mParam
Set mParam = mCommand.CreateParameter("@ProgramId", adVarChar, adParamInput, 10)
mParam.Value = nProgramId
mCommand.Parameters.Append mParam
Set mParam = mCommand.CreateParameter("@OrderDate", adDBTimeStamp, adParamInput)
mParam.Value = nOrderDate
mCommand.Parameters.Append mParam
Set mParam = mCommand.CreateParameter("@RequiredDate", adDBTimeStamp, adParamInput)
mParam.Value = nRequiredDate
mCommand.Parameters.Append mParam
Set mParam = mCommand.CreateParameter("@DeleiveredDate", adDBTimeStamp, adParamInput)
mParam.Value = nDeleiveredDate
mCommand.Parameters.Append mParam

mCommand.ActiveConnection = ado
mCommand.Execute

I get the error as soon the Execute function is called. The fields and the parameters of the stored procedures are declared as SmallDateTime. Has anyone seen this one yet. Thanks for any info.
Michael Lee

 

[bboffin]

Have you tried getting ADO to create the parameters for you?

If you call:

mCommand.Parameters.Refresh

just after assigning the commandtext it will remove the need for all the CreateParameter calls and will guarantee that the parameter data types and sizes are set correctly.

All you then need to do is assign the value for each parameter.

The only downside of the Refresh call is that it involves a round trip to the server to get the information but this is rarely noticeable.

Could you also identify which of the dates is causing the problem?

 

[MichaelaLee]

Hi bboffin,
Thanks for the reply. I'm not sure which one is causing the error. The error is not specific anough. I also need to be able to pass a null value to these fields if possible. If nothing else I can pass a 1/1/1753 since this is the lowest value that SQL will store.
For the parameters, where you suggesting this:
Set mParam = mCommand.CreateParameter("@OrderId", adInteger, adParamInput)
mParam.Value = nOrderid
mCommand.Parameters.Append mParam
mCommand.Parameters.Item(0).Name = "@ProgramId"
mCommand.Parameters.Item(0).Type = adVarChar
mCommand.Parameters.Item(0).mParam.Value = nProgramId
mCommand.Parameters.Refresh

THanks for the reply.
Michael

 

[MichaelaLee]

Hi bboffin,
I did what you suggested as a test and still getting the same error message. Here is the code I changed to:
Set mCommand = New ADODB.Command
mCommand.ActiveConnection = ado
mCommand.CommandType = adCmdStoredProc
mCommand.CommandText = "nf_AddOrder"
mCommand.Parameters.Refresh
mCommand.Parameters.Item("@OrderId").Value = nOrderid
mCommand.Parameters.Item("@ProgramId").Value = nProgramId
mCommand.Parameters.Item("@OrderDate").Value = nOrderDate
mCommand.Parameters.Item("@RequiredDate").Value = nRequiredDate
mCommand.Parameters.Item("@DeleiveredDate").Value = nDeleiveredDate
mCommand.Execute
AddOrder = True

What do you think?
THanks for your time.
Michael

 

[fredericofonseca]

Move mCommand.ActiveConnection = ado
to after
mCommand.CommandText = "nf_AddOrder"

and remove
mCommand.Parameters.Refresh

I personally prefer to pass the datetimes fields as a string, and then to the convert within the SP




Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com