Visual Basic: RDO Data Control

  • Article

rdoResultset Object

See Also    Example    Properties    Methods    Events

An rdoResultset object represents the rows that result from running a query




Remarks

When you use remote data objects, you interact with data almost entirely using rdoResultset objects. rdoResultset objects are created using the RemoteData control, or the OpenResultset method of the rdoQuery, rdoTable, or rdoConnection object.

When you execute a query that contains one or more SQL SELECT statements, the data source returns zero or more rows in an rdoResultset object. All rdoResultset objects are constructed using rows and columns.

A single rdoResultset can contain zero or any number of result sets so-called "multiple" result sets. Once you have completed processing the first result set in an rdoResultset object, use the MoreResults method to discard the current rdoResultset rows and activate the next rdoResultset. You can process individual rows of the new result set just as you processed the first rdoResultset. You can repeat this until the MoreResults method returns False.

A new rdoResultset is automatically added to the rdoResultsets collection when you open the object, and it's automatically removed when you close it.

Note   RDO 1.0 collections behave differently than Data Access Object (DAO) collections. When you Set a variable containing a reference to a RDO object like rdoResultset, the existing rdoResultset is not closed and removed from the rdoResultsets collection. The existing object remains open and a member of its respective collection.

In contrast, RDO 2.0 collections do not behave in this manner. When you use the Set statement to assign a variable containing a reference to an RDO object, the existing object is closed and removed from the associated collection. This change is designed to make RDO more compatible with DAO.

Processing Multiple Result Sets

When you execute a query that contains more than one SELECT statement, you must use the MoreResults method to discard the current rdoResultset rows and activate each subsequent rdoResultset. Each of the rdoResultset rows must be processed or discarded before you can process subsequent result sets. To process result set rows, use the Move methods to position to individual rows, or the MoveLast method to position to the last row of the rdoResultset. You can use the Cancel or Close methods against rdoResultset objects that have not been fully processed.

Choosing a Cursor Type

You can choose the type of rdoResultset object you want to create using the type argument of the OpenResultset method the default Type is rdOpenForwardOnly for RDO and rdOpenKeyset for the RemoteData control. If you specify rdUseNone as the CursorDriver property, a forward-only, read-only result set is created. Each type of rdoResultset can contain columns from one or more tables in a database.

There are four types of rdoResultset objects based on the type of cursor that is created to access the data:

  • Forward-only type rdoResultset individual rows in the result set can be accessed and can be updatable (when using server-side cursors), but the current row pointer can only be moved toward the end of the rdoResultset using the MoveNext method no other method is supported.

  • Static-type rdoResultset a static copy of a set of rows that you can use to find data or generate reports. Static cursors might be updatable when using either the ODBC cursor library or server-side cursors, depending on which drivers are supported and whether the source data can be updated.

  • Keyset-type rdoResultset the result of a query that can have updatable rows. Movement within the keyset is unrestricted. A keyset-type rdoResultset is a dynamic set of rows that you can use to add, change, or delete rows from an underlying database table or tables. Membership of a keyset rdoResultset is fixed.

  • Dynamic-type rdoResultset the result of a query that can have updatable rows. A dynamic-type rdoResultset is a dynamic set of rows that you can use to add, change, or delete rows from an underlying database table or tables. Membership of a dynamic-type rdoResultset is not fixed.

Dissociate rdoResultset objects

When using the client batch cursor library, RDO permits you to disconnect an rdoResultset object from the rdoConnection object used to populate its rows by setting the ActiveConnection property to Nothing. While dissociated, the rdoResultset object becomes a temporary static snapshot of a local cursor. It can be updated, new rows can be added and rows can be removed from this rdoResultset. You can re-associate the rdoResultset by setting the ActiveConnection property to another (or the same) rdoConnection object. Once reconnected, you can use the BatchUpdate method to synchronize the rdoResultset with a remote database.

To perform this type of dissociated update operation, you should open the rdoResultset using an rdOpenStatic cursor, and use the rdConcurBatch as the concurrency option.

Managing rdoResultset Object Properties and Methods

You can use the methods and properties of the rdoResultset object to manipulate data and navigate the rows of a result set. For example, you can:

  • Use the Type property to indicate the type of rdoResultset created, and the Updatable property indicates whether or not you can change the object's rows.

  • Use the BOF and EOF properties to see if the current row pointer is positioned beyond either end of the rdoResultset or it contains no rows.

  • Use the MoveNext method to reposition the current row in forward-only type rdoResultset objects.

  • Use the Bookmarkable, Transactions, and Restartable properties to determine if the rdoResultset supports bookmarks or transactions, or can be restarted.

  • Use the LockEdits property to determine the type of locking used to update the rdoResultset.

  • Use the RowCount property to determine how many rows in the rdoResultset are available. If the RowCount property returns -1, RDO cannot determine how many rows have been processed. Only when you move to EOF does the RowCount property reflect the number of rows returned by the query. Not all cursor types support this functionality. The RowCount property returns -1 if it is not available.

  • Use the AddNew, Edit, Update, and Delete methods to add new rows or otherwise modify updatable rdoResultset objects. Use the CancelUpdate method to cancel pending edits.

  • Use the Requery method to restart the query used to create an rdoResultset object. This method can be used to re-execute a parameterized query.

  • Use the MoreResults method to complete processing of the current rdoResultset and begin processing the next result set generated from a query. Use the Cancel method to terminate processing of all pending queries when the query contains more than one SQL operation. When you use the Close method against an rdoResultset, all pending queries are flushed and the rdoResultset is automatically dropped from the rdoResultsets collection.

  • Use the Close method to terminate and deallocate the rdoResultset object and remove it from the rdoResultsets collection.

rdoResultset Events

The following events are fired as the rdoResultset object is manipulated. These can be used to micro-manage result sets or to synchronize other processes with the operations performed on the rdoResultset object.

Event Name Description
Associate Fired after a new connection is associated with the object.
ResultsChange Fired after current rowset is changed (multiple result sets).
Dissociate Fired after the connection is set to nothing.
QueryComplete Fired after a query has completed.
RowStatusChange Fired after the state of the current row has changed (edit, delete, insert).
RowCurrencyChange Fired after the current row pointer is repositioned.
WillAssociate Fired before a new connection is associated with the object.
WillDissociate Fired before the connection is set to nothing.
WillUpdateRows Fired before an update to the server occurs.

Executing Multiple Operations on a Connection

If there is an unpopulated rdoResultset pending on a data source that can only support a single operation on an rdoConnection object, you cannot create additional rdoQuery or rdoResultset objects**,** or use the Refresh method on the rdoTable object until the rdoResultset is flushed, closed, or fully populated. For example, when using SQL Server 4.2 as a data source, you cannot create an additional rdoResultset object until you move to the last row of the last result set of the current rdoResultset object. To populate the result set, use the MoreResults method to move through all pending result sets, or use the Cancel or Close method on the rdoResultset to flush all pending result sets.

Handing Beginning and End of File Conditions

When you create an rdoResultset, the current row is positioned to the first row if there are any rows. If there are no rows, the RowCount property setting is 0, and the BOF and EOF property settings are both True.

Note   An rdoResultset may not be updatable even if you request an updatable rdoResultset. If the underlying database, table, or column isn't updatable, or if your user does not have update permission, all or portions of your rdoResultset may be read-only. Examine the rdoConnection, rdoResultset, and rdoColumn objects' Updatable property to determine if your code can change the rows.

Closing rdoResultset objects

Use the Close method to remove an rdoResultset object from the rdoResultsets collection, disassociate it from its connection, and free all associated resources. No events are fired when you use the Close method.

Setting the ActiveConnection property to Nothing removes the rdoResultset object from the rdoResultsets collection and fires events, but does not deallocate the object resources. Setting the rdoResultset object's ActiveConnection property to a valid rdoConnection object causes the rdoResultset object to be re-appended to the rdoResultsets collection of the rdoConnection object.

Addressing rdoResultset Objects

The default collection of an rdoResultset is the rdoColumns collection, and the default property of an rdoColumn object is the Value property. You can simplify your code by taking advantage of these defaults. For example, the following lines of code all set the value of the PubID column in the current row of an rdoResultset:

MyRs.rdoColumns("PubID").Value = 99
MyRs("PubID") = 99
MyRs!PubID = 99
' This is the first column 
' returned by the SELECT statement...
MyRs(0) = 99

The Name property of an rdoResultset object contains the first 255 characters of the query used to create the resultset, so it is often unsuitable as an index into the rdoResultsets collection especially since several queries might be created with the same SQL query.

You can refer to rdoResultset objects by their position in the rdoResultsets collection using this syntax (where n is the nth member of the zero-based rdoResultsets collection):

rdoResultsets(n)