?
Solved

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

Posted on 2003-10-30
12
Medium Priority
?
356 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
  • 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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 250 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

807 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