How To Use GetRows to Pass Recordset Data Back from OLE Server

MORE INFORMATION

Sample Program

This example assumes that you already have access to an ODBC data source. In this example we use the PUBS database that is distributed with Microsoft SQL Server, but this can be changed by modifying the code. Because this article contains a large amount of code, we suggest you obtain it electronically so you can paste it directly into your project rather than typing in each line.

This example requires you to run two instances of Visual Basic 4.0 32-bit at the same time, one for the OLE client application, and one for the OLE server application. We will not set up a remote OLE server although this can be done by referring to the following article :

Step One - Create the OLE Server

1. Start a new instance of Visual Basic 4.0 32-bit. Form1 is created by default. Change the Caption property of Form1 to "rdoServer". Also, under the Tools, Options menu Project Tab, set the Project Name to "rdoServer", and the Application Description to "rdoServer".
2. From the Insert menu, add a Class Module (Class1) to your project.
3. In the Properties window for Class1, set the following properties:

      Property      Value
      ------------------------------------
      Instancing    2 - Creatable MultiUse
      Name          rdoClass
      Public        True
   
   						

4. Place the following code into the General Declaration section of Class1:

      Option Explicit
      Private en As rdoEnvironment
      Private cn As rdoConnection
      Private rs As rdoResultset
   
      Private Sub Class_Initialize()
        'when an instance of the rdoserver class gets created in the client
        'this code is executed
        Set en = rdoEngine.rdoEnvironments(0)
        en.CursorDriver = rdUseIfNeeded
        Dim strConn As String
        strConn = "driver={SQL Server}; Server=myserver; Database=pubs; " & _
          "UID=myuid;PWD=mypwd;"
        Set cn = en.OpenConnection(dsname:="", Prompt:=rdDriverNoPrompt, _
          Connect:=strConn)
      End Sub
      Public Function QueryResultset(strSQL As String)
        Set rs = cn.OpenResultset(Name:=strSQL, Type:=rdOpenStatic)
      End Function
      Public Function GetResultset() As rdoResultset
        Set GetResultset = rs
      End Function
      Public Function RowCount() As Integer
        RowCount = rs.RowCount
      End Function
      Public Function GetColHeaders()
        Dim intColumnCount As Integer
        Dim intCurrentColumn As Integer
        Dim varColHeaders()
        intColumnCount = rs.rdoColumns.Count
        ReDim varColHeaders(intColumnCount)
        For intCurrentColumn = 0 To intColumnCount - 1
          varColHeaders(intCurrentColumn) = _
      rs.rdoColumns(intCurrentColumn).Name
        Next intCurrentColumn
        GetColHeaders = varColHeaders
      End Function
      Public Function GetAllRows()
        Dim rsTemp As rdoResultset    'used to get rowcount
        Set rsTemp = cn.OpenResultset(Name:="SELECT Count(*) from authors", _
          Type:=rdOpenForwardOnly)
        GetAllRows = rs.GetRows(CLng(rsTemp(0)))
        rsTemp.Close
        Set rsTemp = Nothing
      End Function
      Public Function GetSomeRows(intHowMany As Integer)
        GetSomeRows = rs.GetRows(intHowMany)
      End Function
      Public Function EOF()
        EOF = rs.EOF
      End Function
      Public Sub MoveFirst()
        rs.MoveFirst
      End Sub
      Public Sub MovePrevious()
        rs.MovePrevious
      End Sub
      Public Sub MoveNext()
        rs.MoveNext
      End Sub
      Public Sub MoveLast()
        rs.MoveLast
      End Sub
      Private Sub Class_Terminate()
        rs.Close
        cn.Close
      End Sub
   
   						

5. From the Tools menu, choose Options. In the StartMode group of the Project tab, choose OLE Server. You now have a complete OLE automation server. You can choose Make EXE to make an .EXE file that you can run later, or you can choose Make DLL to make an In process OLE Server. For this example, we'll just run the server in the Visual Basic IDE.
6. Now you're ready to automate your server. Start the program by choosing Start from the Run menu or by pressing the F5 key.
7. Minimize this instance of Visual Basic.

Step Two - Create the OLE Client

1. Start a new instance of Visual Basic 4.0 32-bit. Form1 is created by default.
2. Add three command buttons to Form1. Change the caption property of Command1 to "Local resultset", the caption property of Commmand2 to "Remote resultset with local pointer", and the caption property of Command3 to "Remote resultset using GetRows".
3. Add a Microsoft Grid Control to Form1, Grid1 by default. Use a normal, not data bound, grid for this example. Size Grid1 to cover most of Form1. If you cannot find the Microsoft Grid Control in the Toolbox go to the Tools menu and select it under Custom Controls.
4. Paste the following code into the General Declarations section of form1.

      Option Explicit
      Private rdoObject As rdoServer.rdoClass  'must be referenced in project
      Private Sub Form_Load()
        Set rdoObject = New rdoServer.rdoClass
      End Sub
      Private Sub Command1_Click()
        Dim en As rdoEnvironment
        Dim cn As rdoConnection
        Dim rsLocal As rdoResultset
        Set en = rdoEngine.rdoEnvironments(0)
        en.CursorDriver = rdUseIfNeeded    'Server Side cursors if available
        Dim strConn As String
        strConn = "driver={SQL Server}; Server=myserver; Database=pubs; " & _
          "UID=myuid;PWD=mypwd;"
        Set cn = en.OpenConnection(dsname:="", Prompt:=rdDriverNoPrompt, _
          Connect:=strConn)
        Set rsLocal = cn.OpenResultset(Name:="SELECT * from authors", _
          Type:=rdOpenStatic)
        Call FillGridFromRS(rsLocal)            'call procedure to fill grid
        rsLocal.Close
        cn.Close
        en.Close
      End Sub
      Private Sub Command2_Click()
        Dim rsPointer As rdoResultset           'pointer to rdoResultset
        rdoObject.QueryResultset ("SELECT * from authors")
        Set rsPointer = rdoObject.GetResultset()
        Call FillGridFromRS(rsPointer)
      End Sub
      Sub FillGridFromRS(rdoRS As rdoResultset)
        Grid1.Cols = rdoRS.rdoColumns.Count
        Dim intCurrentRow As Integer
        Dim intCurrentColumn As Integer
        Dim intColumnCount As Integer
        intColumnCount = rdoRS.rdoColumns.Count
        For intCurrentColumn = 0 To intColumnCount - 1
          Grid1.Row = 0
          Grid1.Col = intCurrentColumn
          Grid1.ColWidth(intCurrentColumn) = 1250
          Grid1.Text = rdoRS.rdoColumns(intCurrentColumn).Name
        Next intCurrentColumn
        intCurrentRow = 1
        Grid1.Rows = intCurrentRow + 1
        While Not rdoRS.EOF
          Grid1.Rows = intCurrentRow + 1
          For intCurrentColumn = 0 To intColumnCount - 1
            Grid1.Row = intCurrentRow
            Grid1.Col = intCurrentColumn
            Grid1.Text = rdoRS.rdoColumns(intCurrentColumn).Value & ""
          Next intCurrentColumn
          rdoRS.MoveNext
          intCurrentRow = intCurrentRow + 1
        Wend
      End Sub
      Private Sub Command3_Click()
        Dim HeaderData
        Dim RowData
        Dim intCurrentRow As Integer
        Dim intCurrentColumn As Integer
        Dim intRowCount As Integer
        Dim intColumnCount As Integer
        Dim intLastRow As Integer
        rdoObject.QueryResultset ("SELECT * from authors")
        HeaderData = rdoObject.GetColHeaders
        intColumnCount = UBound(HeaderData, 1)
        Grid1.Rows = 1
        Grid1.Cols = intColumnCount
        Grid1.Row = 0
        For intCurrentColumn = 0 To intColumnCount - 1
          Grid1.Col = intCurrentColumn
          Grid1.ColWidth(intCurrentColumn) = 1250
          Grid1.Text = HeaderData(intCurrentColumn)
        Next intCurrentColumn
        rdoObject.MoveFirst
        While Not rdoObject.EOF
          'experiment with using the GetSomeRows method to limit the
          'amount of data transferred at one time
          RowData = rdoObject.GetAllRows       ' retrieves all rows at once
          'RowData = rdoObject.GetSomeRows(10)   ' retrieves n rows at a time
          intRowCount = UBound(RowData, 2) + 1
          intColumnCount = UBound(RowData, 1) + 1
          intLastRow = Grid1.Row
          For intCurrentRow = 1 To intRowCount
            Grid1.Rows = intLastRow + intCurrentRow + 1
            Grid1.Row = intLastRow + intCurrentRow
            For intCurrentColumn = 0 To intColumnCount - 1
              Grid1.Col = intCurrentColumn
              Grid1.Text = RowData(intCurrentColumn, intCurrentRow - 1) & ""
            Next intCurrentColumn
          Next intCurrentRow
        Wend
      End Sub
   
   						

5. Note that you will need to change your Driver, Server, UID, and PWD in the OpenConnection method contained in the Command1_Click event. You will also need to modify the SQL statement contained in the Command1_Click event to match your own table.
6. From the Tools menu, choose References. You will see rdoServer located at the bottom of the list under Available References because we started the server in the previous instance of Visual Basic. Click on the check box next to it to reference it.
7. Start the program or press the F5 key.
8. You can now click on the each of the three Command buttons to test the different ways to populate the grid.

To take this application a step further, you could add edit/update functionality to your OLE server. This could not be done by using SQL directly from the client side since it does not have a physical connection to the data source. But, this could be done be sending the changed row data back to the OLE server so it could issue an SQL UPDATE statement using the primary key of the table.

OTHER SUGGESTIONS

NOTE: Although it is possible to pass back a pointer to the rdoResultset object, this method is not recommended since it is very inefficient. Also keep in mind that you cannot assign a pointer from an out of process remote rdoResultset to the Resultset property of the Remote Data Control (RDC). This is because notifications for IConnectionPoint are not marshaled by OLE so setting the MSRDC.Resultset = to a rdoResultset that is passed in from an out of process server will fail at some point during initialization.

This will work if an in-process OLE server provides the ResultSet, because the notifications do not need to be marshaled since we are in the same address space.