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

The information in this article applies to:

Microsoft Visual InterDev 6.0

This article was previously published under Q190762

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

-or-

ADODB.Recordset (0x800a0e78) Operation is not allowed when 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

Create a DataEnvironment (DE) command and bind it to the stored procedure.

Use the following code to access the return value of the Data Command:

Set DE = Server.CreateObject("DERuntime.DERuntime")
DE.Init(Application("DE"))

DE.Command1 'parm1, parm2, etc. if parameters required

Set objCMD = DE.Commands("Command1")
RetVal = objCMD.Parameters(0)

STATUS

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

MORE INFORMATION

Steps to Reproduce Behavior

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

      Create Procedure JustReturns
      As
      return 23

Bind a recordset DTC to the stored procedure.

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

Modification Type:	Minor	Last Reviewed:	3/7/2005
Keywords:	kbCtrl kbprb KB190762