ipopDeveloper
asked on
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.CursorLoc ation = 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_GetWarehouseOrde rListing(
@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(@strShipSta tusWhere)) <>'') SET @strSQLWhere = @strSQLWhere + ' ([eri].[ship_status] IN (' + @strShipStatusWhere + ')) AND'
IF(LTrim(RTrim(@strWarehou seWhere))< >'') SET @strSQLWhere = @strSQLWhere + '([w].[id] IN (' + @strWarehouseWhere + ')) AND'
IF(@bolCustomerService = 1) SET @strSQLWhere = @strSQLWhere + '(([er].[status]&4) = 4) AND'
IF(LTrim(RTrim(@strSearchC olumnName) )<>'') SET @strSQLWhere = @strSQLWhere + '(' + @strSearchColumnName + ' LIKE ''%' + @strSearchValue + '%'') AND'
IF(LTrim(RTrim(@strSQLWher e)) = '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([ws s].[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].[ord er_id] And [warehouseuserid]=0), 0) AS [lngOrderListUnReadCount] FROM [eframe_receipt] AS [er] INNER JOIN [eframe_receipt_item] AS [eri] ON [eri].[order_id]=[er].[ord er_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].[WarehouseI D] LEFT OUTER JOIN [WarehouseCentral].[dbo].[ Warehouse_ Ship_Statu s] AS [wss] ON [wss].[id]=[eri].[ship_sta tus] LEFT OUTER JOIN [eframe_shopper] AS [es] ON [es].[shopper_id]=[er].[sh opper_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(@strSQLCursorCreat ion)
-- Declare Necessary Receipt Item Variables
Declare @strOrderListOrderID varchar(18)
Declare @strPREVOrderListOrderID varchar(18)
Declare @dteOrderListDateEntered datetime
Declare @dtePREVOrderListDateEnter ed datetime
Declare @strOrderListCompany varchar(100)
Declare @strPREVOrderListCompany varchar(100)
Declare @strOrderListStatus varchar(50)
Declare @strOrderListWarehouse varchar(50)
Declare @lngOrderListUnReadCount int
Declare @lngPREVOrderListUnReadCou nt 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 @dtePREVOrderListDateEnter ed = @dteOrderListDateEntered
SET @strPREVOrderListCompany = @strOrderListCompany
SET @lngPREVOrderListUnReadCou nt = @lngOrderListUnReadCount
IF(CharIndex(@strOrderList Status, @strOrderListStatuses) <= 0) SET @strOrderListStatuses = @strOrderListStatuses + @strOrderListStatus + ','
IF(CharIndex(@strOrderList Warehouse, @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(@strPREVOrderListOrderI D <> @strOrderListOrderID)
BEGIN
INSERT INTO [#OrderListTemp] ([strOrderID], [dteDateEntered], [strCompany], [strStatuses], [strWarehouses], [lngUnReadCount]) VALUES (@strPREVOrderListOrderID, @dtePREVOrderListDateEnter ed, @strPREVOrderListCompany, @strOrderListStatuses, @strOrderListWarehouses, @lngPREVOrderListUnReadCou nt)
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, @dtePREVOrderListDateEnter ed, @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([strSta tuses], Len([strStatuses]) - 1) WHERE Right([strStatuses], 1) = ','
UPDATE [#OrderListTemp] SET [strWarehouses]=Left([strW arehouses] , 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
Set rsGetCSOrderList = Server.CreateObject("ADODB
rsGetCSOrderList.CursorLoc
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_GetWarehouseOrde
@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(@strShipSta
IF(LTrim(RTrim(@strWarehou
IF(@bolCustomerService = 1) SET @strSQLWhere = @strSQLWhere + '(([er].[status]&4) = 4) AND'
IF(LTrim(RTrim(@strSearchC
IF(LTrim(RTrim(@strSQLWher
BEGIN
SET @strSQLWhere = ''
END
ELSE
BEGIN
SET @strSQLWhere = Left(@strSQLWhere, Len(@strSQLWhere) - 4)
END
SET @strSQLQuery = 'SELECT LTrim(RTrim([er].[order_id
-- 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(@strSQLCursorCreat
-- Declare Necessary Receipt Item Variables
Declare @strOrderListOrderID varchar(18)
Declare @strPREVOrderListOrderID varchar(18)
Declare @dteOrderListDateEntered datetime
Declare @dtePREVOrderListDateEnter
Declare @strOrderListCompany varchar(100)
Declare @strPREVOrderListCompany varchar(100)
Declare @strOrderListStatus varchar(50)
Declare @strOrderListWarehouse varchar(50)
Declare @lngOrderListUnReadCount int
Declare @lngPREVOrderListUnReadCou
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 @dtePREVOrderListDateEnter
SET @strPREVOrderListCompany = @strOrderListCompany
SET @lngPREVOrderListUnReadCou
IF(CharIndex(@strOrderList
IF(CharIndex(@strOrderList
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(@strPREVOrderListOrderI
BEGIN
INSERT INTO [#OrderListTemp] ([strOrderID], [dteDateEntered], [strCompany], [strStatuses], [strWarehouses], [lngUnReadCount]) VALUES (@strPREVOrderListOrderID,
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,
END
-- Kill Cursor For Server's Sake
DEALLOCATE rsReceiptItemList
-- Strip Any Trailing Commas (,) Off The Statuses And Warehouses Fields
UPDATE [#OrderListTemp] SET [strStatuses]=Left([strSta
UPDATE [#OrderListTemp] SET [strWarehouses]=Left([strW
-- 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
ASKER
strCSOrderListSQL = exec usp_GetWarehouseOrderListi ng '', '', 1, '', ''
i ran the same stored procedure in query analyzer and it returned me 4 rows.
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
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
"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
>>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
Try posting the output from SQL Query Analyzer. I suspect that will give us a clue as to what is going on.
Anthony
"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
sounds to me like you have not openned your connection object before attempting to execute the sql
ASKER
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
acperkins: the resuilts came out alright. the only notable one to think is that [strCompany] returns a zero length string
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
'200310211357380789','2003
'200310221436158643','2003
'200310231731040567','2003
'200310272107313501','2003
comma delimited and with quotes for identification.
thanks
If that is the complete output, than I have no idea.
Anthony
Anthony
ASKER
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
thanks for all your help and input
Then please close the question.
Thanks,
Anthony
Thanks,
Anthony
Is your question answered? could you please close it if so
what's it's value ?
it should be something like
exec MyDB.dbo.usp_GetWarehouseO
Hilaire