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

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" wrote in message
...
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

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



-----Original Message-----
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" wrote ...

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.

--

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

"DBAL" wrote ...

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.

--