The object does not support the property or method EOF

Question

I have 3 pages:

my DB-class

<!--#include virtual="/res/adovbs.inc"-->
<%
    Class classDB
        'Declarations
        Private SQL
        Private RS
        Private Conn

        'Class Initialization
        Private Sub Class_Initialize()
            Set Conn = Server.CreateObject("ADODB.Connection")
            Conn.ConnectionString = "DSN=x"
            Call Conn.Open          
        End Sub

        'Terminate Class
        Private Sub Class_Terminate()           
            If IsObject(RS) Then 
                If RS.State = 1 Then
                    RS.Close()
                End If
            End If 
            
            If Conn.State = 1 Then
                Conn.Close()
            End If          
        End Sub

        Public Function GetRS(SQL)
            Set RS = Conn.Execute(SQL)

            GetRS = RS
        End Function


        Public Function ExecuteSQL(SQL)
            Conn.Execute(SQL)
        End Function
    End Class
%> 

And my "functions":

<!--#include virtual="/res/classDB.asp"-->
<%
    'GET PAGED MEMBERS
    Function GetMembers(page)
        If Not IsNumeric(page) Or page = "" Then page = 1

        Dim db : Set db = new classDB
        
        Set GetMembers = db.GetRS("SELECT * FROM member")
    End Function
.....

And in my page:

<%
     Dim members : Set members = GetMembers(1)
                
     If Not members.EOF Then
          Do Until members.EOF
%>

I get Error:

The object does not support the property or method 'EOF'

What might be wrong here? An Object was returned to the page. But I guess it has no properties or entries.... Impossible to debug.

EDIT:

Function GetMembers(page)
    If Not IsNumeric(page) Or page = "" Then page = 1
    Dim rs
    Dim m : Set m = new classDB     
    
    Set rs = m.GetRS("SELECT * FROM member")

    Response.Write(rs("email"))

    Set GetMembers = rs
End Function

It prints out the email for the "member".... but it doesn't work on the "next" page with the loop.

It's it returning the recordset to the page?

<%
   Dim m : Set m = GetMembers(1)

   If Not m.EOF Then
       Do Until m.EOF
%>

Is not working..... what to do?

EDIT

I changed to:

Public Function GetRS(SQL)
    Dim RS : Set RS = m_Conn.Execute(SQL)
    Set GetRS = RS
End Function

Now I get error: "The action is not allowed when the object is closed"

Google translated :)

I have the open in the Class_Initialize.... why?

Answer 1

The first error is due to the lack of Set on the returned object reference in the GetRS() method in the classDB class. The fix for this is to change the return value so it is prefixed with Set like this;

Public Function GetRS(SQL)
    Dim RS: Set RS = Conn.Execute(SQL)
    'Returning object references from a function requires the "Set" statement.
    Set GetRS = RS
End Function

You then reported a "not allowed when closed" error. This comes from the encapsulation of the ADODB.Connection object inside the classDB object instance. Because the connection is scoped to the class returning a recordset causes the connection to be severed, hence the recordset returning as closed.

The fix is to bring the connection outside the class so it lives in the context of the page and then passes it into the class via a parameter. There are varying ways of doing this, you could add a argument to the GetRS() method but this would only affect that single method. The other option is to build a sub procedure that you call after instantiation of the class to set the connection, something like;

Class classDB
    'Declarations
    Private SQL
    Private RS
    Private Conn

    Public Sub Init(conn)
        Set Conn = conn
    End Sub

    'Terminate Class
    Private Sub Class_Terminate()           
        If IsObject(RS) Then 
            If RS.State = 1 Then
                RS.Close()
            End If
        End If 
        
        If Conn.State = 1 Then
            Conn.Close()
        End If          
    End Sub

    Public Function GetRS(SQL)
        Set RS = Conn.Execute(SQL)

        GetRS = RS
    End Function


    Public Function ExecuteSQL(SQL)
        Conn.Execute(SQL)
    End Function
End Class

You then make sure to call it before using the class and pass your ADODB.Connection object.

'Key is connection object is declared in the scope of the page
Dim conn: Set conn = Server.CreateObject("ADODB.Connection")
Call conn.Open("DSN=x")

'GET PAGED MEMBERS
Function GetMembers(page)
    If Not IsNumeric(page) Or page = "" Then page = 1

    Dim db : Set db = new classDB
    'Call init() method and pass connection into the class instance.
    Call db.Init(conn)
    
    Set GetMembers = db.GetRS("SELECT * FROM member")
End Function