Set Command object ActiveConnection property to Nothing?

[Stefan Berglund]

I was looking for a general consensus as to whether the ActiveConnection property of the command
object should be set to Nothing or whether it's sufficient to set the command object itself to
nothing.

I also seem to be forced to use the Parameters.Append method, since I was unable to make the
statement Set rs = .Execute(,Array(strShowID,strSort),adCmdStoredProc) work without error.
I'm guessing that while they're both Variants, one is a numeric and the other a string? The above
statement yields :

Parameter object is improperly defined. Inconsistent or incomplete information was provided.

<%With cmd
.CommandText = "show_ShowEntries"
.Parameters.Append .CreateParameter("@Sort", adVarChar, adParamInput, 7, strSort)
Set rs = .Execute(,,adCmdStoredProc)
Set .ActiveConnection = Nothing <---- is this necessary/advisable ?
End With
Set cmd = Nothing <---- or is this sufficient ?
With rs
If (NOT .EOF) Then
Dim ar
ar = .GetRows
Set rs = .NextRecordset
Dim intTotalEntries
intTotalEntries = rs(0)
End If
End With
rs.Close: Set rs = Nothing: cn.Close: Set cn = Nothing

 

[Bob Barrows]

I was looking for a general consensus as to whether the
ActiveConnection property of the command object should be set to
Nothing or whether it's sufficient to set the command object itself
to nothing.

You don't have to set it to nothing

I also seem to be forced to use the Parameters.Append method, since I
was unable to make the statement Set rs =
.Execute(,Array(strShowID,strSort),adCmdStoredProc) work without
error.
I'm guessing that while they're both Variants, one is a numeric and
the other a string? The above statement yields :

Parameter object is improperly defined. Inconsistent or incomplete
information was provided.

Since your stored procedure has no output parameters, and you don't seem to
be interested in reading the value of the Return parameter, I suggest you
don't use an explicit Command object at all. Try this (assuming your
connection object variable is called cn):

set rs=server.createobject("adodb.recordset")
cn.show_ShowEntries strSort, rs

If you're executing a procedure that does not return records, just leave off
the rs argument:
cn.NoRecords parmval1, ..., parmvalN

HTH,
Bob Barrows

 

[Stefan Berglund]

On Mon, 20 Oct 2003 16:44:33 -0400, "Bob Barrows"

You don't have to set it to nothing


Since your stored procedure has no output parameters, and you don't seem to
be interested in reading the value of the Return parameter, I suggest you
don't use an explicit Command object at all. Try this (assuming your
connection object variable is called cn):

set rs=server.createobject("adodb.recordset")
cn.show_ShowEntries strSort, rs

If you're executing a procedure that does not return records, just leave off
the rs argument:
cn.NoRecords parmval1, ..., parmvalN

HTH,
Bob Barrows

Most excellent Bob, thanks. I had searched MSDN unsuccessfully
for an example that would allow execution off the connection
object with parameters and was more or less resigned to having to
use the command object. I also searched the FAQ but I guess I
either wasn't persistent enough or didn't use the right search
criteria, since I just saw the EXEC @param=value option which at
least comes close. I guess I just should have tried it.

Now I can chop out more code. <g> Thanks again.