Visual Basic Concepts
Any SQL statement can include multiple SELECT statements or stored procedures that invoke one or more SELECT statements. Each SELECT statement generates a result set that must be processed by your code or discarded before the RDO resources are released and the next result set is made available.
Action queries also generate row-less result sets that must also be processed — this is another type of multiple result set query. In many cases, when you execute a stored procedure, it might return more than one result set. It is often difficult to determine if a stored procedure will return more than one result set because a stored procedure might call another procedure.
For example, if you submit a query that includes four SELECT queries to populate four local ListBox controls and a stored procedure that updates a table, your code must deal with at least five result sets. Because you might not know how many result sets can be generated by a stored procedure, your code must be prepared to process n sets of results.
There are two approaches to executing queries with multiple result sets:
Both are processed in similar ways, but if you use the rdoQuery, you can examine the RowsAffected property to determine the number of rows affected by action queries. While it is possible to execute a multiple result set query using the Execute method, it is not possible to retrieve the rows affected from individual statements, and a trappable error results if any of the queries returns rows.
Not all cursor drivers support the ability to process queries that contain more than one set of results — the SQL Server server-side cursor driver is an example. However, if you request a cursorless result set by using the rdOpenForwardOnly, rdConcurReadOnly options and by setting the RowsetSize property to 1, you can execute queries with multiple result sets using server-side cursors. You can also set these options for all result sets by setting the CursorDriver property to rdUseNone.
This section takes you through a step-by-step procedure that demonstrates how to execute a query with multiple result sets by using the rdoQuery object.
Tip Whenever you use the concatenation operator "&" to build SQL queries, be sure to include white space (such as spaces or tabs) between the operators on separate lines.
MySQL
. For SQL Server, multiple statements must be separated by semi-colons.
Dim MySQL As String
MySQL = "Select Name from Authors Where ID = 5; " _
& " Select City from Publishers; " _
& " Update MyTable " _
& " Set Age = 18 Where Name = 'Fred'"
MyQy
. The example assumes an rdoConnection object (Cn
) already exists. There are a number of other ways to instantiate and initialize rdoQuery objects; this example illustrates only one of these ways.
Dim MyQy As rdoQuery
Set MyQy = Cn.CreateQuery("MyQy1", "")
MyQy.SQL = MySQL
Dim MyRs As rdoResultset
Set MyRs = MyQy.OpenResultset(rdOpenForwardOnly, _
rdConcurReadOnly)
The following example fills a ListBox control called NameList1
with the results of the query.
While Not MyRs.EOF ' Loop through all rows.
' Use the first column.
NameList1.AddItem = MyRs(0)
MyRs.MoveNext ' Position to the next row
' in the result set.
Wend
MyRs.EOF
= True). Use the MoreResults method to activate the next result set. Once you execute MoreResults, the first set of rows is no longer available — even if you used one of the cursor options to create it.
' Activate the next set of results.
If (MyRs.MoreResults) Then ...
' Loop through some rows.
Do While Not MyRs.EOF and MyRs(0) < "B"
' Use the first column.
NameList1.AddItem = MyRs(0)
MyRs.MoveNext
Loop
' Activate the next set of results
' and discard remaining rows.
If (MyRs.MoreResults) Then ...
If MyQy.RowsAffected = 0 Then
MsgBox "No rows were updated"
End If
' Activate the next set of results.
If (MyRs.MoreResults) Then ...
When you use the MoreResults method against the last result set, it should return False and other resources required to process the query are released. At this point the rdoQuery object can be reused. If you use the Close method against an rdoQuery object, it is removed from the rdoQueries collection.