ADODB.Recordset error '800a0e78' Operation is not allowed when the object is closed.

i'm calling this storedprocedure from ASP and i keep getting an error

Set rsGetCSOrderList = Server.CreateObject("ADODB.Recordset")
rsGetCSOrderList.CursorLocation = 3
rsGetCSOrderList.Open strCSOrderListSQL, DataSource, 1, 3, 1

-->the error occurs in this line.
If Not rsGetCSOrderList.EOF And Not rsGetCSOrderList.BOF Then

--> this is the stored procedure
CREATE PROCEDURE [dbo].usp_GetWarehouseOrderListing(
@strShipStatusWhere varchar(300),
 @strWarehouseWhere varchar(300),
 @bolCustomerService tinyint,
 @strSearchColumnName varchar(100),
 @strSearchValue varchar(300))
-- Declare Dynamic SQL Query Variables
DECLARE @strSQLCursorCreation varchar(2600)
DECLARE @strSQLQuery varchar(2500)
DECLARE @strSQLWhere varchar(1250)
-- Create Temporary Table To Hold Information
CREATE TABLE [#OrderListTemp] ([lngID] int identity(1,1), [strOrderID] varchar(18), [dteDateEntered] datetime, [strCompany] varchar(100), [strStatuses] varchar(1000), [strWarehouses] varchar(1000), [lngUnReadCount] int)
-- Begin Parsing Together Required SQL Query For Receipt Item Listing
SET @strSQLWhere = ' WHERE'
IF(LTrim(RTrim(@strShipStatusWhere))<>'') SET @strSQLWhere = @strSQLWhere + ' ([eri].[ship_status] IN (' + @strShipStatusWhere + ')) AND'
IF(LTrim(RTrim(@strWarehouseWhere))<>'') SET @strSQLWhere = @strSQLWhere + '([w].[id] IN (' + @strWarehouseWhere + ')) AND'
IF(@bolCustomerService = 1) SET @strSQLWhere = @strSQLWhere + '(([er].[status]&4) = 4) AND'
IF(LTrim(RTrim(@strSearchColumnName))<>'') SET @strSQLWhere = @strSQLWhere + '(' + @strSearchColumnName + ' LIKE ''%' + @strSearchValue + '%'') AND'
IF(LTrim(RTrim(@strSQLWhere)) = 'WHERE')
  SET @strSQLWhere = ''
  SET @strSQLWhere = Left(@strSQLWhere, Len(@strSQLWhere) - 4)
SET @strSQLQuery = 'SELECT LTrim(RTrim([er].[order_id])) AS [strOrderListOrderID], Min([er].[date_entered]) AS [dteOrderListDateEntered], LTrim(RTrim(Min(IsNull([es].[company], '''')))) AS [strOrderListCompany], LTrim(RTrim(Min(isnull([wss].[type], ''<i>Unknown</i>'')))) as [strOrderListStatus], LTrim(RTrim(Min(isnull([w].[name], ''<i>Unknown</i>'')))) as [strOrderListWarehouse], IsNull((SELECT Count([ercs].[id]) FROM [eframe_receipt_cust_serv] AS [ercs] WHERE [ercs].[orderid]=[er].[order_id] And [warehouseuserid]=0), 0) AS [lngOrderListUnReadCount] FROM [eframe_receipt] AS [er] INNER JOIN [eframe_receipt_item] AS [eri] ON [eri].[order_id]=[er].[order_id] LEFT OUTER JOIN [WarehouseCentral].[dbo].[Warehouse_InvProduct_Link] AS [wil] ON [wil].[ID] = [eri].[warehouse_assigned] LEFT OUTER JOIN [WarehouseCentral].[dbo].[Warehouse] AS [w] ON [w].[id]=[wil].[WarehouseID] LEFT OUTER JOIN [WarehouseCentral].[dbo].[Warehouse_Ship_Status] AS [wss] ON [wss].[id]=[eri].[ship_status] LEFT OUTER JOIN [eframe_shopper] AS [es] ON [es].[shopper_id]=[er].[shopper_id]' + @strSQLWhere + ' GROUP BY [er].[order_id], [w].[id], [eri].[ship_status] ORDER BY Min([er].[date_entered]) ASC, Min([er].[order_id]) ASC, Min([w].[name]) ASC'
-- Create Cursor To Loop Through Receipt Items, Generating List Of Orders, Statuses, And Warehouses
SET @strSQLCursorCreation = 'DECLARE rsReceiptItemList CURSOR GLOBAL FORWARD_ONLY READ_ONLY FOR ' + @strSQLQuery
-- Declare Necessary Receipt Item Variables
Declare @strOrderListOrderID varchar(18)
Declare @strPREVOrderListOrderID varchar(18)
Declare @dteOrderListDateEntered datetime
Declare @dtePREVOrderListDateEntered datetime
Declare @strOrderListCompany varchar(100)
Declare @strPREVOrderListCompany varchar(100)
Declare @strOrderListStatus varchar(50)
Declare @strOrderListWarehouse varchar(50)
Declare @lngOrderListUnReadCount int
Declare @lngPREVOrderListUnReadCount int
Declare @strOrderListStatuses varchar(1000)
Declare @strOrderListWarehouses varchar(1000)
Declare @bolRecordsExist tinyint
-- Open Cursor, Retrieve First Record
OPEN rsReceiptItemList
FETCH NEXT FROM rsReceiptItemList INTO @strOrderListOrderID, @dteOrderListDateEntered, @strOrderListCompany, @strOrderListStatus, @strOrderListWarehouse, @lngOrderListUnReadCount
SET @strOrderListStatuses = ''
SET @strOrderListWarehouses = ''
SET @bolRecordsExist = 0
-- Loop Cursor To Generate Order Listing
While @@FETCH_STATUS <> -1
  SET @bolRecordsExist = 1
  SET @strPREVOrderListOrderID = @strOrderListOrderID
  SET @dtePREVOrderListDateEntered = @dteOrderListDateEntered
  SET @strPREVOrderListCompany = @strOrderListCompany
  SET @lngPREVOrderListUnReadCount = @lngOrderListUnReadCount
  IF(CharIndex(@strOrderListStatus, @strOrderListStatuses) <= 0) SET @strOrderListStatuses = @strOrderListStatuses + @strOrderListStatus + ','
  IF(CharIndex(@strOrderListWarehouse, @strOrderListWarehouses) <= 0) SET @strOrderListWarehouses = @strOrderListWarehouses + @strOrderListWarehouse + ','
  FETCH NEXT FROM rsReceiptItemList INTO @strOrderListOrderID, @dteOrderListDateEntered, @strOrderListCompany, @strOrderListStatus, @strOrderListWarehouse, @lngOrderListUnReadCount
  -- OrderID Has Changed, Last Order Statuses And Warehouses Are Complete, Add To Temp Table
  IF(@strPREVOrderListOrderID <> @strOrderListOrderID)
    INSERT INTO [#OrderListTemp] ([strOrderID], [dteDateEntered], [strCompany], [strStatuses], [strWarehouses], [lngUnReadCount]) VALUES (@strPREVOrderListOrderID, @dtePREVOrderListDateEntered, @strPREVOrderListCompany, @strOrderListStatuses, @strOrderListWarehouses, @lngPREVOrderListUnReadCount)
    SET @strOrderListStatuses = ''
    SET @strOrderListWarehouses = ''
-- Don't Forget The Last Order Looped Through
IF @bolRecordsExist = 1
  INSERT INTO [#OrderListTemp] ([strOrderID], [dteDateEntered], [strCompany], [strStatuses], [strWarehouses], [lngUnReadCount]) VALUES (@strPREVOrderListOrderID, @dtePREVOrderListDateEntered, @strPREVOrderListCompany, @strOrderListStatuses, @strOrderListWarehouses, @lngOrderListUnReadCount)
-- Kill Cursor For Server's Sake
DEALLOCATE rsReceiptItemList
-- Strip Any Trailing Commas (,) Off The Statuses And Warehouses Fields
UPDATE [#OrderListTemp] SET [strStatuses]=Left([strStatuses], Len([strStatuses]) - 1) WHERE Right([strStatuses], 1) = ','
UPDATE [#OrderListTemp] SET [strWarehouses]=Left([strWarehouses], Len([strWarehouses]) - 1) WHERE Right([strWarehouses], 1) = ','
-- Return Properly Formatted Results
SELECT [strOrderID], [dteDateEntered], [strCompany], [strStatuses], [strWarehouses], [lngUnReadCount] FROM [#OrderListTemp] ORDER BY [lngID]
-- Kill The Temp Table For Server's Sake
DROP TABLE [#OrderListTemp]


i know that my connection to the database is correct so i'm zeroing on this stored procedure but can't seem to figure it out.

any help would be appreciated
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Where is the strCSOrderListSQL variable affected

what's it's value ?

it should be something like

exec MyDB.dbo.usp_GetWarehouseOrderListing param1, param2, ...

ipopDeveloperAuthor Commented:
strCSOrderListSQL  = exec usp_GetWarehouseOrderListing '', '', 1, '', ''

i ran the same stored procedure in query analyzer and it returned me 4 rows.
What does the following line mean ?

"If Not rsGetCSOrderList.EOF And Not rsGetCSOrderList.BOF Then"
When you open the recordset, rsGetCSOrderList.BOF should return true

If your sp is supposed to return several lines, I would suggest
while not rsGetCSOrderList.EOF
    if blnFirstLine then
         'do something special for the first line ?
         blnFirstLine = false
    end if
    'process line, build table, ...


The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

Anthony PerkinsCommented:
>>i ran the same stored procedure in query analyzer and it returned me 4 rows. <<
Try posting the output from SQL Query Analyzer.  I suspect that will give us a clue as to what is going on.

"Operation is not allowed when the object is closed."

sounds to me like you have not openned your connection object before attempting to execute the sql
ipopDeveloperAuthor Commented:
ShogunWade: i'm pretty sure the connection is opened. i tested this by running a different select statement on another table and it returned the value correctly

acperkins: the resuilts came out alright. the only notable one to think is that [strCompany] returns a zero length string
Anthony PerkinsCommented:
>>the resuilts came out alright. the only notable one to think is that [strCompany] returns a zero length string <<
Unfortunately that does not tell me anything.  If you can copy and paste the output here, obfuscating sensitive data if necessary, it would be more enlightening.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ipopDeveloperAuthor Commented:
here's the result set

'200310211357380789','2003-10-21 13:57:40.000','','Pending,Closed','<i>Unknown</i>,New Jersey Warehouse','1'

'200310221436158643','2003-10-22 14:36:16.000','','Pending','Indiana Warehouse','0'

'200310231731040567','2003-10-23 17:31:05.000','','Printed','New Jersey Warehouse','2

'200310272107313501','2003-10-27 21:07:31.000','','Closed','New Jersey Warehouse','1

comma delimited and with quotes for identification.


Anthony PerkinsCommented:
If that is the complete output, than I have no idea.

ipopDeveloperAuthor Commented:
i found out the error! there was null value returned on @strSQLQuery which was causing a null aggregate.

thanks for all your help and input
Anthony PerkinsCommented:
Then please close the question.

Is your question answered?   could you please close it if so
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.