I have a very long stored procedure that stores the data it gathers from 
various tables in a table variable. Everything works fine selecting the 
data from this variable until I update some data then try to select from 
the variable I get "ADODB.Recordset error '800a0e78'. Operation is not 
allowed when the object is closed. " 
If I run the query with the same data I use in my ASP in Query Analyzer I 
can see the correct results returned in the results pane.
I've tried to select the data into #temp and select from the temp table 
but still nothing :-(

Has anyone come across this before?

Do I have to do something extra after an update to be able to select a 
valid RS from it.

I should also point out that the Stored Procedure should always return 
data to a RS.
Also everything i.e. inserting into the variable, updating data in the 
variable(optional) and selecting the data out of the variable into a RS is 
done in a single pass all, data in the SP is lost when the SP finishes

Lloyd,

You may want to post the code that is causing the error so we can help
you troubleshoot the problem.  Based on the error you are receiving, I
would look through your code to ensure that you are not closing the
Recordset and trying to access it later in your code.

Ex.
	objRS.Close

Regards,
Eric

-----Original Message-----
From: Lloyd Burley [mailto:trip-l@n...] 
Sent: Wednesday, June 26, 2002 12:33 AM
To: ASP Database Setup
Subject: [asp_database_setup] ADODB.Recordset error '800a0e78'

I have a very long stored procedure that stores the data it gathers from

various tables in a table variable. Everything works fine selecting the 
data from this variable until I update some data then try to select from

the variable I get "ADODB.Recordset error '800a0e78'. Operation is not 
allowed when the object is closed. " 
If I run the query with the same data I use in my ASP in Query Analyzer
I 
can see the correct results returned in the results pane.
I've tried to select the data into #temp and select from the temp table 
but still nothing :-(

Has anyone come across this before?

Do I have to do something extra after an update to be able to select a 
valid RS from it.
eric@d...
%%email.unsub%%

Hi Eric this is a scaled down version of the code the inner most cursor is 
where the update happens, if I don't update I can select from this SP fine

Create Procedure stp_GetNewBasket
@BasketID int
as
Set NoCount off
/*Declare @BasketID int
Set @BasketID = 824*/
If Exists(Select BasketItemID from BasketItems where BasketID = @BasketID)
Begin
	--Declare Variables
	Declare @SaleDetailsID int
	Declare @SaleName varchar(64)
	Declare	@Price decimal(8,2)
	Declare @Purchase int
	Declare @Receive int
	Declare @UsePrice bit
	Declare @UsePurchase bit
	Declare @UseReceive bit
	Declare @ProdSel int
	Declare @NumProds int
	Declare @LastVal int
	Declare @SalePrice as decimal(8,2)
	Declare @SaleItem as bit
	Declare @Tot as decimal(8,2)
	Declare @SalesID int
	Declare @TypeID int
	Declare @Quantity int
	Declare @BasketItemID int
	Declare @Remain int
	Declare @Returns int
	DEclare @Min int
	Declare @Max int
	Declare @UseNum int
	Declare @ItemPrice decimal(8,2)
	Declare @TotPrice decimal(9,2)
	Declare @TotSaving decimal(9,2)
	Declare @TotSale decimal(9,2)
	Declare @Dis decimal(8,2)
	Declare @TempTbl table(TabID INT IDENTITY(1,1) PRIMARY KEY,
						BasketItemID int null 
default(0),
						name varchar(64) null 
default(0), 
						Quantity tinyint null 
default(0), 
						price decimal(8,2) null 
default(0), 
						InSale int null default(0),
						SaleID int null default
(0), 
						SalePrice decimal(8,2) 
null default(0),
						Saving decimal(8,2) null 
default(0),
						MaxNum tinyint default(0))

	-- Insert products that are not in a sale into the table variable
	insert into @TempTbl(BasketItemID, name, Quantity, price) 
	select BasketItemID, name, Quantity, VBasketItems.price
	FROM VBasketItems Left Outer join VSale on VBasketItems.ProductID 
= VSale.ProductID 
	WHERE BasketID = @BasketID and Insale is Null

	-- Declare Cursor to store the SaleIDs for all the items that are 
on sale
	Declare IDs_Cursor Cursor for Select SaleDetailsID from 
SaleDetails where 
	SaleDetailsID in (Select InSale from BasketITems where BasketID = 
@BasketID) and 
	NumProds in (Select Count(ProductID) as Counts from BasketItems 
	where BasketID = @BasketID group by InSale)
	
	Open IDs_Cursor
	Fetch next from IDs_Cursor into @SalesID
	While @@Fetch_Status = 0
	Begin
		-- Store the last ID outside the while loop
		Set @LastVal = @SalesID

		-- Insert into the @table the next row from the basket
		insert into @Temptbl(BasketItemID, name, Quantity, price, 
InSale) 
		select BasketItemID, name, Quantity, VBasketItems.price, 
InSale
		FROM VBasketItems inner join VSale on 
VBasketItems.ProductID = VSale.ProductID 
		WHERE BasketID = @BasketID and Insale = @SalesID

		-- Declare Cursor to store the details of the sale
		Declare Sales_Cursor Cursor for Select Distinct 
SaleDetailsID, SaleName, Price, Purchase, Receive, NumProds, UsePrice, 
UsePurchase, UseReceive, Prodselect, MaxNum from VSale where SaleDetailsID 
= @SalesID
		Open Sales_Cursor
		Fetch next from Sales_Cursor into @SaleDetailsID, 
@SaleName, @Price, @Purchase, @Receive, @NumProds, @UsePrice, 
@UsePurchase, @UseReceive, @ProdSel, @Max
		While @@Fetch_Status = 0
		Begin
///*** This is the inner most loop ***///
			-- Else If we have a Standard Sale
			If @UsePrice = 1 and @UsePurchase = 0 and 
@UseReceive = 0 and @ProdSel = 1
			Begin
				if (Select min(Quantity) from @TempTbl 
where InSale = @LastVal) > @Max and @Max <> 0
				Begin
					print @Max
					insert into @Temptbl(SaleID, Name, 
SalePrice) 
					values(@SaleDetailsID, @SaleName, 
@Price)
					Select @Price = Sum(Price), @Min = 
min(Quantity) from @TempTbl where InSale = @LastVal	
					If @Min > @Max and @Max <> 0
						Set @UseNum = @Max
					Else
						Set @UseNum = @Min
///*** An Update here ***///
					update @TempTbl set Price = 
@Price*@Min, Saving = (@price*@UseNum)-SalePrice*@UseNum, SalePrice = 
@Price*@Min-((@price*@UseNum)-SalePrice*@UseNum), Quantity = @UseNum, 
MaxNum = @Max where SaleID = @LastVal and Quantity is Null
				End
				Else
				Begin
///*** and here ***///
					update @TempTbl Set SalePrice = 
@Price
				End
				-- Sale has been updated so break to 
outter loop
				Break
			End
		End
		Close Sales_Cursor
		Deallocate Sales_Cursor
		Fetch Next from IDs_Cursor into @SalesID
	End

			-- Catch any items that aren't the correct amount 
for the sale
			insert into @TempTbl(BasketItemID, name, Quantity, 
price, InSale) 
			select BasketItemID, name, Quantity, 
VBasketItems.price, InSale
			FROM VBasketItems Left Outer join VSale on 
VBasketItems.ProductID = VSale.ProductID 
			WHERE BasketID = 824 and BasketItemID not in 
(Select BasketItemID from @Temptbl)

	Close IDs_Cursor
	Deallocate IDs_Cursor
	Select * from @Temptbl

End

This very generic test script generates the ADODB.Recordset 
error '800a0e78'

	Set db = Server.CreateObject("ADODB.Connection")
	db.Open str
	Set rs = Server.CreateObject("ADODB.Recordset")
	sql = "Execute Stp_GetNewBasket 824"
	rs.open sql, db
	
	If not rs.EOF then
	
		do while not rs.EOF
			Response.Write rs("name")
			rs.MoveNext
		Loop
		
	End if

SQL Server is returning the message ""xx records affected" after you initial
insert in your sproc. This is being placed into the recordset.

What I suggest you do, is alter your sproc so that you have:

SET NOCOUNT ON

prior to doing the INSERT

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Lloyd Burley" <trip-l@n...>
Subject: [asp_database_setup] RE: ADODB.Recordset error '800a0e78'


: Hi Eric this is a scaled down version of the code the inner most cursor is
: where the update happens, if I don't update I can select from this SP fine
:
: Create Procedure stp_GetNewBasket
: @BasketID int
: as
: Set NoCount off
: /*Declare @BasketID int
: Set @BasketID = 824*/
: If Exists(Select BasketItemID from BasketItems where BasketID = @BasketID)
: Begin
: --Declare Variables
: Declare @SaleDetailsID int
: Declare @SaleName varchar(64)
: Declare @Price decimal(8,2)
: Declare @Purchase int
: Declare @Receive int
: Declare @UsePrice bit
: Declare @UsePurchase bit
: Declare @UseReceive bit
: Declare @ProdSel int
: Declare @NumProds int
: Declare @LastVal int
: Declare @SalePrice as decimal(8,2)
: Declare @SaleItem as bit
: Declare @Tot as decimal(8,2)
: Declare @SalesID int
: Declare @TypeID int
: Declare @Quantity int
: Declare @BasketItemID int
: Declare @Remain int
: Declare @Returns int
: DEclare @Min int
: Declare @Max int
: Declare @UseNum int
: Declare @ItemPrice decimal(8,2)
: Declare @TotPrice decimal(9,2)
: Declare @TotSaving decimal(9,2)
: Declare @TotSale decimal(9,2)
: Declare @Dis decimal(8,2)
: Declare @TempTbl table(TabID INT IDENTITY(1,1) PRIMARY KEY,
: BasketItemID int null
: default(0),
: name varchar(64) null
: default(0),
: Quantity tinyint null
: default(0),
: price decimal(8,2) null
: default(0),
: InSale int null default(0),
: SaleID int null default
: (0),
: SalePrice decimal(8,2)
: null default(0),
: Saving decimal(8,2) null
: default(0),
: MaxNum tinyint default(0))
:
: -- Insert products that are not in a sale into the table variable
: insert into @TempTbl(BasketItemID, name, Quantity, price)
: select BasketItemID, name, Quantity, VBasketItems.price
: FROM VBasketItems Left Outer join VSale on VBasketItems.ProductID
: = VSale.ProductID
: WHERE BasketID = @BasketID and Insale is Null
:
: -- Declare Cursor to store the SaleIDs for all the items that are
: on sale
: Declare IDs_Cursor Cursor for Select SaleDetailsID from
: SaleDetails where
: SaleDetailsID in (Select InSale from BasketITems where BasketID 
: @BasketID) and
: NumProds in (Select Count(ProductID) as Counts from BasketItems
: where BasketID = @BasketID group by InSale)
:
: Open IDs_Cursor
: Fetch next from IDs_Cursor into @SalesID
: While @@Fetch_Status = 0
: Begin
: -- Store the last ID outside the while loop
: Set @LastVal = @SalesID
:
: -- Insert into the @table the next row from the basket
: insert into @Temptbl(BasketItemID, name, Quantity, price,
: InSale)
: select BasketItemID, name, Quantity, VBasketItems.price,
: InSale
: FROM VBasketItems inner join VSale on
: VBasketItems.ProductID = VSale.ProductID
: WHERE BasketID = @BasketID and Insale = @SalesID
:
: -- Declare Cursor to store the details of the sale
: Declare Sales_Cursor Cursor for Select Distinct
: SaleDetailsID, SaleName, Price, Purchase, Receive, NumProds, UsePrice,
: UsePurchase, UseReceive, Prodselect, MaxNum from VSale where SaleDetailsID
: = @SalesID
: Open Sales_Cursor
: Fetch next from Sales_Cursor into @SaleDetailsID,
: @SaleName, @Price, @Purchase, @Receive, @NumProds, @UsePrice,
: @UsePurchase, @UseReceive, @ProdSel, @Max
: While @@Fetch_Status = 0
: Begin
: ///*** This is the inner most loop ***///
: -- Else If we have a Standard Sale
: If @UsePrice = 1 and @UsePurchase = 0 and
: @UseReceive = 0 and @ProdSel = 1
: Begin
: if (Select min(Quantity) from @TempTbl
: where InSale = @LastVal) > @Max and @Max <> 0
: Begin
: print @Max
: insert into @Temptbl(SaleID, Name,
: SalePrice)
: values(@SaleDetailsID, @SaleName,
: @Price)
: Select @Price = Sum(Price), @Min 
: min(Quantity) from @TempTbl where InSale = @LastVal
: If @Min > @Max and @Max <> 0
: Set @UseNum = @Max
: Else
: Set @UseNum = @Min
: ///*** An Update here ***///
: update @TempTbl set Price 
: @Price*@Min, Saving = (@price*@UseNum)-SalePrice*@UseNum, SalePrice 
: @Price*@Min-((@price*@UseNum)-SalePrice*@UseNum), Quantity = @UseNum,
: MaxNum = @Max where SaleID = @LastVal and Quantity is Null
: End
: Else
: Begin
: ///*** and here ***///
: update @TempTbl Set SalePrice 
: @Price
: End
: -- Sale has been updated so break to
: outter loop
: Break
: End
: End
: Close Sales_Cursor
: Deallocate Sales_Cursor
: Fetch Next from IDs_Cursor into @SalesID
: End
:
: -- Catch any items that aren't the correct amount
: for the sale
: insert into @TempTbl(BasketItemID, name, Quantity,
: price, InSale)
: select BasketItemID, name, Quantity,
: VBasketItems.price, InSale
: FROM VBasketItems Left Outer join VSale on
: VBasketItems.ProductID = VSale.ProductID
: WHERE BasketID = 824 and BasketItemID not in
: (Select BasketItemID from @Temptbl)
:
: Close IDs_Cursor
: Deallocate IDs_Cursor
: Select * from @Temptbl
:
: End

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Lloyd,

The sproc is first returning the message "xx rows affected". You can supress
this message by doing

SET NOCOUNT ON

inside your sproc. Alternatively, you can do the following (expensive)
operation in your ASP code:

' objRS contains "xx rows affected"
Set objRS = objCommand.Execute

' Now objRS contains the data from the SELECT
Set objRS = objRS.NextRecordSet

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Lloyd Burley" <trip-l@n...>
Subject: [asp_database_setup] Re: ADODB.Recordset error '800a0e78'


: I should also point out that the Stored Procedure should always return
: data to a RS.
: Also everything i.e. inserting into the variable, updating data in the
: variable(optional) and selecting the data out of the variable into a RS is
: done in a single pass all, data in the SP is lost when the SP finishes

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~