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
 
     While Recordset.EOF <> True
        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