Operation is not allowed when the object is closed
From: Conax (ConaxLiu_at_hotmail.com)
Date: 12/30/04
- Next message: Dan Guzman: "Re: Operation is not allowed when the object is closed"
- Previous message: Itai Gordon: "ADO 2.7 performance"
- Next in thread: Dan Guzman: "Re: Operation is not allowed when the object is closed"
- Reply: Dan Guzman: "Re: Operation is not allowed when the object is closed"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 30 Dec 2004 16:46:22 +1300
Hello!
Error Message: "Operation is not allowed when the object is closed."
What I have is a stored procedure on SQL server that goes and checks data on
a table and populates a temporary table (declared within the stored
procedure) with record ID and description of invalid records. At the end of
the stored procedure, I use a select statement to return the records from
this temporary table to the caller.
I then use VB6 to execute this stored procedure and expect the return
recordset. While the recordset object is returned, it is closed.
I tested by changing the select statement to select records from a real
table, and the recordset returned is also closed. So there would be
something that I do before calling the select statement that's causing the
problem...I think.
Any help or suggestion is very much appreciated.
Sample Code:
CREATE PROCEDURE CHECK_Table
AS
DECLARE @ErrorLimit int
DECLARE @ErrorCount int
DECLARE @TotalCount int
DECLARE @TempTable TABLE(RowNo int, Reason varchar (1000))
SET @ErrorLimit = 100
SET @ErrorCount = 0
SET @TotalCount = 0
SET DATEFORMAT dmy
/***************************************************************************
***********************
Validate Record Type : Must be ADT
****************************************************************************
**********************/
IF @ErrorCount < @ErrorLimit
BEGIN
INSERT INTO @TempTable (RowNo, Reason)
SELECT TOP 100 RowNo + 1, 'Invalid record type'
FROM dbo.MyTable
WHERE RecordType Is Null
OR RecordType <> 'INV'
ORDER BY RowNo
SET @ErrorCount = @ErrorCount + @@ROWCOUNT
END
DELETE FROM dbo.MyTable
WHERE RecordType Is Null
OR RecordType <> 'INV'
SET @TotalCount = @TotalCount + @@ROWCOUNT
/***************************************************************************
***********************
Check for duplicate records
Key : Code 1
****************************************************************************
**********************/
IF @ErrorCount < @ErrorLimit
BEGIN
INSERT INTO @TempTable (RowNo, Reason)
SELECT TOP 100 RowNo + 1, 'Duplicated record'
FROM dbo.MyTable INV,
(
SELECT Code1
FROM dbo.MyTable
GROUP BY Code1
HAVING (Count(RowNo) > 1)
) Dups
WHERE INV.Code1 = Dups.Code1
ORDER BY RowNo
SET @ErrorCount = @ErrorCount + @@ROWCOUNT
END
DELETE FROM dbo.MyTable
WHERE RowNo In
(
SELECT RowNo
FROM dbo.MyTable INV,
(
SELECT Code1
FROM dbo.MyTable
GROUP BY Code1
HAVING (Count(RowNo) > 1)
) Dups
WHERE INV.Code1 = Dups.Code1
)
SET @TotalCount = @TotalCount + @@ROWCOUNT
/***************************************************************************
***********************
Insert total error count
****************************************************************************
**********************/
IF @ErrorCount > 0
BEGIN
INSERT INTO @TempTable (RowNo, Reason)
VALUES (0, Cast(@TotalCount As varchar(1000)))
END
-- Ensure that only first 100 reasons + 1 total record are returned to avoid
overflowing
SELECT TOP 101 *
FROM @TempTable
ORDER BY RowNo ASC
GO
- Next message: Dan Guzman: "Re: Operation is not allowed when the object is closed"
- Previous message: Itai Gordon: "ADO 2.7 performance"
- Next in thread: Dan Guzman: "Re: Operation is not allowed when the object is closed"
- Reply: Dan Guzman: "Re: Operation is not allowed when the object is closed"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
