Problems with Stored Procedure and Date

I apologize if this is a double post. I tried earlier and it didn't
appear to post.

I'm passing data to a stored procedure in SQL Server 2000 and am
having trouble with the date parameters. If I pass a date it works. If
I don't pass any data I get the following error:
ADODB.Command error '800a0d5d'
Application uses a value of the wrong type for the current operation.

The lines of error are:
.Parameters.Append .CreateParameter("@cDate", adDBTimeStamp,
adParamInput, 8, request.Form("XXcreationDate"))
.Parameters.Append .CreateParameter("@efDate", adDBTimeStamp,
adParamInput, 8, request.Form("NTEffectiveDate"))
.Parameters.Append .CreateParameter("@exDate", adDBTimeStamp,
adParamInput, 8, request.Form("NTExpirationDate"))

My ASP code is as follows:
With objCmd
.ActiveConnection = objConn
.CommandText = "spSaveContactDetails"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@id", adInteger,
adParamInput, 10, request.QueryString("ID"))
.Parameters.Append .CreateParameter("@ln", adVarChar,
adParamInput, 50, request.Form("RTlname"))
.Parameters.Append .CreateParameter("@fn", adVarChar,
adParamInput, 50, request.Form("RTfname"))
.Parameters.Append .CreateParameter("@emp", adInteger,
adParamInput, 10, request.Form("Rtemployer"))
.Parameters.Append .CreateParameter("@op", adVarChar,
adParamInput, 50, request.Form("NTofficePhone"))
.Parameters.Append .CreateParameter("@cp", adVarChar,
adParamInput, 50, request.Form("NTcellPhone"))
.Parameters.Append .CreateParameter("@title", adVarChar,
adParamInput, 50, request.Form("NTtitle"))
.Parameters.Append .CreateParameter("@unit", adVarChar,
adParamInput, 50, request.Form("NTunit"))
.Parameters.Append .CreateParameter("@email", adVarChar,
adParamInput, 50, request.Form("NTemailAddress"))
.Parameters.Append .CreateParameter("@empID", adVarChar,
adParamInput, 50, request.Form("NTemployeeid"))
.Parameters.Append .CreateParameter("@notes", adVarChar,
adParamInput, 4000, request.Form("NTnotes"))
.Parameters.Append .CreateParameter("@cDate", adDBTimeStamp,
adParamInput, 8, request.Form("XXcreationDate"))
.Parameters.Append .CreateParameter("@efDate", adDBTimeStamp,
adParamInput, 8, request.Form("NTEffectiveDate"))
.Parameters.Append .CreateParameter("@exDate", adDBTimeStamp,
adParamInput, 8, request.Form("NTExpirationDate"))
.Parameters.Append .CreateParameter("@webC", adInteger,
adParamInput, 1, intWebContact)
.Parameters.Append .CreateParameter("@newID", adInteger,
adParamOutput, 10)
.Execute, , adExecuteNoRecords
newID = .Parameters("@newID")
End With

My stored procedure is as follows:
CREATE PROCEDURE [dbo].[spSaveContactDetails]
@id int
,@ln nvarchar(50)
,@fn nvarchar(50)
,@emp int
,@op nvarchar(50) = NULL
,@cp nvarchar(50) = NULL
,@title nvarchar(50) = NULL
,@unit nvarchar(50) = NULL
,@email nvarchar(50) = NULL
,@empID nvarchar(50) = NULL
,@notes nvarchar(4000) = NULL
,@cDate dateTime = NULL
,@efDate dateTime = NULL
,@exDate dateTime = NULL
,@webC bit
,@newID int OUTPUT
AS
SET NOCOUNT ON


Can anyone see what I might be doing wrong?

Thank you,

--
Jerry

RE: Problems with Stored Procedure and Date

adDBTimeStamp is not datetime. Try changing your data type to an appropriate
date/time type.


ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/

Re: Problems with Stored Procedure and Date

"Jerry" wrote in message
news:1187966548.828306.10700@m37g2000prh.googlegroups.com...
>I apologize if this is a double post. I tried earlier and it didn't
> appear to post.
>
> I'm passing data to a stored procedure in SQL Server 2000 and am
> having trouble with the date parameters. If I pass a date it works. If
> I don't pass any data I get the following error:
> ADODB.Command error '800a0d5d'
> Application uses a value of the wrong type for the current operation.
>
> The lines of error are:
> .Parameters.Append .CreateParameter("@cDate", adDBTimeStamp,
> adParamInput, 8, request.Form("XXcreationDate"))
> .Parameters.Append .CreateParameter("@efDate", adDBTimeStamp,
> adParamInput, 8, request.Form("NTEffectiveDate"))
> .Parameters.Append .CreateParameter("@exDate", adDBTimeStamp,
> adParamInput, 8, request.Form("NTExpirationDate"))
>
> My ASP code is as follows:
> With objCmd
> .ActiveConnection = objConn
> .CommandText = "spSaveContactDetails"
> .CommandType = adCmdStoredProc
> .Parameters.Append .CreateParameter("@id", adInteger,
> adParamInput, 10, request.QueryString("ID"))
> .Parameters.Append .CreateParameter("@ln", adVarChar,
> adParamInput, 50, request.Form("RTlname"))
> .Parameters.Append .CreateParameter("@fn", adVarChar,
> adParamInput, 50, request.Form("RTfname"))
> .Parameters.Append .CreateParameter("@emp", adInteger,
> adParamInput, 10, request.Form("Rtemployer"))
> .Parameters.Append .CreateParameter("@op", adVarChar,
> adParamInput, 50, request.Form("NTofficePhone"))
> .Parameters.Append .CreateParameter("@cp", adVarChar,
> adParamInput, 50, request.Form("NTcellPhone"))
> .Parameters.Append .CreateParameter("@title", adVarChar,
> adParamInput, 50, request.Form("NTtitle"))
> .Parameters.Append .CreateParameter("@unit", adVarChar,
> adParamInput, 50, request.Form("NTunit"))
> .Parameters.Append .CreateParameter("@email", adVarChar,
> adParamInput, 50, request.Form("NTemailAddress"))
> .Parameters.Append .CreateParameter("@empID", adVarChar,
> adParamInput, 50, request.Form("NTemployeeid"))
> .Parameters.Append .CreateParameter("@notes", adVarChar,
> adParamInput, 4000, request.Form("NTnotes"))
> .Parameters.Append .CreateParameter("@cDate", adDBTimeStamp,
> adParamInput, 8, request.Form("XXcreationDate"))
> .Parameters.Append .CreateParameter("@efDate", adDBTimeStamp,
> adParamInput, 8, request.Form("NTEffectiveDate"))
> .Parameters.Append .CreateParameter("@exDate", adDBTimeStamp,
> adParamInput, 8, request.Form("NTExpirationDate"))
> .Parameters.Append .CreateParameter("@webC", adInteger,
> adParamInput, 1, intWebContact)
> .Parameters.Append .CreateParameter("@newID", adInteger,
> adParamOutput, 10)
> .Execute, , adExecuteNoRecords
> newID = .Parameters("@newID")
> End With
>
> My stored procedure is as follows:
> CREATE PROCEDURE [dbo].[spSaveContactDetails]
> @id int
> ,@ln nvarchar(50)
> ,@fn nvarchar(50)
> ,@emp int
> ,@op nvarchar(50) = NULL
> ,@cp nvarchar(50) = NULL
> ,@title nvarchar(50) = NULL
> ,@unit nvarchar(50) = NULL
> ,@email nvarchar(50) = NULL
> ,@empID nvarchar(50) = NULL
> ,@notes nvarchar(4000) = NULL
> ,@cDate dateTime = NULL
> ,@efDate dateTime = NULL
> ,@exDate dateTime = NULL
> ,@webC bit
> ,@newID int OUTPUT
> AS
> SET NOCOUNT ON
>
>
> Can anyone see what I might be doing wrong?
>
> Thank you,

Items from the Request.Querystring and Request.Form collections are returned
as string types, regardless of content. You need to coerse them to the
types to match the expectations of your parameter, e.g.,

CDate(request.Form("NTEffectiveDate"))
CLng(request.QueryString("ID"))

Of course this pre-supposes that these inputs contain valid string
representations of an integer and a date, as passed to this script. If that
is not the case, an exception will occur (and therefore you code will need
to be prepared for this eventuality.)


-Mark


> Jerry
>

Re: Problems with Stored Procedure and Date

On Aug 24, 10:20 am, "Mark J. McGinty"
wrote:
> "Jerry" wrote in message
>
> news:1187966548.828306.10700@m37g2000prh.googlegroups.com...
>
>
>
> >I apologize if this is a double post. I tried earlier and it didn't
> > appear to post.
>
> > I'm passing data to a stored procedure in SQL Server 2000 and am
> > having trouble with the date parameters. If I pass a date it works. If
> > I don't pass any data I get the following error:
> > ADODB.Command error '800a0d5d'
> > Application uses a value of the wrong type for the current operation.
>
> > The lines of error are:
> > .Parameters.Append .CreateParameter("@cDate", adDBTimeStamp,
> > adParamInput, 8, request.Form("XXcreationDate"))
> > .Parameters.Append .CreateParameter("@efDate", adDBTimeStamp,
> > adParamInput, 8, request.Form("NTEffectiveDate"))
> > .Parameters.Append .CreateParameter("@exDate", adDBTimeStamp,
> > adParamInput, 8, request.Form("NTExpirationDate"))
>
> > My ASP code is as follows:
> > With objCmd
> > .ActiveConnection = objConn
> > .CommandText = "spSaveContactDetails"
> > .CommandType = adCmdStoredProc
> > .Parameters.Append .CreateParameter("@id", adInteger,
> > adParamInput, 10, request.QueryString("ID"))
> > .Parameters.Append .CreateParameter("@ln", adVarChar,
> > adParamInput, 50, request.Form("RTlname"))
> > .Parameters.Append .CreateParameter("@fn", adVarChar,
> > adParamInput, 50, request.Form("RTfname"))
> > .Parameters.Append .CreateParameter("@emp", adInteger,
> > adParamInput, 10, request.Form("Rtemployer"))
> > .Parameters.Append .CreateParameter("@op", adVarChar,
> > adParamInput, 50, request.Form("NTofficePhone"))
> > .Parameters.Append .CreateParameter("@cp", adVarChar,
> > adParamInput, 50, request.Form("NTcellPhone"))
> > .Parameters.Append .CreateParameter("@title", adVarChar,
> > adParamInput, 50, request.Form("NTtitle"))
> > .Parameters.Append .CreateParameter("@unit", adVarChar,
> > adParamInput, 50, request.Form("NTunit"))
> > .Parameters.Append .CreateParameter("@email", adVarChar,
> > adParamInput, 50, request.Form("NTemailAddress"))
> > .Parameters.Append .CreateParameter("@empID", adVarChar,
> > adParamInput, 50, request.Form("NTemployeeid"))
> > .Parameters.Append .CreateParameter("@notes", adVarChar,
> > adParamInput, 4000, request.Form("NTnotes"))
> > .Parameters.Append .CreateParameter("@cDate", adDBTimeStamp,
> > adParamInput, 8, request.Form("XXcreationDate"))
> > .Parameters.Append .CreateParameter("@efDate", adDBTimeStamp,
> > adParamInput, 8, request.Form("NTEffectiveDate"))
> > .Parameters.Append .CreateParameter("@exDate", adDBTimeStamp,
> > adParamInput, 8, request.Form("NTExpirationDate"))
> > .Parameters.Append .CreateParameter("@webC", adInteger,
> > adParamInput, 1, intWebContact)
> > .Parameters.Append .CreateParameter("@newID", adInteger,
> > adParamOutput, 10)
> > .Execute, , adExecuteNoRecords
> > newID = .Parameters("@newID")
> > End With
>
> > My stored procedure is as follows:
> > CREATE PROCEDURE [dbo].[spSaveContactDetails]
> > @id int
> > ,@ln nvarchar(50)
> > ,@fn nvarchar(50)
> > ,@emp int
> > ,@op nvarchar(50) = NULL
> > ,@cp nvarchar(50) = NULL
> > ,@title nvarchar(50) = NULL
> > ,@unit nvarchar(50) = NULL
> > ,@email nvarchar(50) = NULL
> > ,@empID nvarchar(50) = NULL
> > ,@notes nvarchar(4000) = NULL
> > ,@cDate dateTime = NULL
> > ,@efDate dateTime = NULL
> > ,@exDate dateTime = NULL
> > ,@webC bit
> > ,@newID int OUTPUT
> > AS
> > SET NOCOUNT ON
> >
>
> > Can anyone see what I might be doing wrong?
>
> > Thank you,
>
> Items from the Request.Querystring and Request.Form collections are returned
> as string types, regardless of content. You need to coerse them to the
> types to match the expectations of your parameter, e.g.,
>
> CDate(request.Form("NTEffectiveDate"))
> CLng(request.QueryString("ID"))
>
> Of course this pre-supposes that these inputs contain valid string
> representations of an integer and a date, as passed to this script. If that
> is not the case, an exception will occur (and therefore you code will need
> to be prepared for this eventuality.)

If I pass a date to them I don't get the error. I only get the error
if I don't pass a date. I should have mentioned this before but I'd
like to be able to store a NULL value in the table so I'd need the
ability to pass nothing to the date fields.

--
Jerry

Re: Problems with Stored Procedure and Date

Can we see the stored procedure definition?


ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/

Re: Problems with Stored Procedure and Date

If you want to pass the null value, then maybe you should explicitely use
the value Null instead of the empty string "" that might be returned by
request.Form("NTemailAddress"). It's also possible that your error is
located somewhere else; so you should take a look at all the other
parameters to make sure that they are OK; for example, your use of the value
10 for adInteger or the use of adVarChar for a nvarchar parameter both look
suspicious to me.

Here's a little procedure to verify the properties of each parameter:
Sub liste_des_parametres(nom_procedure As String)


Dim cmd As ADODB.Command
Set cmd = New ADODB.Command


Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = nom_procedure


cmd.Parameters.Refresh


Dim p As ADODB.Parameter


For Each p In cmd.Parameters
Debug.Print "name = " & p.name
Debug.Print "Direction = " & p.Direction
Debug.Print "Type = " & p.Type
Debug.Print "Size = " & p.Size
Debug.Print "Precision = " & p.Precision
Debug.Print "NumericScale = " & p.NumericScale
Debug.Print
Next


Set cmd = Nothing
End Sub


For using this procedure, you must *not* give the prefix dbo. to the name of
the SP. See http://www.asp101.com/articles/john/adovbs/adojavas.inc for the
numerical values.

Finally, don't forget that the order of the parameters is of uttermost
importance (because ADO won't use the names of the parameter to make this
check) and that you must have an adParamReturnValue parameter as the first
one if your SP have any call to the Return statement.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Jerry" wrote in message
news:1187969782.402405.65040@q3g2000prf.googlegroups.com...
> On Aug 24, 10:20 am, "Mark J. McGinty"
> wrote:
>> "Jerry" wrote in message
>>
>> news:1187966548.828306.10700@m37g2000prh.googlegroups.com...
>>
>>
>>
>> >I apologize if this is a double post. I tried earlier and it didn't
>> > appear to post.
>>
>> > I'm passing data to a stored procedure in SQL Server 2000 and am
>> > having trouble with the date parameters. If I pass a date it works. If
>> > I don't pass any data I get the following error:
>> > ADODB.Command error '800a0d5d'
>> > Application uses a value of the wrong type for the current operation.
>>
>> > The lines of error are:
>> > .Parameters.Append .CreateParameter("@cDate", adDBTimeStamp,
>> > adParamInput, 8, request.Form("XXcreationDate"))
>> > .Parameters.Append .CreateParameter("@efDate", adDBTimeStamp,
>> > adParamInput, 8, request.Form("NTEffectiveDate"))
>> > .Parameters.Append .CreateParameter("@exDate", adDBTimeStamp,
>> > adParamInput, 8, request.Form("NTExpirationDate"))
>>
>> > My ASP code is as follows:
>> > With objCmd
>> > .ActiveConnection = objConn
>> > .CommandText = "spSaveContactDetails"
>> > .CommandType = adCmdStoredProc
>> > .Parameters.Append .CreateParameter("@id", adInteger,
>> > adParamInput, 10, request.QueryString("ID"))
>> > .Parameters.Append .CreateParameter("@ln", adVarChar,
>> > adParamInput, 50, request.Form("RTlname"))
>> > .Parameters.Append .CreateParameter("@fn", adVarChar,
>> > adParamInput, 50, request.Form("RTfname"))
>> > .Parameters.Append .CreateParameter("@emp", adInteger,
>> > adParamInput, 10, request.Form("Rtemployer"))
>> > .Parameters.Append .CreateParameter("@op", adVarChar,
>> > adParamInput, 50, request.Form("NTofficePhone"))
>> > .Parameters.Append .CreateParameter("@cp", adVarChar,
>> > adParamInput, 50, request.Form("NTcellPhone"))
>> > .Parameters.Append .CreateParameter("@title", adVarChar,
>> > adParamInput, 50, request.Form("NTtitle"))
>> > .Parameters.Append .CreateParameter("@unit", adVarChar,
>> > adParamInput, 50, request.Form("NTunit"))
>> > .Parameters.Append .CreateParameter("@email", adVarChar,
>> > adParamInput, 50, request.Form("NTemailAddress"))
>> > .Parameters.Append .CreateParameter("@empID", adVarChar,
>> > adParamInput, 50, request.Form("NTemployeeid"))
>> > .Parameters.Append .CreateParameter("@notes", adVarChar,
>> > adParamInput, 4000, request.Form("NTnotes"))
>> > .Parameters.Append .CreateParameter("@cDate", adDBTimeStamp,
>> > adParamInput, 8, request.Form("XXcreationDate"))
>> > .Parameters.Append .CreateParameter("@efDate", adDBTimeStamp,
>> > adParamInput, 8, request.Form("NTEffectiveDate"))
>> > .Parameters.Append .CreateParameter("@exDate", adDBTimeStamp,
>> > adParamInput, 8, request.Form("NTExpirationDate"))
>> > .Parameters.Append .CreateParameter("@webC", adInteger,
>> > adParamInput, 1, intWebContact)
>> > .Parameters.Append .CreateParameter("@newID", adInteger,
>> > adParamOutput, 10)
>> > .Execute, , adExecuteNoRecords
>> > newID = .Parameters("@newID")
>> > End With
>>
>> > My stored procedure is as follows:
>> > CREATE PROCEDURE [dbo].[spSaveContactDetails]
>> > @id int
>> > ,@ln nvarchar(50)
>> > ,@fn nvarchar(50)
>> > ,@emp int
>> > ,@op nvarchar(50) = NULL
>> > ,@cp nvarchar(50) = NULL
>> > ,@title nvarchar(50) = NULL
>> > ,@unit nvarchar(50) = NULL
>> > ,@email nvarchar(50) = NULL
>> > ,@empID nvarchar(50) = NULL
>> > ,@notes nvarchar(4000) = NULL
>> > ,@cDate dateTime = NULL
>> > ,@efDate dateTime = NULL
>> > ,@exDate dateTime = NULL
>> > ,@webC bit
>> > ,@newID int OUTPUT
>> > AS
>> > SET NOCOUNT ON
>> >
>>
>> > Can anyone see what I might be doing wrong?
>>
>> > Thank you,
>>
>> Items from the Request.Querystring and Request.Form collections are
>> returned
>> as string types, regardless of content. You need to coerse them to the
>> types to match the expectations of your parameter, e.g.,
>>
>> CDate(request.Form("NTEffectiveDate"))
>> CLng(request.QueryString("ID"))
>>
>> Of course this pre-supposes that these inputs contain valid string
>> representations of an integer and a date, as passed to this script. If
>> that
>> is not the case, an exception will occur (and therefore you code will
>> need
>> to be prepared for this eventuality.)
>
> If I pass a date to them I don't get the error. I only get the error
> if I don't pass a date. I should have mentioned this before but I'd
> like to be able to store a NULL value in the table so I'd need the
> ability to pass nothing to the date fields.
>
> --
> Jerry
>

Re: Problems with Stored Procedure and Date

ML wrote:
> adDBTimeStamp is not datetime.

Actually it is, at least for Jet and SQL Server.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Problems with Stored Procedure and Date

Jerry wrote:
> I apologize if this is a double post. I tried earlier and it didn't
> appear to post.
>

You will probably benefit from my parameters collection code generator
available here:
http://common.mvps.org/barrowsb/ClassicASP_sp_code_generator .zip
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Problems with Stored Procedure and Date

You're right. Sorry about that. I've misread a KB article dealing with
timestamp and datetime values being passed from an application to SQL Server.


ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/