Re: Command Object Output Parameter Undefined?

gary.huntress@xxxxxxxxx wrote:
I'm trying to maintain some old ADO code and I'm having trouble
understanding how my command object is behaving. The command object
is calling a stored procedure. I set up a normal command and
connection object, then I create 2 input and one output parameter.
The output parameter is named "ReturnValue".

The thing I'm confused about is that if I call

Cmd.Execute()
Response.Write (Cmd.Parameters("ReturnValue"))

I get the proper ReturnValue, however if I call

rs1 = Cmd.Execute()

I assume you meant "set rs1 = Cmd.Execute()"

Response.Write (Cmd.Parameters("ReturnValue"))

then ReturnValue is undefined. Nothing else in the code changes.
Why would capturing the returned recordset (whether I use it or not)
affect the parameters in the Cmd object?

Return and output parameter values are not sent to the client until all
resultsets generated by the procedure are consumed. This means that you
have to at least navigate to the last record of rs1 before attempting to
read the output parameter. This can be accomplished by using a
client-side cursorlocation for your recordset (assuming the procedure
returns a single resultset).
My SOP is to use GetRows to stuff the data from the recordset into an
array, close the recordset, then retrieve the output and return
parameter values, allowing me to immediately close the connection before
starting to process the data.

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


.

Relevant Pages

  • Re: Populate read-only form from SQL stored proc
    ... Command Object instead of a Connection Object because you can have typed ... closed recordset containing an info about the line count of the Select ... @UserNo as TinyInt ...
    (microsoft.public.access.formscoding)
  • Re: Return a recordset from an MDB
    ... Is it necessary to specify a CursorLocation? ... Execute method of the Command object to return a Recordset, ... If you use a Connection object, ...
    (microsoft.public.scripting.vbscript)
  • Re: ADO memory leaks
    ... > I use a connection object only. ... No recordset, command object are using. ...
    (microsoft.public.sqlserver.programming)
  • Re: ASP/ADO: Return a value from a Stored Procedure
    ... I think you original way cannot get a return value from the store ... You have to use Command object and OutPut parameter, ... recordset. ... performance when we use command object. ...
    (microsoft.public.inetserver.asp.db)
  • Re: Command Oject to pass parameters and CursorLocation
    ... recieved any results in my recordset. ... The Cursor location for RecordSet returned is whatever the current Cursor ... Location of the underlying Connection for Command object is set to. ... DO NOT set Connection. ...
    (microsoft.public.data.ado)
Loading