PRB: Cannot Access a Stored Procedure's Return Value from DTC

• Microsoft Visual InterDev, version 6.0

SYMPTOMS

A Recordset design-time control (DTC) is bound to a stored procedure, which returns a value, but no recordset. When the return value is accessed, this error occurs:

ADODB.Recordset error '800a0e78'
The operation requested by the application is not allowed if the object is closed.
/<Web name>/_ScriptLibrary/Recordset.ASP, line 762

CAUSE

The stored procedure must return a recordset in order for the Recordset DTC to access the return value.

RESOLUTION

1. Create a DataEnvironment (DE) command.



2. Use the following code to access the return value of the data command:

   
         set DE = server.createobject("DERuntime.DERuntime")
         DE.init Application("DE")
         Set cmd = DE.Commands("Command1")
         Set ps = cmd.Parameters
         ' Uncomment the following lines to provide parameters
         ' for the stored procedure.  Use appropriate values.
         '  ps(1) = param1
         '  ps(2) = param2
         cmd.execute
         retval = ps(0) 

STATUS

This behavior is by design. The Recordset DTC was designed to work with recordsets.

MORE INFORMATION

Steps to Reproduce Behavior

1. Create a stored procedure that does not return a recordset, such as this:

   
         Create Procedure JustReturns
         As
         return 23 




2. Bind a recordset DTC to the stored procedure.



3. Try to access the return value of the stored procedure:

   
         Response.Write "Return Value = ["
         Response.Write Recordset1.getParameter( 0 )
         Response.Write "]" 

   This produces an error:

   
         ADODB.Recordset error '800a0e78'
         The operation requested by the application is not allowed
         if the object is closed.
         /<Web name>/_ScriptLibrary/Recordset.ASP, line 762 

Additional query words:

Keywords : kbASP kbCtrl kbVisID kbVisID600 kbGrpASP
Version : WINDOWS:6.0
Platform : WINDOWS
Issue type : kbprb