Error message, operation not allowed when object is closed

[sailoosha]

Hi,

I am getting an error "operation not allowed when object is closed" when executing the following code. I am getting the runtime error at
While Recordset.EOF <> True

I am not sure where the problem is.
Plz let me know if you have idea on it.

Dim query As String
Dim RunNum As Integer
Dim rng As Range

Sub getdata()

    CLRPercent = " pr_selectpercent RUN#, 1080, 0"
    RuNnum = Split(InputBox("Enter your run numbers as  comma delimated values "), ",")
  Set ws = Application.ActiveWorkbook.Sheets.Item("Summary")
  For I = 0 To 4
     Select Case I
 
	Case 0
	Case 1

    Case 2
        
      
        For Each Cell In [B27:Z27]
        Cell.ClearContents
        Next
        query = CLRPercent
        Myrow = 27
        MyCol = 2
        GetPB1C MyCol, Myrow, RuNnum, query

	Case 4

  End Select
Next
 
End Sub

Sub GetPB1C(MyCol, Myrow, RuNnum, query)

For RunNumIndex = 0 To UBound(RuNnum)
       DefaultConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=a;Password = r ;Data Source= sab;Use Encryption for Data=False;Initial Catalog=AutomatedQA"
       Set Connection = CreateObject("ADODB.connection")
       Set Recordset = CreateObject("ADODB.Recordset")
       Connection.ConnectionString = DefaultConnectionString
       Connection.Open
       query = Replace(query, "RUN#", RuNnum(RunNumIndex))
       Set ws = Application.ActiveWorkbook.Sheets.Item("Summary")
       Recordset.Open query, Connection
       Ow = Application.ThisWorkbook.Name
          
 [B]    While Recordset.EOF <> True[/B]
        If Recordset.EOF <> True Then
          ws.Cells(Myrow, MyCol) = Recordset.Fields("percentage").Value

        End If
        Recordset.Movenext
     Wend
     MyCol = MyCol + 1
   Next

 End Sub

the query retuns a value in SQL Server with column name percentage and value 0.49
pr_selectpercen t 5, 1080, 0


the Procedure is :

USE [AutomatedQA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[pr_selectpercent] @runID int , @pid int ,@PVALUE int
As
Begin 

DECLARE @COUNTPERCENT numeric(10,2)
DECLARE @MATCH int
DECLARE @TOTAL int
DECLARE @MATCHNUM numeric(10,2)
DECLARE @TOTALNUM numeric(10,2)

select @MATCH=COUNT(perfmonvalue) from Run_Perfmon_details where runid =@runID and perfmonid = @perfmonid AND PERFMONVALUE > @PERFMONVALUE


select @TOTAL=COUNT(perfmonvalue) from Run_Perfmon_details where runid =@runID and perfmonid = @perfmonid

select @MATCHNUM = convert(numeric (10, 2),@MATCH)
select @TOTALNUM = convert(numeric (10, 2),@TOTAL)

SET @COUNTPERCENT = @MATCHNUM*100/@TOTALNUM 

SELECT @COUNTPERCENT as percentage

end

[Stewart Ross]

Your Recordset.Open statement at line 45 is clearly failing to open the recordset concerned - hence the error message about the object being closed. I cannot comment on why this is so - you'll need to set breakpoints in your routine and step through the code a line at a time to work this one out.

I would also advise that you are continually opening new connections in your FOR loop without closing the previous one at the end of the loop. You should always explicitly close such a connection before re-opening it.

-Stewart