[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2003-10-30
12
Medium Priority
?
353 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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

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!

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

656 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