?
Solved

ADODB.Recordset error '800a0e78'

Posted on 2003-03-17
3
Medium Priority
?
1,147 Views
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)
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
Comment
Question by:ddorado
[X]
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
3 Comments
 
LVL 2

Accepted Solution

by:
navneet77 earned 100 total points
ID: 8155984
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
 
LVL 5

Assisted Solution

by:yronnen
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

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
 

Expert Comment

by:CleanupPing
ID: 9447060
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

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