DOCUMENT:Q143034 06-FEB-2002 [vbwin] TITLE :How To Use GetRows to Pass Recordset Data Back from OLE Server PRODUCT :Microsoft Visual Basic for Windows PROD/VER::4.0 OPER/SYS: KEYWORDS:kbDatabase kbODBC ====================================================================== ------------------------------------------------------------------------------- The information in this article applies to: - Microsoft Visual Basic Professional Edition, 32-bit, for Windows, version 4.0 - Microsoft Visual Basic Enterprise Edition, 32-bit, for Windows, version 4.0 ------------------------------------------------------------------------------- SUMMARY ======= When implementing an OLE server to process your data requests for an OLE client, you can use the GetRows method to send the data back to the client from the server. This article demonstrates this using RDO; the term Resultset is used instead of Recordset, but you could easily modify the code to use the DAO Recordset object. The sample application will let you build an OLE client that retrieves the rows from an rdoResultset in three different ways: - The first way will use traditional RDO controlled completely on the client side with no OLE interaction to populate the grid. This is done for a performance comparison only. - The second will allow the rdoResultset to be created by the OLE server but will receive a pointer to the rdoResultset object, which it will reference to populate the grid. This method is very slow and only works reliably for an in process OLE server. - The third way will allow the rdoResultset to be created remotely and receive an array with the GetRows method that contains the row data to populate the grid. This is the recommended way to implement three tier client server architecture. 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 : Q142534 : How to Create Programs in Visual Basic that Use Remote OLE 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. Additional query words: 4.00 vb4win vb432 ====================================================================== Keywords : kbDatabase kbODBC Technology : kbVBSearch kbAudDeveloper kbVB400Search kbVB400 Version : :4.0 ============================================================================= THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY. Copyright Microsoft Corporation 2002.