VBScript Class/Object Problem

Question

I would like an object that is used on many pages to contain its own connection and recordset variables so that they do not need to be declared on each page that wants to directly access the recordset, rather than interact with the functions of the object that normally handle this.

However, the recordset is apparently not becoming an object.

<!--
METADATA
TYPE="TypeLib"
NAME="Microsoft ActiveX Data Objects 2.5 Library"
UUID="{00000205-0000-0010-8000-00AA006D2EA4}"
-->

And

<%
Option Explicit
Class cls
Public conn
Public rs

Public Sub OpenRS(ByRef conn, ByRef rs, ByRef sql, ByRef Mode, 
                  ByRef CursorType, ByRef LockType)
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.Provider = "Microsoft.Jet.OLEDB.4.0"
    conn.Mode = Mode
    conn.Open = Server.MapPath(".") & "\mb\testdb.mdb"
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.CursorType = CursorType
    rs.LockType = LockType
    rs.ActiveConnection = conn
    rs.Open sql
End Sub
Public Sub CloseRS(ByRef conn, ByRef rs)
    If VarType(rs) = vbObject Then rs.Close
    Set rs = Nothing
    If VarType(conn) = vbObject Then conn.Close
    Set conn = Nothing
End Sub
Private Sub Class_Initialize()
    Set conn = Nothing
    Set rs = Nothing
End Sub
End Class

Dim a: Set a = New cls
a.OpenRS a.conn,a.rs, "SELECT * FROM emp", 
         adModeRead, adOpenForwardOnly, adLockPessimistic
Response.Write(a.rs.EOF)
%>

Answer 1

Try to do not pass that rs parameter:

Public Sub OpenRS(ByRef conn, ByRef sql, ByRef Mode, 
                  ByRef CursorType, ByRef LockType)
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.Provider = "Microsoft.Jet.OLEDB.4.0"
    conn.Mode = Mode
    conn.Open = Server.MapPath(".") & "\mb\testdb.mdb"
    '' // will set public property, not that old parameter
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.CursorType = CursorType
    rs.LockType = LockType
    rs.ActiveConnection = conn
    rs.Open sql
End Sub

and to use like this

a.OpenRS a.conn, "SELECT * FROM emp", 
         adModeRead, adOpenForwardOnly, adLockPessimistic

Answer 2

This works for me. Notice I am not passing the connection or recordset when calling OpenRS.

Class cls
    Public conn
    Public rs

    Public Sub OpenRS(ByRef sql, ByRef Mode, ByRef CursorType, ByRef LockType)
        Set conn = Server.CreateObject("ADODB.Connection")
        conn.Provider = "Microsoft.Jet.OLEDB.4.0"
        conn.Mode = Mode
        conn.Open = Server.MapPath(".") & "\mb\testdb.mdb"
        Set rs = Server.CreateObject("ADODB.Recordset")
        rs.CursorType = CursorType
        rs.LockType = LockType
        rs.ActiveConnection = conn
        rs.Open sql
    End Sub

    Public Sub CloseRS(ByRef conn, ByRef rs)
        If VarType(rs) = vbObject Then rs.Close
        Set rs = Nothing
        If VarType(conn) = vbObject Then conn.Close
        Set conn = Nothing
    End Sub

    Private Sub Class_Initialize()
        Set conn = Nothing
        Set rs = Nothing
    End Sub
End Class 


Set a = New cls
a.OpenRS "SELECT * FROM emp", adModeRead, adOpenForwardOnly, adLockPessimistic

You don't have to pass the object it's own properties, it already has access to them.

Answer 3

My version:-

Option Explicit
Class RSManager
  Private conn
  Private rs

  Public Property Get RecordSet()
     Set RecordSet = rs
  End Property

  Public Function OpenRS(ByVal sql, ByVal Mode, 
                  ByVal CursorType, ByVal LockType)
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.Provider = "Microsoft.Jet.OLEDB.4.0"
    conn.Mode = Mode
    conn.Open = Server.MapPath("/App_Data") & "\mb\testdb.mdb"

    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.CursorType = CursorType
    rs.LockType = LockType
    rs.ActiveConnection = conn
    rs.Open sql
    Set OpenRS = rs
  End Sub

  Public Sub CloseRS()
    If Not rs Is Nothing Then
      If rs.State = adStateOpen Then rs.Close
      Set rs = Nothing
    End If
    If Not conn Is Nothing Then
        If conn.State = adStateOpen Then conn.Close
        Set conn = Nothing
    End If
  End Sub

  Private Sub Class_Initialize()
    Set conn = Nothing
    Set rs = Nothing
  End Sub

  Private Sub Class_Terminate()
    CloseRS
  End Sub
End Class

Dim RSMEmp: Set RSMEmp = New RSManager
Dim rs : Set rs = RSMEMp.OpenRS "SELECT * FROM emp", 
     adModeRead, adOpenForwardOnly, adLockPessimistic

Response.Write(rs.EOF)

Notes:-

• the connection is now private and the access to the internal recordset is Readonly.
• OpenRS does not rely on the caller to pass in its own members for assignment
• OpenRS returns the recordset for convience
• OpenRS uses an absolute path in MapPath, thus eliminating coupling of the include with a specific folder.
• CloseRS now belts and braces
• Terminate event added to ensure recordset and connection is closed if the consuming code fails to call CloseRS.

Edit

Perhaps I should elaborate on "CloseRS now belts and braces" its actually a bit more than that. The original does seem to be attempting to ensure that Close doesn't get called when unnecessary or when the variable is set to nothing. Unfortunately the VarType of a variable set to Nothing is still vbObject hence in the original code calling CloseRS when it has already been called or OpenRS had never been called would result in an error.