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.CreateObject("ADODB.Command")

objComm.activeConnection = objConn
objComm.CommandText = "Insert_Add_Publication"
objComm.CommandType = adCmdStoredProc

objComm.Parameters.Append
objComm.CreateParameter("@tblPublicationName",
adVarChar,adParamInput,50,strName)
objComm.Parameters.Append
######objComm.CreateParameter("@tblPublicationCreationDate",
adDBDate,adParamInput,4,dtCreationDate)
objComm.Parameters.Append
objComm.CreateParameter("@tblPublicationOpen", adTinyInt,adParamInput,
,blnOpen)
objComm.Parameters.Append
objComm.CreateParameter("@tblPublicationDeadLine",
adDate,adParamInput,4,dtDeadlineDate)

objComm.Execute

set objComm = nothing

END FUNCTION

Many thanks
for any help recieved.


Stuart

 

[Bob Barrows [MVP]]

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.CreateObject("ADODB.Command")

objComm.activeConnection = 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 ActiveConnection. This causes a new
connection to be implicitly created and opened when you execute the Command,
in effect, disabling session pooling.

objComm.CommandText = "Insert_Add_Publication"
objComm.CommandType = adCmdStoredProc

objComm.Parameters.Append
objComm.CreateParameter("@tblPublicationName",
adVarChar,adParamInput,50,strName)
objComm.Parameters.Append
######objComm.CreateParameter("@tblPublicationCreationDate",
adDBDate,adParamInput,4,dtCreationDate)

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

 

[stuart]

Thanks Bob.

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 ActiveConnection. This causes a new
connection to be implicitly created and opened when you execute the Command,
in effect, disabling session pooling.




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]

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 ActiveConnection.

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]]

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

 

[Dave Anderson]

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.