Error with Stored_Proc

[stuart]

Hi,

I am having a problem with a stored procedure. I am getting the
following error

ADODB.Command error '800a0d5d'

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

/myproofs/includes/functions.asp, line 183

I have higlighted line 183 with #######

FUNCTION addPublication( strName, dtCreationDate, dtDeadlineDate, blnOpen)

Dim objComm

set objComm = server.CreateOb ject("ADODB.Com mand")

objComm.activeC onnection = objConn
objComm.Command Text = "Insert_Add_Pub lication"
objComm.Command Type = adCmdStoredProc

objComm.Paramet ers.Append
objComm.CreateP arameter("@tblP ublicationName" ,
adVarChar,adPar amInput,50,strN ame)
objComm.Paramet ers.Append
######objComm.C reateParameter( "@tblPublicatio nCreationDate",
adDBDate,adPara mInput,4,dtCrea tionDate)
objComm.Paramet ers.Append
objComm.CreateP arameter("@tblP ublicationOpen" , adTinyInt,adPar amInput,
,blnOpen)
objComm.Paramet ers.Append
objComm.CreateP arameter("@tblP ublicationDeadL ine",
adDate,adParamI nput,4,dtDeadli neDate)

objComm.Execute

set objComm = nothing

END FUNCTION

Many thanks
for any help recieved.
Stuart

[Bob Barrows [MVP]]

stuart wrote:

Hi,

I am having a problem with a stored procedure. I am getting the
following error

ADODB.Command error '800a0d5d'

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

/myproofs/includes/functions.asp, line 183

I have higlighted line 183 with #######

FUNCTION addPublication( strName,
dtCreationDate, dtDeadlineDate, blnOpen)
Dim objComm

set objComm = server.CreateOb ject("ADODB.Com mand")

objComm.activeC onnection = objConn
Bad practice here. Always use Set when assigning an object to a variable or
property. Without the Set keyword, the default property of objConn (its
connection string) is assigned to ActiveConnectio n. This causes a new
connection to be implicitly created and opened when you execute the Command,
in effect, disabling session pooling.
objComm.Command Text = "Insert_Add_Pub lication"
objComm.Command Type = adCmdStoredProc

objComm.Paramet ers.Append
objComm.CreateP arameter("@tblP ublicationName" ,
adVarChar,adPar amInput,50,strN ame)
objComm.Paramet ers.Append
######objComm.C reateParameter( "@tblPublicatio nCreationDate",
adDBDate,adPara mInput,4,dtCrea tionDate)

1. The correct datatype constant to use is adDBTimeStamp
2. You need to verify that dtCreationDate actually contains a date. Use
CDate with error-handling to do this verification. If dtCreationDate
contains an empty string, you need to assign Null to the variable.
3. You don't have any output parameters that I can see, and you don't seem
to be interested in reading the return parameter, so you do not need an
explicit Command object., using the "stored-procedure-as-connection-method"
technique to execute your procedure instead. You can rewrite your function
as follows:

FUNCTION addPublication( strName, dtCreationDate, dtDeadlineDate, blnOpen)
' validate your inputs per step 2 above, then:
on error resume next
objConn.Insert_ Add_Publication strName, dtCreationDate, blnOpen, _
dtDeadlineDate
if err <> 0 then
'handle the error
end if
end function

I only use an explicit Command object when
a. I need to read the value of the return parameter
b. I need to use output parameters
c. both of the above

Bob Barrows
--
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"

[stuart]

Thanks Bob.

Bob Barrows [MVP] wrote:

stuart wrote:

Hi,

I am having a problem with a stored procedure. I am getting the
following error

ADODB.Comma nd error '800a0d5d'

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

/myproofs/includes/functions.asp, line 183

I have higlighted line 183 with #######

FUNCTION addPublication( strName,
dtCreationDat e,dtDeadlineDat e,blnOpen)
Dim objComm

set objComm = server.CreateOb ject("ADODB.Com mand")

objComm.activ eConnection = objConn

Bad practice here. Always use Set when assigning an object to a variable or
property. Without the Set keyword, the default property of objConn (its
connection string) is assigned to ActiveConnectio n. This causes a new
connection to be implicitly created and opened when you execute the Command,
in effect, disabling session pooling.

objComm.Comma ndText = "Insert_Add_Pub lication"
objComm.Comma ndType = adCmdStoredProc

objComm.Param eters.Append
objComm.Creat eParameter("@tb lPublicationNam e",
adVarChar,adP aramInput,50,st rName)
objComm.Param eters.Append
######objComm .CreateParamete r("@tblPublicat ionCreationDate ",
adDBDate,adPa ramInput,4,dtCr eationDate)

1. The correct datatype constant to use is adDBTimeStamp
2. You need to verify that dtCreationDate actually contains a date. Use
CDate with error-handling to do this verification. If dtCreationDate
contains an empty string, you need to assign Null to the variable.
3. You don't have any output parameters that I can see, and you don't seem
to be interested in reading the return parameter, so you do not need an
explicit Command object., using the "stored-procedure-as-connection-method"
technique to execute your procedure instead. You can rewrite your function
as follows:

FUNCTION addPublication( strName, dtCreationDate, dtDeadlineDate, blnOpen)
' validate your inputs per step 2 above, then:
on error resume next
objConn.Insert_ Add_Publication strName, dtCreationDate, blnOpen, _
dtDeadlineDate
if err <> 0 then
'handle the error
end if
end function

I only use an explicit Command object when
a. I need to read the value of the return parameter
b. I need to use output parameters
c. both of the above

Bob Barrows

[Dave Anderson]

Bob Barrows [MVP] wrote:

objComm.activeC onnection = objConn

Bad practice here. Always use Set when assigning an object to a
variable or property. Without the Set keyword, the default property
of objConn (its connection string) is assigned to ActiveConnectio n.

And what does that imply WRT JScript (where there is no Set keyword) and
connection pooling?

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

[Bob Barrows [MVP]]

Dave Anderson wrote:

Bob Barrows [MVP] wrote:

objComm.activeC onnection = objConn

Bad practice here. Always use Set when assigning an object to a
variable or property. Without the Set keyword, the default property
of objConn (its connection string) is assigned to ActiveConnectio n.

And what does that imply WRT JScript (where there is no Set keyword)
and connection pooling?

If you check out the "Implicit Connections" thread started by Mark McGinty,
you will see me eating crow about this. It turns out that the Set keyword is
actually not needed in this situation for some reason. Somehow, the
ADODB.Command class is handling it the way it should be handled.

I do not believe this issue would have any bearing wrt jscript anyways,
since, as you say, there is no equivalent of a Set keyword in that language.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Dave Anderson]

Bob Barrows [MVP] wrote:

...Without the Set keyword, the default property of objConn
(its connection string) is assigned to ActiveConnectio n...

I do not believe this issue would have any bearing wrt jscript
anyways, since, as you say, there is no equivalent of a Set keyword
in that language.

And perhaps just as importantly, no default properties.

This group has been swimming in interesting performance-related threads
lately.
--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.