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]

Stefan Berglund wrote:

> 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

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

[Stefan Berglund]

On Mon, 20 Oct 2003 16:44:33 -0400, "Bob Barrows"
<reb01501@NOyahoo.SPAMcom> wrote:
in <OOlZ7r0lDHA.2732@TK2MSFTNGP11.phx.gbl>

>Stefan Berglund wrote:

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

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.