Solved

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

Posted on 2003-10-30
12
347 Views
Last Modified: 2011-09-20
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))
AS
SET NOCOUNT ON
-- 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')
 BEGIN
  SET @strSQLWhere = ''
 END
ELSE
 BEGIN
  SET @strSQLWhere = Left(@strSQLWhere, Len(@strSQLWhere) - 4)
 END
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
EXECUTE(@strSQLCursorCreation)
-- 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
 BEGIN
  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)
   BEGIN
    INSERT INTO [#OrderListTemp] ([strOrderID], [dteDateEntered], [strCompany], [strStatuses], [strWarehouses], [lngUnReadCount]) VALUES (@strPREVOrderListOrderID, @dtePREVOrderListDateEntered, @strPREVOrderListCompany, @strOrderListStatuses, @strOrderListWarehouses, @lngPREVOrderListUnReadCount)
    SET @strOrderListStatuses = ''
    SET @strOrderListWarehouses = ''
   END
 END
-- Don't Forget The Last Order Looped Through
IF @bolRecordsExist = 1
 BEGIN
  INSERT INTO [#OrderListTemp] ([strOrderID], [dteDateEntered], [strCompany], [strStatuses], [strWarehouses], [lngUnReadCount]) VALUES (@strPREVOrderListOrderID, @dtePREVOrderListDateEntered, @strPREVOrderListCompany, @strOrderListStatuses, @strOrderListWarehouses, @lngOrderListUnReadCount)
 END
-- 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
thanks
0
Comment
Question by:ipopDeveloper
[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
  • 4
  • 4
  • 2
  • +1
12 Comments
 
LVL 26

Expert Comment

by:Hilaire
ID: 9651081
Where is the strCSOrderListSQL variable affected

what's it's value ?

it should be something like

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

Hilaire
0
 

Author Comment

by:ipopDeveloper
ID: 9651333
strCSOrderListSQL  = exec usp_GetWarehouseOrderListing '', '', 1, '', ''

i ran the same stored procedure in query analyzer and it returned me 4 rows.
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 9651410
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
blnFirstLine=True
while not rsGetCSOrderList.EOF
    if blnFirstLine then
         'do something special for the first line ?
         blnFirstLine = false
    end if
    'process line, build table, ...
    rs.MoveNext
wend

Hilaire

0
Industry Leaders: 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!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9651594
>>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.

Anthony
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 9651939
"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
0
 

Author Comment

by:ipopDeveloper
ID: 9652246
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
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 125 total points
ID: 9652309
>>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.

Anthony
0
 

Author Comment

by:ipopDeveloper
ID: 9652515
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.

thanks

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9652839
If that is the complete output, than I have no idea.

Anthony
0
 

Author Comment

by:ipopDeveloper
ID: 9654442
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
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9654793
Then please close the question.

Thanks,
Anthony
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 10021045
Is your question answered?   could you please close it if so
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!

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

707 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