by Lisa Hooper - GUI Computing
Before VB 5 the only way to process multiple result sets was to use direct ODBC API calls such as SQLMoreResults. This meant that for those not wanting to involve themselves in ODBC API programming there was no way to establish whether the end of file really was the intended end of results, as only the first resultset would be available through VB. In complex systems where stored procedures are calling other procedures it can be difficult to determine the number of result sets a stored procedure will return. VB 5's RDO 2 makes processing multiple result sets relatively simple.
Just as you can move through records within a recordset, you can also move through recordsets within an rdoResultset. To process multiple result sets you must use the OpenResultset method against either an rdoConnection object or an rdoQuery object. To move through each successive recordset use the MoreResults method of the rdoResultset object. Remember that as you move through the recordsets any previous recordset is no longer available. After using the MoreResults method against the last resultset, it should return false and resources will be released.
The following code uses an RDO connection to access the Pubs database residing on an SQL Server. It uses an RDO resultset to return the results of two queries one of which contains a list of all titles under the category of business and the second which contains a total sold figure for the above titles:
Private Sub cmdRunSales_Click() Dim Cn As New rdoConnection Dim Rs As rdoResultset Dim SQL As String 'Establish a rdo connection to our Sql Server Pubs database With Cn .Connect = "DSN=PubsData;UID=sa;PWD=;Database=Pubs;" .LoginTimeout = 5 .CursorDriver = rdUseOdbc .EstablishConnection rdDriverNoPrompt 'all login info is supplied End With SQL = "select titles.title from titles where type = 'business' order by title " _ & "select sum(qty) TotalSales from sales, titles where titles.title_id = sales.title_id and titles.type = 'business'" 'Note the Sql sent to the server contains two sql statements 'The first contains a list of all titles under the category of business 'The second contains a total sold figure for the above titles 'Open a forward scrolling read-only recordset Set Rs = Cn.OpenResultset(SQL, rdOpenForwardOnly, rdConcurReadOnly) 'Loop through the first recordset and place contents in list box Do Until Rs.EOF lstBooks.AddItem Rs!Title Rs.MoveNext Loop 'More results will return true if another recordset is ready to be processed If Rs.MoreResults Then lstBooks.AddItem "Total sales: " & Rs!TotalSales End If Rs.Close End Sub
This method of returning results from an ODBC data source can be particularly advantageous when working over a slow link. Bundling a number of requests into one means a reduction in round trips to the server and over a slow link this could provide a significant performance enhancement. However, you should be aware that when you are not dealing with a slow link the performance difference between opening a multi resultset and opening separate recordsets for each request is so minimal that it may not be worth the sacrifice of code readability.