A stupid error in Code! 2

[gus121]

Hi i get this stupid error in my code on line marked in red
Operation is not allowed when the object is closed. (Just after i open the connection!)

	Dim rs, cmd
	
	Set rs		= CreateObject("ADODB.Recordset")
	Set cmd		= CreateObject("ADODB.Command")         
	                
	
	With cmd                                                            
		.CommandText = "usp_HomeTopSeller"                                
		.CommandType = adCmdStoredProc                                  
		.ActiveConnection = gConn        
		.Parameters.Append .CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 0)
		.Parameters.Append .CreateParameter("@SiteID", adInteger, adParamInput, 0)
		
		.Parameters("@SiteID")		= gSiteID
	End With 
	
	rs.Open cmd
	
	[COLOR=red]If Not rs.EOF then[/color]
    .......
    Do While Not RS.EOF
      .......
    RS.MoveNext
    Loop
   End If

-Gus

 

[DotNetGnat]

rs.Open cmd

try this instead..

Set rs = cmd.Execute

-DNG

 

[gus121]

Hi DNG,

Thanks for your reply i tried your suggestion and it made no difference.

thanks


-Gus

 

[DotNetGnat]

do you have these variables on top of your page...

Const adCmdStoredProc = &H0004
Const adParamInput = &H0001


take a look at this similar example at this link

http://www.webconcerns.co.uk/asp/accessqueries/accessqueries.asp

post back if you have any further questions...

-DNG

 

[Kendel]

I saw this error before. What I did was "set nocount on" in my stored procedure. Something like this:

CREATE PROCEDURE usp_HomeTopSeller
AS

 SET NOCOUNT ON

Give it a try see if it will make a different.

 

[DotNetGnat]

The error he posted made me think in the other direction...

i dont know how can Set NoCount On makes the difference...

you have any idea Kendel...

-DNG

 

[gus121]

Hi Kendel,

Sorry for my late reply i just got back from the pub.
Brilliant thanks you solved my issue. Yet I agree with DNG.
Why and how can this be possible?!!!

Any input would be welcome

thanks

Angus

-Gus

 

[DotNetGnat]

if you dont set the NOCOUNT it will result the number of records and that might cause a break in displaying your recordset...

take a look at this thread...thread333-1088533

-DNG

 

[DotNetGnat]

so if the NOCOUNT is not set to ON then messages like "1 row affected" etc are returned from the stored procedure, causing the connection to break...and that results in the error you posted...

there is always lot to learn and i try to learn a lot daily posting in this forum

-DNG

 

[gus121]

Hi DNG,

Thanks for you diligent search. Here is a star for your trouble.

thanks


-Gus

 

[Sheco]

You can check the .State property of both the recordset and connection objects to avoid throwing this error.

So something like:

IF (gConn.State <> 1) THEN
  Response.Write "ADO Connection is not open."
END IF

IF (rs.State <> 1) THEN
  Response.Write "ADO Recordset is not open."
END IF

 

[Kendel]

Sorry, I totally forgot my post on this thread.

I can't explain why but ... that's how I get my problem resolved. Whenever I got "Operation is not allowed when the object is closed", the first thing I'd check is "set nocount on".

 

[DotNetGnat]

Kendel, read my previous replies...i have provided a little explanation for this odd behavior...

-DNG

 

[Kendel]

Oh yeah, I just read it. That explains why. Thanks again DNG.