Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1160
  • Last Modified:

ADODB.Recordset error '800a0e78'

Sorry, my english is no good, and I'm desperated.

I work with Stored procedures (SQL Server 2000) and IIS 5.0 with a asp aplication.


My store procedure:

CREATE Procedure p_lee_archivos(
@idSubCategoria int = null,
@id int OUTPUT,
@Categoria varchar (50) OUTPUT,
@SubCategoria varchar(50) OUTPUT,
@archivo varchar (50) OUTPUT,
@descripcion varchar (256) OUTPUT,
@anchoalto varchar (20) OUTPUT,
@tamano bigint OUTPUT,
@fechaAlta varchar (50) OUTPUT,
@Descargas bigint OUTPUT,
@tipo char (4) OUTPUT,
@enviadoPor varchar (100) OUTPUT,
@creditosNecesarios int OUTPUT)
AS
SET NOCOUNT ON
BEGIN
select id,archivo,descripcion,anchoalto,
CAST(tamano/1024 as int) as  tamano,
FLOOR((tamano/1024)/250) as creditosNecesarios,
fechaalta, descargas,tipo,enviadopor, categoria, subcategoria
from datos where
idsubcategoria = @idsubcategoria
order by descripcion
END


My asp code:

<%'p_lee_archivos
Set conexion = Server.CreateObject("ADODB.Connection")
conexion.open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=xxxx;PWD=xxxxxx;Initial Catalog=xxxx;Data Source=XXXXX"

set cmd = Server.CreateObject("ADODB.Command")
set cmd.ActiveConnection = conexion
cmd.commandtype = 4
cmd.commandtext = "p_lee_archivos"

set idSubCategoria =   cmd.createparameter("idSubCategoria",adInteger,adParamInput)
cmd.Parameters.Append idSubCategoria
idSubCategoria.Value = Request("idSubCategoria")

set id =   cmd.createparameter("id",adInteger,adParamOutput)
cmd.Parameters.Append id

set Archivo=  cmd.createparameter("Archivo",adVarChar,adParamOutput,50)
cmd.Parameters.Append Archivo
                         
set Categoria=  cmd.createparameter("Categoria",adVarChar,adParamOutput,50)
cmd.Parameters.Append Categoria

set subCategoria=  cmd.createparameter("subCategoria",adVarChar,adParamOutput,50)
cmd.Parameters.Append subCategoria

set Descripcion=  cmd.createparameter("Descripcion",adVarChar,adParamOutput,50)
cmd.Parameters.Append Descripcion

set anchoAlto =   cmd.createparameter("anchoAlto",adVarChar,adParamOutput,20)
cmd.Parameters.Append anchoAlto              
                         
set Tamano =   cmd.createparameter("Tamano",adBigInt,adParamOutput)
cmd.Parameters.Append Tamano
                         
set fechaAlta =   cmd.createparameter("fechaAlta",adVarChar,adParamOutput,50)
cmd.Parameters.Append fechaAlta

set Descargas =   cmd.createparameter("Descargas",adBigInt,adParamOutput)
cmd.Parameters.Append Descargas

set Tipo =   cmd.createparameter("Tipo",adVarChar,adParamOutput,50)
cmd.Parameters.Append Tipo

set enviadoPor =   cmd.createparameter("enviadoPor",adVarChar,adParamOutput,50)
cmd.Parameters.Append enviadoPor
                         
set creditosNecesarios =   cmd.createparameter("creditosNecesarios",adInteger,adParamOutput)
cmd.Parameters.Append creditosNecesarios

set resultados = Server.CreateObject("ADODB.Recordset")
resultados.Open cmd                    
                         
                         
While not resultados.EOF%>

Print any param

<%resultados.MoveNext()
Wend
set resultados = nothing
set cmd = nothing
conexion.close
Set conexion = nothing
%>



The error:

ADODB.Recordset error '800a0e78'
La operación no está permitida si el objeto está cerrado.

/archivos_subcategoria.asp, line 97
 


The stored procedure works fine.

Other stored procedures and other asp pages, with similar code works OK.

I don't understand anything, because that's  my first experience in stored procedures with asp.

Best regards
Diego

0
ddorado
Asked:
ddorado
2 Solutions
 
navneet77Commented:
Can you tell us which is line number 97. And what if you try

<%resultados.MoveNext()
Wend
conexion.close
set resultados = nothing
set cmd = nothing
Set conexion = nothing
%>
0
 
yronnenCommented:
This one is courtesy of MSDN, hope it will help:
PRB: Cannot Access a Stored Procedure's Return Value from DTC

Q190762


--------------------------------------------------------------------------------
The information in this article applies to:

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
-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
0
 
CleanupPingCommented:
ddorado:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now