Solved

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

Posted on 2003-10-30
12
342 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

822 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