Image of Navigational Map linked to Home / Contents / Search Processing Multiple Result Sets in VB 5

by Lisa Hooper - GUI Computing
Image of Line Break

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.



Written by: Lisa Hooper
March '97

Image of Arrow linked to Previous Article Image of Arrow linked to Next Article
Image of Line Break
[HOME] [TABLE OF CONTENTS] [SEARCH]