Recordset or Object is closed HELP PLEASE!

[DBAL]

Guys,
I am getting this stubborn error anytime I try to do
anything with my recordset: '3704' Operation is not
allowed when the object is closed.

I don't understand why this recordset is closed no matter
how I write the code for the recordset. I've tried
the 'open method', the 'connection object' method, and
now the 'command object' method (if the recordset is the
object the error is referencing).

I have added SET NOCOUNT ON, and SET ANSI_WARNINGS OFF to
the SQL that is passed under the variable strSQL1. But
it doesn't help. PLEASE HELP ME CRACK THIS ONE!!!!

Here is my code: (there is some debugging code near the
bottom):

'Create the connection string and the Recordset
Set adoCn = New ADODB.Connection
With adoCn
.ConnectionString = "Provider=SQLOLEDB;Integrated
Security=SSPI;Trusted_Connection=yes;Persist Security
Info=False;Server=SQLSERVER;Database=m2mdata01;Uid=sa;
Pwd=;"
.CursorLocation = adUseServer
.Open
End With

Set adoCm = New ADODB.Command
With adoCm
Set .ActiveConnection = adoCn
.CommandType = adCmdText
.CommandText = strSQL1
.Execute
End With

Set adoRs = New ADODB.Recordset
With adoRs
Set .ActiveConnection = adoCn
.LockType = adLockReadOnly
.CursorLocation = adUseServer
.CursorType = adOpenStatic
.Open adoCm '"SET NOCOUNT ON SET ANSI_WARNINGS OFF"
End With


'Attemps to debug below.
MsgBox "Connection state is " & adoCn.State

MsgBox "Recordset state is " & adoRs.State

adoRs.NextRecordset

MsgBox adoRs.State

adoRs.NextRecordset

MsgBox adoRs.State

Do While adoRs.State <> adStateOpen
Set adoRs = adoRs.NextRecordset
lTryCount = lTryCount + 1
If lTryCount > 100 Then
Exit Do
End If
Loop
MsgBox lTryCount

If adoRs.BOF And adoRs.EOF Then
MsgBox "No records in this recordset!"
End If

 

[ijb]

Am I missing something, why are you moving to the next recordset?
adoRs.MoveNext moves through the recordset.

--
If I've mis-understood the question please tell me.

HTH

ijb

Remove nospam from my e-mail address to talk direct

Not MCSD, MVP, TLA, P&P, PCMCIA, etc just trying to help

 

[DBAL]

The reason why I was trying to move to the next recordset
was because my only stab at the problem here was that
somehow SQL Server was returning two or three recordsets
because of the counting and Ansi warnings. In Query
Analyzer when I run the straight SQL I get the following
in addition to my data:

(113 row(s) affected)

Warning: Null value is eliminated by an aggregate or
other SET operation.


And so someone's comments on this stated that they had
gotten around the '3704' (Operation is not allowed when
the object is closed) error message by moving to the
recordset that contains the actual data... Apparently
they believed that the SQL Server was returning another
recordset with that SQL response above. Does that make
sense? It was my only lead so far. But that attempt
wasn't working either.

DBAL

 

[Jamie Collins]

...

The reason why I was trying to move to the next recordset
was because my only stab at the problem here was that
somehow SQL Server was returning two or three recordsets

The only way I know to create such a heirarchical recordset is using
the MSDataShape provider with the SHAPE syntax.

In Query
Analyzer when I run the straight SQL I get the following
in addition to my data:

(113 row(s) affected)

Are you sure your SQL returns a rowset (which it must do to create a
recordset)? For example neither

SELECT * INTO MyNewTable FROM MyTable

nor

INSERT INTO MyOtherTable SELECT * MyTable

return a rowset ASAIK even though they use the SELECT syntax. If in
doubt, post your SQL.

Jamie.

--

 

[DBAL]

Jamie, thank you for your reply.
When I run the SQL in Query Analyzer it seems to run
fine. I'm not sure why it wouldn't create a resultset.
Does it not create a recordset to be returned to excel
when there is definitely are query returned in Query
Analzyer at the SQLSERVER? I'm not sure anymore I guess:

Here is the SQL:

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

USE m2mdata01

DECLARE @IntCurrMonth INT
DECLARE @IntYearBeforeLast INT
DECLARE @IntLastYear INT
DECLARE @IntThisYear INT
DECLARE @DateSepFirstYearBeforeLast DATETIME
DECLARE @DateAugThirtyFirstLastYear DATETIME
DECLARE @DateSepFirstLastYear DATETIME
DECLARE @DateAugThirtyFirstThisYear DATETIME

SET @IntCurrMonth=MONTH(CURRENT_TIMESTAMP)
SET @IntYearBeforeLast=YEAR(DATEADD(YEAR,-
2,CURRENT_TIMESTAMP))
SET @IntLastYear=YEAR(DATEADD(YEAR,-1,CURRENT_TIMESTAMP))
SET @IntThisYear=YEAR(CURRENT_TIMESTAMP)
SET @DateSepFirstYearBeforeLast=CAST(SUBSTRING(CONVERT
(CHAR(8),DATEADD(YEAR,-2,CURRENT_TIMESTAMP),112),1,4)
+'0901' AS DATETIME)
SET @DateAugThirtyFirstLastYear=CAST(SUBSTRING(CONVERT
(CHAR(8),DATEADD(YEAR,-1,CURRENT_TIMESTAMP),112),1,4)
+'0831' AS DATETIME)
SET @DateSepFirstLastYear=CAST(SUBSTRING(CONVERT(CHAR
(8),DATEADD(YEAR,-1,CURRENT_TIMESTAMP),112),1,4)+'0901'
AS DATETIME)
SET @DateAugThirtyFirstThisYear=CAST(SUBSTRING(CONVERT
(CHAR(8),CURRENT_TIMESTAMP,112),1,4)+'0831' AS DATETIME)

IF @IntCurrMonth BETWEEN 1 AND 8
BEGIN
SELECT ProdClass=Inprod.fpc_name,
Part_Num=Aritem.fpartno,
Sep_Sales=ISNULL(SUM(CASE WHEN MONTH(Armast.finvdate)
= 9 AND YEAR(Armast.finvdate) = @IntYearBeforeLast THEN
Aritem.ftotprice END), 0.00),
Sep_Units=FLOOR(ISNULL(SUM(CASE WHEN MONTH
(Armast.finvdate) = 9 AND YEAR(Armast.finvdate) =
@IntYearBeforeLast THEN
(Aritem.fshipqty*Inmast.fnusrqty1) END), 0)),
Oct_Sales=ISNULL(SUM(CASE WHEN MONTH(Armast.finvdate)
= 10 AND YEAR(Armast.finvdate) = @IntYearBeforeLast THEN
Aritem.ftotprice END), 0.00),
Oct_Units=FLOOR(ISNULL(SUM(CASE WHEN MONTH
(Armast.finvdate) = 10 AND YEAR(Armast.finvdate) =
@IntYearBeforeLast THEN
(Aritem.fshipqty*Inmast.fnusrqty1) END), 0)),
Nov_Sales=ISNULL(SUM(CASE WHEN MONTH(Armast.finvdate)
= 11 AND YEAR(Armast.finvdate) = @IntYearBeforeLast THEN
Aritem.ftotprice END), 0.00),
Nov_Units=FLOOR(ISNULL(SUM(CASE WHEN MONTH
(Armast.finvdate) = 11 AND YEAR(Armast.finvdate) =
@IntYearBeforeLast THEN
(Aritem.fshipqty*Inmast.fnusrqty1) END), 0)),
Dec_Sales=ISNULL(SUM(CASE WHEN MONTH(Armast.finvdate)
= 12 AND YEAR(Armast.finvdate) = @IntYearBeforeLast THEN
Aritem.ftotprice END), 0.00),
Dec_Units=FLOOR(ISNULL(SUM(CASE WHEN MONTH
(Armast.finvdate) = 12 AND YEAR(Armast.finvdate) =
@IntYearBeforeLast THEN
(Aritem.fshipqty*Inmast.fnusrqty1) END), 0)),
Jan_Sales=ISNULL(SUM(CASE WHEN MONTH(Armast.finvdate)
= 1 AND YEAR(Armast.finvdate) = @IntLastYear THEN
Aritem.ftotprice END), 0.00),
Jan_Units=FLOOR(ISNULL(SUM(CASE WHEN MONTH
(Armast.finvdate) = 1 AND YEAR(Armast.finvdate) =
@IntLastYear THEN (Aritem.fshipqty*Inmast.fnusrqty1)
END), 0)),
Feb_Sales=ISNULL(SUM(CASE WHEN MONTH(Armast.finvdate)
= 2 AND YEAR(Armast.finvdate) = @IntLastYear THEN
Aritem.ftotprice END), 0.00),
Feb_Units=FLOOR(ISNULL(SUM(CASE WHEN MONTH
(Armast.finvdate) = 2 AND YEAR(Armast.finvdate) =
@IntLastYear THEN (Aritem.fshipqty*Inmast.fnusrqty1)
END), 0)),
Mar_Sales=ISNULL(SUM(CASE WHEN MONTH(Armast.finvdate)
= 3 AND YEAR(Armast.finvdate) = @IntLastYear THEN
Aritem.ftotprice END), 0.00),
Mar_Units=FLOOR(ISNULL(SUM(CASE WHEN MONTH
(Armast.finvdate) = 3 AND YEAR(Armast.finvdate) =
@IntLastYear THEN (Aritem.fshipqty*Inmast.fnusrqty1)
END), 0)),
Apr_Sales=ISNULL(SUM(CASE WHEN MONTH(Armast.finvdate)
= 4 AND YEAR(Armast.finvdate) = @IntLastYear THEN
Aritem.ftotprice END), 0.00),
Apr_Units=FLOOR(ISNULL(SUM(CASE WHEN MONTH
(Armast.finvdate) = 4 AND YEAR(Armast.finvdate) =
@IntLastYear THEN (Aritem.fshipqty*Inmast.fnusrqty1)
END), 0)),
May_Sales=ISNULL(SUM(CASE WHEN MONTH(Armast.finvdate)
= 5 AND YEAR(Armast.finvdate) = @IntLastYear THEN
Aritem.ftotprice END), 0.00),
May_Units=FLOOR(ISNULL(SUM(CASE WHEN MONTH
(Armast.finvdate) = 5 AND YEAR(Armast.finvdate) =
@IntLastYear THEN (Aritem.fshipqty*Inmast.fnusrqty1)
END), 0)),
Jun_Sales=ISNULL(SUM(CASE WHEN MONTH(Armast.finvdate)
= 6 AND YEAR(Armast.finvdate) = @IntLastYear THEN
Aritem.ftotprice END), 0.00),
Jun_Units=FLOOR(ISNULL(SUM(CASE WHEN MONTH
(Armast.finvdate) = 6 AND YEAR(Armast.finvdate) =
@IntLastYear THEN (Aritem.fshipqty*Inmast.fnusrqty1)
END), 0)),
Jul_Sales=ISNULL(SUM(CASE WHEN MONTH(Armast.finvdate)
= 7 AND YEAR(Armast.finvdate) = @IntLastYear THEN
Aritem.ftotprice END), 0.00),
Jul_Units=FLOOR(ISNULL(SUM(CASE WHEN MONTH
(Armast.finvdate) = 7 AND YEAR(Armast.finvdate) =
@IntLastYear THEN (Aritem.fshipqty*Inmast.fnusrqty1)
END), 0)),
Aug_Sales=ISNULL(SUM(CASE WHEN MONTH(Armast.finvdate)
= 8 AND YEAR(Armast.finvdate) = @IntLastYear THEN
Aritem.ftotprice END), 0.00),
Aug_Units=FLOOR(ISNULL(SUM(CASE WHEN MONTH
(Armast.finvdate) = 8 AND YEAR(Armast.finvdate) =
@IntLastYear THEN (Aritem.fshipqty*Inmast.fnusrqty1)
END), 0)),
Tot_Sales=ISNULL(SUM(CASE WHEN Armast.finvdate
BETWEEN @DateSepFirstYearBeforeLast AND
@DateAugThirtyFirstLastYear THEN Aritem.ftotprice END),
0.00),
Tot_Units=FLOOR(ISNUll(SUM(CASE WHEN Armast.finvdate
BETWEEN @DateSepFirstYearBeforeLast AND
@DateAugThirtyFirstLastYear THEN
(Aritem.fshipqty*Inmast.fnusrqty1) END), 0))
FROM Armast JOIN Aritem ON (UPPER(Armast.fcinvoice)
=UPPER(Aritem.fcinvoice))
JOIN Inmast ON (Aritem.fac=Inmast.fac AND
Aritem.fpartno=Inmast.fpartno AND Aritem.frev=Inmast.frev)
JOIN Inprod ON (Inmast.fprodcl=Inprod.fpc_number)
WHERE (Armast.finvtype='N') AND (Armast.fcStatus='U'
OR Armast.fcStatus='P' OR Armast.fcStatus='F')
AND (LEFT(Inprod.fpc_name,2)<>'RM') AND
(Inprod.fpc_name<>'RAW MATERIAL')
AND Aritem.fpartno >='99000000' AND Aritem.fpartno
<= '99999999'
GROUP BY Inprod.fpc_name, Aritem.fpartno
WITH ROLLUP
ORDER BY GROUPING(Inprod.fpc_name), Inprod.fpc_name,
GROUPING(Aritem.fpartno), Aritem.fpartno
END

ELSE IF @IntCurrMonth BETWEEN 9 AND 12
BEGIN
SELECT ProdClass=Inprod.fpc_name,
Part_Num=Aritem.fpartno,
Sep_Sales=ISNULL(SUM(CASE WHEN MONTH(Armast.finvdate)
= 9 AND YEAR(Armast.finvdate) = @IntLastYear THEN
Aritem.ftotprice END), 0.00),
Sep_Units=FLOOR(ISNULL(SUM(CASE WHEN MONTH
(Armast.finvdate) = 9 AND YEAR(Armast.finvdate) =
@IntLastYear THEN (Aritem.fshipqty*Inmast.fnusrqty1)
END), 0)),
Oct_Sales=ISNULL(SUM(CASE WHEN MONTH(Armast.finvdate)
= 10 AND YEAR(Armast.finvdate) = @IntLastYear THEN
Aritem.ftotprice END), 0.00),
Oct_Units=FLOOR(ISNULL(SUM(CASE WHEN MONTH
(Armast.finvdate) = 10 AND YEAR(Armast.finvdate) =
@IntLastYear THEN (Aritem.fshipqty*Inmast.fnusrqty1)
END), 0)),
Nov_Sales=ISNULL(SUM(CASE WHEN MONTH(Armast.finvdate)
= 11 AND YEAR(Armast.finvdate) = @IntLastYear THEN
Aritem.ftotprice END), 0.00),
Nov_Units=FLOOR(ISNULL(SUM(CASE WHEN MONTH
(Armast.finvdate) = 11 AND YEAR(Armast.finvdate) =
@IntLastYear THEN (Aritem.fshipqty*Inmast.fnusrqty1)
END), 0)),
Dec_Sales=ISNULL(SUM(CASE WHEN MONTH(Armast.finvdate)
= 12 AND YEAR(Armast.finvdate) = @IntLastYear THEN
Aritem.ftotprice END), 0.00),
Dec_Units=FLOOR(ISNULL(SUM(CASE WHEN MONTH
(Armast.finvdate) = 12 AND YEAR(Armast.finvdate) =
@IntLastYear THEN (Aritem.fshipqty*Inmast.fnusrqty1)
END), 0)),
Jan_Sales=ISNULL(SUM(CASE WHEN MONTH(Armast.finvdate)
= 1 AND YEAR(Armast.finvdate) = @IntThisYear THEN
Aritem.ftotprice END), 0.00),
Jan_Units=FLOOR(ISNULL(SUM(CASE WHEN MONTH
(Armast.finvdate) = 1 AND YEAR(Armast.finvdate) =
@IntThisYear THEN (Aritem.fshipqty*Inmast.fnusrqty1)
END), 0)),
Feb_Sales=ISNULL(SUM(CASE WHEN MONTH(Armast.finvdate)
= 2 AND YEAR(Armast.finvdate) = @IntThisYear THEN
Aritem.ftotprice END), 0.00),
Feb_Units=FLOOR(ISNULL(SUM(CASE WHEN MONTH
(Armast.finvdate) = 2 AND YEAR(Armast.finvdate) =
@IntThisYear THEN (Aritem.fshipqty*Inmast.fnusrqty1)
END), 0)),
Mar_Sales=ISNULL(SUM(CASE WHEN MONTH(Armast.finvdate)
= 3 AND YEAR(Armast.finvdate) = @IntThisYear THEN
Aritem.ftotprice END), 0.00),
Mar_Units=FLOOR(ISNULL(SUM(CASE WHEN MONTH
(Armast.finvdate) = 3 AND YEAR(Armast.finvdate) =
@IntThisYear THEN (Aritem.fshipqty*Inmast.fnusrqty1)
END), 0)),
Apr_Sales=ISNULL(SUM(CASE WHEN MONTH(Armast.finvdate)
= 4 AND YEAR(Armast.finvdate) = @IntThisYear THEN
Aritem.ftotprice END), 0.00),
Apr_Units=FLOOR(ISNULL(SUM(CASE WHEN MONTH
(Armast.finvdate) = 4 AND YEAR(Armast.finvdate) =
@IntThisYear THEN (Aritem.fshipqty*Inmast.fnusrqty1)
END), 0)),
May_Sales=ISNULL(SUM(CASE WHEN MONTH(Armast.finvdate)
= 5 AND YEAR(Armast.finvdate) = @IntThisYear THEN
Aritem.ftotprice END), 0.00),
May_Units=FLOOR(ISNULL(SUM(CASE WHEN MONTH
(Armast.finvdate) = 5 AND YEAR(Armast.finvdate) =
@IntThisYear THEN (Aritem.fshipqty*Inmast.fnusrqty1)
END), 0)),
Jun_Sales=ISNULL(SUM(CASE WHEN MONTH(Armast.finvdate)
= 6 AND YEAR(Armast.finvdate) = @IntThisYear THEN
Aritem.ftotprice END), 0.00),
Jun_Units=FLOOR(ISNULL(SUM(CASE WHEN MONTH
(Armast.finvdate) = 6 AND YEAR(Armast.finvdate) =
@IntThisYear THEN (Aritem.fshipqty*Inmast.fnusrqty1)
END), 0)),
Jul_Sales=ISNULL(SUM(CASE WHEN MONTH(Armast.finvdate)
= 7 AND YEAR(Armast.finvdate) = @IntThisYear THEN
Aritem.ftotprice END), 0.00),
Jul_Units=FLOOR(ISNULL(SUM(CASE WHEN MONTH
(Armast.finvdate) = 7 AND YEAR(Armast.finvdate) =
@IntThisYear THEN (Aritem.fshipqty*Inmast.fnusrqty1)
END), 0)),
Aug_Sales=ISNULL(SUM(CASE WHEN MONTH(Armast.finvdate)
= 8 AND YEAR(Armast.finvdate) = @IntThisYear THEN
Aritem.ftotprice END), 0.00),
Aug_Units=FLOOR(ISNULL(SUM(CASE WHEN MONTH
(Armast.finvdate) = 8 AND YEAR(Armast.finvdate) =
@IntThisYear THEN (Aritem.fshipqty*Inmast.fnusrqty1)
END), 0)),
Tot_Sales=ISNULL(SUM(CASE WHEN Armast.finvdate
BETWEEN @DateSepFirstLastYear AND
@DateAugThirtyFirstThisYear THEN Aritem.ftotprice END),
0.00),
Tot_Units=FLOOR(ISNUll(SUM(CASE WHEN Armast.finvdate
BETWEEN @DateSepFirstLastYear AND
@DateAugThirtyFirstThisYear THEN
(Aritem.fshipqty*Inmast.fnusrqty1) END), 0))
FROM Armast JOIN Aritem ON (UPPER(Armast.fcinvoice)
=UPPER(Aritem.fcinvoice))
JOIN Inmast ON (Aritem.fac=Inmast.fac AND
Aritem.fpartno=Inmast.fpartno AND Aritem.frev=Inmast.frev)
JOIN Inprod ON (Inmast.fprodcl=Inprod.fpc_number)
WHERE (Armast.finvtype='N') AND (Armast.fcStatus='U'
OR Armast.fcStatus='P' OR Armast.fcStatus='F')
AND (LEFT(Inprod.fpc_name,2)<>'RM') AND
(Inprod.fpc_name<>'RAW MATERIAL')
AND Aritem.fpartno >='99000000' AND Aritem.fpartno
<= '99999999'
GROUP BY Inprod.fpc_name, Aritem.fpartno
WITH ROLLUP
ORDER BY GROUPING(Inprod.fpc_name), Inprod.fpc_name,
GROUPING(Aritem.fpartno), Aritem.fpartno
END


Thanks, DBAL

 

[Jamie Collins]

Jamie, thank you for your reply.
When I run the SQL in Query Analyzer it seems to run
fine. I'm not sure why it wouldn't create a resultset.
Does it not create a recordset to be returned to excel
when there is definitely are query returned in Query
Analzyer at the SQLSERVER? I'm not sure anymore I guess:

Here is the SQL:

<snip>

Eek! The query from hell!

<More politely> I have no experience of executing that kind of T-SQL
text from a client (e.g. Excel). I'd expect it to be wrapped in a
stored procedure on the server and the stored proc executed from the
client.

Jamie.

--

 

[DBAL]

Thanks.
I need to find out more about how to do a Stored
Procedure. I have done some isolation to find out what
is causing the error. The code runs fine with a
different query that is simpler. The main difference is
that this query Declares Variables and Sets those
variables to system times and dates. This is definitely
where the problem lies. It won't return a recordset with
a query that has Declare Statments. Or atleast that is
what I have narrowed it down to so far. The Stored
Procedure idea must be the only way because passing the
SQL Server the query is giving me that error:'3704'
Operation is not allowed when the object is closed.

DBAL

 

[DBAL]

I just wanted to express my gratitude to all for helping
me with this. I believe I have found the problem!

As relieved as I am, I'm pissed it was something so
stupid. I mean while originally there were some slight
issues with my VBA code and while there were a few
statements that I probably needed to add to my SQL like
the SET NOCOUNT ON etc... (and the exercise definitely
helped me tighten up my code, error trap, and make it
more efficient)

After all this it was in the very top line of the SQL.
It was that USE statement. I deleted that sucker,
realizing that it was not necessary because the
connection string specifies the initial catalog and
BAM!!!! IT WORKS!!

THANKS because all your suggestions helped me make a
better product.
DBAL.

 

[mikemalachy]

DBAL,

Are you sure that in all instances of the SQL statement, you will
actually have a resultset? I have had this problem before and it
occurred when my statement returned no data. My workaround was to
check if recordset = nothing before referencing it.


Good luck.

 

[Jamie Collins]

hi
i've read your solutions but they don't help me. this is the first time i'm doing VBA. i get the same error but i do use a stored procedure. it gives me that '3704 operation not allowed when the object is closed' on this statement :

Sheet1.Range("A4").CopyFromRecordset rsBDA

Post the rest of your code.

Jamie.

--

 

[Jamie Collins]

Are you sure your stored proc is returning a results set? Print the
cmdBDA.CommandText value into the immediate window (e.g. using
Debug.Print) and paste into QA to run the exact text from there. (BTW
you do know that prefixing your SQL Server stored procedures with sp_
is a bad idea, right?)

Jamie.

--