ADODB.Recordset error '800a0e78'

Posted on 2003-03-17
Medium Priority
Last Modified: 2012-06-22
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)
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

My asp code:

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

set resultados = nothing
set cmd = nothing
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

Question by:ddorado
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

navneet77 earned 100 total points
ID: 8155984
Can you tell us which is line number 97. And what if you try

set resultados = nothing
set cmd = nothing
Set conexion = nothing

Assisted Solution

yronnen earned 100 total points
ID: 8157663
This one is courtesy of MSDN, hope it will help:
PRB: Cannot Access a Stored Procedure's Return Value from DTC


The information in this article applies to:

Microsoft Visual InterDev, version 6.0


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

ADODB.Recordset (0x800a0e78) Operation is not allowed when the object is closed.
/<Web name>/_ScriptLibrary/Recordset.ASP, line 762

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


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.Command1 'parm1, parm2, etc. if parameters required

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

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


Steps to Reproduce Behavior
Create a stored procedure that does not return a recordset, such as this:

      Create Procedure JustReturns
      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

Expert Comment

ID: 9447060
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 
Post your closing recommendations!  No comment means you don't care.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A short article about problems I had with the new location API and permissions in Marshmallow
Make the most of your online learning experience.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question