Make your own free website on Tripod.com
  Visual Studio Web Site Visual C++ Web Site Visual FoxPro Web Site Visual InterDev Web Site Visual J++ Web Site
Microsoft HomeProductsSearchSupportShopWrite Us Microsoft Home
 
 Visual Basic Home
 Product Information
 Technical Resources
 Samples & Downloads
 Developer Community
 News & Reviews
 Registration
 Site Map
 Search Developer Sites:
 
 

Break-out the business logic in your multi-tier apps. Our updated sample application will show you how!
Download this document

Technical Articles

Remote Server Connectivity


Looking at Remote Data Objects and the RemoteData Control

William R. Vaughn
Microsoft Corporation
February 1996

Abstract

More and more Visual Basic developers are creating front-end applications to remote database servers. In previous versions of Visual Basic, the number of options available were limited to either Jet Data Access Objects or an API-based interface like VBSQL. Visual Basic Version 4.0 Enterprise Edition now includes an entirely new way to access data in intelligent servers like Microsoft SQL Server - the Remote Data Objects. Remote Data Objects implement a set of objects to deal with the special requirements of remote data access. RDO implements a thin code layer over the ODBC API and driver manager that establishes connections, creates result sets and cursors, and executes complex procedures using minimal workstation resources. Because bound controls sometimes play an important role in these applications, the Enterprise Edition also includes the RemoteData control that takes on the same functionality as the Data control.

This paper provides an overview of the features of the Remote Data Objects (RDO) programming model and the RemoteData control. This information can help you make a decision regarding which data access programming model to use in your client/server application.

Contents

Introduction
Using Remote Data Objects
Using the RemoteData Control

Introduction

With RDO and the RemoteData control, your applications can access ODBC data sources without using a local query processor. This can mean significantly higher performance and more flexibility when accessing remote database engines. Although you can access any ODBC data source with RDO and the RemoteData control, these features are designed to take advantage of database servers, like Microsoft SQL Server and Oracle, that use sophisticated query engines.

By using RDO, you can create simple cursor-less result sets, or more complex cursors - even using SQL Server "server-side" cursors. You can also run queries that return any number of result sets, or execute stored procedures that return result sets with or without output parameters and return values. You can limit the number of rows returned or updated and monitor all of the messages and errors generated by the remote data source without compromising the executing query. RDO also permits either synchronous or asynchronous operation so your application doesn't need to be blocked while lengthy queries are executed.

Note  RDO and the RemoteData control are features of the Visual Basic Version 4.0, Enterprise Edition. You cannot develop code or use the RDO object library or RemoteData control in the Professional or Standard Editions of Visual Basic.

Client/Server Design Goals
RDO and the RemoteData control can help you meet a specific set of client/server requirements. By using these remote data access features, you can:
  • Gain high-performance data access against remote ODBC data sources. The ability to quickly retrieve the results from complex queries is a goal of every data access application. RDO provides a level of performance rivaled only by the ODBC API and VBSQL API programming models. By leveraging the remote data engine, RDO greatly improves response time and user productivity.
  • Manage return codes and both input and output parameters from stored procedures. Output parameters are the only way to extract information from an Oracle stored procedure, and are used heavily for singleton queries and many administrative functions. In many cases, you cannot determine if a stored procedure completed successfully without accessing the procedure's return value. RDO supports access to each of these parameters through the rdoParameter object.
  • Manage multiple result sets. By using a single query that returns several sets of related results, you can use the query processor and system resources more efficiently. You can improve performance by running a single query to gather data to fill multiple data-driven list boxes and menus. In addition, by combining a row-count query with a SELECT query, you can accurately set up scroll bars and progress status bars.
  • Limit the number of returned rows. In situations where users might select more rows than it is practical to handle, RDO implements a query governor to limit the number of rows returned from any data source. This way, you can predict query response time and more easily manage the workstation or server resources required to maintain cursor keysets.
  • Pre-set the fatal error threshold. Data sources generate a variety of informational and low-severity messages in addition to high-severity messages and error notifications. It is important to differentiate between errors considered fatal that should cause the query to be abandoned, and errors that should be reported, but not considered fatal. With RDO, you can set an error threshold that sets the severity of error that constitutes a fatal error.
  • Utilize server-side cursors. Some servers, such as Microsoft SQL Server, support cursor keysets that are created on the server, instead of on the workstation. Under the right conditions, this type of cursor management can significantly improve performance. You can choose to use server-side cursors when they are available.
  • Execute queries asynchronously. If a query takes an extended period of time to run, you should have the option of either executing code while the query is being processed or canceling the query in progress. RDO provides an asynchronous query option that you can use when executing any query, as well as a way to cancel an asynchronous query.
  • Expose underlying ODBC handles. In cases where you need more flexibility or control than is available in the object model, you should have a way to directly access the data source. RDO provides access to the ODBC environment, connection, and statement handles.
  • Reduce memory footprint. In many cases, the system that hosts a client/server front-end application is limited in RAM capacity. Because of this, it is important that applications designed for these types of systems economize on their use of RAM and other workstation system resources. The RDO memory footprint is dramatically smaller than other programming models, and it does not require the use of local memory or disk space for its lowest-level cursors.
RDO Compared to Microsoft Jet
Basically, you can use the Remote Data Objects pretty much like the way you use the Microsoft Jet database engine Data Access Objects (DAO), and the RemoteData control is similar to the Data control. With RDO, you can submit queries, create a result set or cursor, and process the results from the query using database-independent object-oriented code. Using the RemoteData control, you can create a form containing the same bound controls recognized by the Data control, and process a result set with little or no code. The following table lists RDO objects and their equivalent DAO objects.

RDO object Equivalent DAO object
rdoEngine DBEngine
Not implemented User, Group
rdoEnvironment Workspace
rdoConnection Database
rdoTable TableDef
Not Implemented Index
rdoResultset Recordset
Not implemented Table-type
Keyset-type Dynaset-type
Static-type Snapshot-type
Dynamic-type (none)
Forward-only - type Similar to Forward-Only Snapshot
rdoColumn Field
rdoPreparedStatement QueryDef
rdoParameter Parameter

RDO refers to table rows instead of records and columns instead of fields -- the generally accepted terminology for relational databases. The data returned from a query is in the form of result sets, which can contain zero or more data rows composed of one or more columns.

Some DAO objects, methods, and properties are designed to implement and support the ISAM structure of Jet and installable ISAM databases. For example, you can use the Index object and the Seek method to manage ISAM indexes and locate rows based on those indexes. Because the RDO and relational databases manage indexes in an entirely different manner, these objects and methods are not needed.

Jet also supports the creation and modification of the database schema through DAO methods and properties. RDO does not support any type of schema modification because this is fully supported in the tools and utilities provided with the server systems. You can still run make-table queries or execute action queries that create, modify, or delete databases and tables using native SQL statements. You can also execute complex stored procedures that manage the database schema or perform maintenance operations that are not possible with DAO.

Using Remote Data Objects

In most respects, Remote Data Objects are used like Jet Data Access Objects. This means that for the most part you can take your existing applications that use DAO and the Data control and convert them to use RDO and the RemoteData control with a minimum number of changes. There are some differences, however, because of the way RDO is implemented and designed for use in relational databases. RDO has no query processor; it depends on the data source to process all queries and create the result sets. The data objects themselves are built from the result sets and cursors returned by the ODBC driver.

You can use the RemoteData control to create RDO objects, just as you can use the Data control to create Jet DAO objects. You can also create result sets using RDO methods and pass these to the RemoteData control for management and editing by associated bound controls.

Understanding the RDO Object Model
RDO objects and collections provide a framework for using code to create and manipulate components of a remote ODBC database system. Objects and collections have properties that describe the characteristics of database components and methods that you use to manipulate them. Using the containment framework, you create relationships among objects and collections, and these relationships represent the logical structure of your database system.

With the exception of the rdoEngine object, each of these objects is maintained in an associated collection. When RDO is initialized on first access, RDO automatically creates an instance of the rdoEngine and the default rdoEnvironments(0).

Note  RDO is only supported on 32-bit operating systems (Windows 95 and Windows NT). To use the Remote Data Objects, you must set a reference to the Microsoft Remote Data Object 1.0 object library in the Visual Basic References dialog box.

To establish a physical link to an ODBC data source and a specific database, your code must create an rdoConnection object. You can define the data source by using the rdoRegisterDataSource method in code or by using the ODBC Data Sources settings in the Windows Control Panel. The database associated with the rdoConnection is determined by connect string arguments or the default database assigned to the user.

Basically, there are two approaches to executing queries and retrieving result sets:

  • Use queries that are run only once. In this case, use the OpenResultset or Execute method to execute fixed SQL queries against the rdoConnection object to create rdoResultset objects or execute action queries.
  • Use queries that are run more than once and that might require parameters. Use the CreatePreparedStatement method to create an rdoPreparedStatement object that can be reused as needed, and that allows changes to one or more parameters each time the query is executed. Once the rdoPreparedStatement is created, use the OpenResultset or Execute method against the rdoPreparedStatement to create an rdoResultset or execute an action query. To change the query parameters, change the rdoParameter object settings.
Using arguments of the OpenResultset method or properties of the rdoPreparedStatement object, you can specify the type of cursor (if any) and other attributes of the rdoResultset object.

You can use the rdoTable object to map the tables and columns of a data source or create rdoResultset objects from all rows in the table -- which is not recommended with RDO. There are very few cases where it is necessary to retrieve all rows from a database table into workstation memory. Many remote database tables are far too large to be downloaded to the workstation.

To reposition the current row pointer in an rdoResultset, use the Move methods, or the AbsolutePosition and PercentPosition properties. You can also save and use bookmarks to position to selected locations in an rdoResultset. To locate a value in an rdoResultset, refine the WHERE clause, and resubmit the query.

The rdoEngine Object
The rdoEngine object represents the remote data source and is created automatically when you make any reference to RDO or the RemoteData control. As the top-level object, it contains all other objects in the hierarchy of Remote Data Objects. The rdoEngine object is a predefined object, therefore you can't create additional rdoEngine objects, and it isn't an element of any collection. You can use the rdoEngine to set data source parameters and the default rdoEnvironment.

Even though the rdoEngine is shared among applications that use it, the rdoEngine default properties are not shared between multiple applications that are using it. Each instance of your application is provided with its own set of default values that have no affect on other applications that also use RDO or the RemoteData control.

Note  Adding the RemoteData control to your Toolbox does not automatically set a reference to the Microsoft Remote Data Object 1.0 object library. To use the rdoEngine and Remote Data Objects, you must set a reference to the Microsoft Remote Data Object 1.0 object library in the References dialog box (available from the Tools menu); otherwise, you will get compilation errors.

For More Information
Search Visual Basic Books Online or online Help for rdoEngine object.
Initializing rdoEngine Properties
When you create new rdoEnvironment, rdoConnection, or rdoResultset objects, the characteristics of the new objects are determined by the arguments in the rdoCreateEnvironment or OpenResultset method or by the default values in the default properties of the rdoEngine. These properties are listed in the following table.

Property Specifies Default
rdoDefaultCursorDriver Cursor location (ODBC or server-side) rdUseIfNeeded
rdoDefaultPassword User password "" (empty string)
rdoDefaultUser User name "" (empty string)
rdoDefaultErrorThreshold Error severity above which errors are fatal -1 (disabled)
rdoDefaultLoginTimeout Time to wait before abandoning connection attempt 15 seconds

You can change any of these default value properties before creating new rdoEnvironment, rdoConnection, or rdoResultset objects. However, rdoEnvironments(0) is created using the defaults shown in the preceding table. This is because it is created automatically on the first reference to RDO or the RemoteData control. If the default values are not appropriate for your application, you can change the properties of rdoEnvironments(0) or the rdoEngine before opening a connection.

Registering a Data Source
All RDO operations require that you first connect to a suitable ODBC data source. A data source can be any database system or file that is supported with an ODBC driver. You can access any ODBC data source with RDO, however, a number of optimizations have been made to support specific features of Microsoft SQL Server and Oracle databases. Because of this, some features provided by RDO may not be suitable for use with ISAM or other flat-file data sources.

Before you can access a data source, you must first use the ODBC Data Sources dialog box in the Windows Control Panel to create a data source entry in the Windows Registry. You can also use the rdoRegisterDataSource method to register a new data source. Registering a data source is a one-time operation that should take place when the application is first installed with Setup.

Creating an rdoEnvironment
If your application expects to support more than one transaction scope, or separate user name and password contexts, you should use the rdoCreateEnvironment method to create a new rdoEnvironment object with specific user name and password values. This method accepts a unique name, a user name, and password. If the name you choose matches the name of an existing member of the rdoEnvironments collection, a trappable error results.

The default rdoEnvironments(0) is created automatically when the RemoteData control is initialized, or when the first RDO object is referenced in code. The Name property of rdoEnvironments(0) is "Default_Environment". The user name and password for rdoEnvironments(0) are both zero-length strings ("").

Newly created rdoEnvironment objects are automatically appended to the rdoEnvironments collection if you provide a unique name. You can also use a zero-length string for the name argument of the rdoCreateEnvironment method. In this case, the new rdoEnvironment is not appended to the rdoEnvironments collection.

Logon Information
The user name and password information from the rdoEnvironment is used to establish the connection if these values are not supplied in the connect argument of the OpenConnection method, or in the Connect property of the RemoteData control. For example, the default user name (Fred) and password (Blond) can be used to establish a connection in the En environment:

Dim En As rdoEnvironment
Set En = rdoCreateEnvironment("", "Fred", "Blond")

An rdoEnvironment object logically corresponds to an ODBC environment. You can refer to an rdoEnvironment object by using the ODBC API functions, accessible through the rdoEnvironment object's hEnv property. However, because ODBC only allows one environment handle per application, the actual lifetime of the ODBC environment handle is tied to the lifetime of the rdoEngine. Actual ODBC connections only take place when an rdoConnection object is opened.
Transaction Scope
The rdoEnvironment also determines transaction scope. Committing an rdoEnvironment transaction commits all open rdoConnection databases with pending transactions (which are opened on that rdoEnvironment object, and their corresponding open rdoResultset objects). This does not imply a two-phase commit operation -- it simply means that individual rdoConnection objects are instructed to commit any pending transactions.

Use the rdoEnvironment object to start an additional connection. In an rdoEnvironment, you can open multiple databases, manage transactions, and establish security based on user names and passwords. For example, you can:

  • Create an rdoEnvironment object using the Name, Password, and UserName properties to establish a named, password-protected environment. The environment creates a scope in which you can open multiple connections and conduct one instance of ODBC transactions.
  • Use the OpenConnection method to open one or more existing connections in that rdoEnvironment.
  • Use the BeginTrans, CommitTrans, and RollbackTrans methods to manage ODBC transaction processing within an rdoEnvironment.
  • Use several rdoEnvironment objects to conduct multiple, simultaneous, independent, and overlapping transactions.
  • Use the Close method to terminate an environment and the connection.
Note  The ODBC transaction model does not support nested transactions. That is, you cannot execute a second BeginTrans method before the previous transaction is either committed or rolled back. However, if your ODBC data source supports it, you can use SQL statements to execute nested transactions.

When you use ODBC transactions, all databases in the specified rdoEnvironment are affected -- even if multiple rdoConnection objects are opened in the rdoEnvironment. For example, if you use a BeginTrans method against one of the databases visible from the connection, you should update several rows in the database, and then delete rows in another rdoConnection database. When you use the RollbackTrans method, both the update and delete operations are rolled back. You can create additional rdoEnvironment objects to manage transactions independently across rdoConnection objects. Transactions executed by multiple rdoEnvironment objects are serialized and are not atomic operations. Because of this, their success or failure is not interdependent.

Establishing a Connection
Once a data source is identified, you use the OpenConnection method to create an rdoConnection object, with which you establish a physical link to the data source. To establish a connection, the data source entry must identify the network location of the data source, as well as the driver type. Your code provides a number of additional parameters used to log the user onto the data source. By choosing an appropriate prompt argument, you can program the ODBC driver manager to prompt the user for missing arguments and prevent the use of alternate arguments. The OpenConnection method accepts the following arguments:
  • The dsName argument indicates the name of the registered data source. You can reference the new rdoConnection object using the data source name (DSN) or the rdoConnection's ordinal position in the rdoConnections collection.
  • The prompt argument indicates if the user can supply arguments to ODBC connect dialogs. If you do not want the user to supply a different data source name, user name, password, or default database, use the rdDriverNoPrompt constant as the prompt argument.
  • The readonly argument is set to False if the user expects to update data through the connection.
  • The connect string supplies parameters to the ODBC driver manager. These parameters can include user name, password, default database, and DSN (which overrides the value provided in the dsName argument). Different ODBC data sources require different parameters.
For example, you can use the following code to establish a connection to a Microsoft SQL Server database with an existing data source name of MyRemote:

Dim Cn As rdoConnection, En as rdoEnvironment, Conn As String
Set En = rdoEnvironments(0)
Conn$ = "DSN=MyRemote;UID=Holly;PWD=Huskador;DATABASE=MyDb;"
Set Cn = En.OpenConnection("", rdDriverPrompt, False, Conn$)

The dsName argument of the OpenConnection method is an empty string. In this case, the data source name is taken from the connect argument.

Note  Each of the RDO methods supports named arguments, so it is possible to specify each argument of the method using the argument:= syntax. For example, the previous OpenConnection method could be coded:


Set Cn = En.OpenConnection(prompt:=rdDriverPrompt, _
  readonly:=False,Connect:=Conn$)

If you choose to use domain-managed security, you should use empty arguments for the UID and PWD parameters of the connect argument. This type of security passes your Windows NT logon ID and password to the data source. If your database administrator has implemented integrated or mixed security, this technique should permit you to log on to the data source -- assuming you have been granted permission to do so. For example, using the preceding sample code, a domain-managed security connect argument is coded as follows:
Conn$ = "DSN=MyRemote;UID=;PWD=;DATABASE=MyDb;"

You might be unable to establish a connection for a variety of reasons, including lack of permission on the data source, improper network connection or permissions, or a missing or disabled data source. When connecting to Microsoft SQL Server, Oracle, or other data sources, the number of simultaneous connections permitted might be limited by license agreements, resource constraints, or by database settings. Check with your server administrator if you suspect that all available connections are allocated.

Once the rdoConnection object is created, you can use it to:

  • Create rdoResultset or rdoPreparedStatement objects using the OpenResultset or CreatePreparedStatement method, respectively.
  • Examine database tables and the columns of each table by using the rdoTable and rdoColumn objects in the rdoConnections(0).rdoTables and rdoTables(n).rdoColumns collection.
  • Start, commit, or roll back transactions using the BeginTrans, CommitTrans, and RollbackTrans methods.
  • Disconnect from the data source and free its resources using the Close method.
When using remote data objects against a SQL Server that has case-sensitivity enabled, if your table name contains any upper-case letters the Update method will fail with an 'invalid object <table name>' error. This will only occur when using the ODBC Cursor Library against a case-sensitive SQL Server. To work around this problem you have several options:
  • Remove case-sensitivity from the SQL Server
  • Make your table names all lower-case
  • Use server-side cursors

If the above options are not available, you can issue update SQL statements to the server using the Execute method.

This does not affect Oracle servers.

Submitting a Query
To extract data from the data source, use the OpenResultset method against an rdoConnection object. You can also create rdoResultset objects by using parameterized queries with rdoPreparedStatement.OpenResultset.

When you use the OpenResultset method against an rdoConnection or rdoPreparedStatement, and assign the result to an existing rdoResultset object, the existing object is maintained and a new rdoResultset object is appended to the rdoResultsets collection. When performing similar operations using the Microsoft Jet database engine, existing recordsets are automatically closed when the variable is assigned, and no two Recordsets collection members can have the same name. For example, using RDO:


Dim rs as rdoResultset
Dim cn as rdoConnection

Set cn = OpenConnection....
Set rs = cn.OpenResultset("Select * from Authors", rdOpenStatic)
Set rs = cn.OpenResultset("Select * from Titles", rdOpenDynamic)
This code opens two separate rdoResultset objects; both are stored in the rdoResultsets collection. After this code runs the second query, which is stored in rdoResultsets(1), is assigned to the rdoResultset variable rs. The first query is available and its cursor is still available by referencing rdoResultsets(0). Because of this implementation, more than one member of the rdoResultsets collection can have the same name.

This behavior permits you to maintain existing rdoResultset objects, which are maintained in the rdoResultsets collection, or close them as needed. In other words, you must explicitly close any rdoResultset objects that are no longer needed. Simply assigning another rdoResultset to a rdoResultset-type variable has no affect on the existing rdoResultset formerly referenced by the variable. Note that the procedures and other temporary objects created to manage the rdoResultset are maintained on the remote server as long as the rdoResultset remains open.

If you write an application that does not close each rdoResultset before opening additional rdoResultset objects, the number of procedures maintained in TempDB or elsewhere on the server increase each time another rdoResultset object is opened. Over time, this behavior can overflow the capacity of the server or workstation resources.

The OpenResultset method accepts the following arguments when you use it with an rdoConnection object:

  • The source argument specifies an SQL query, the name of an rdoPreparedStatement or rdoTable object, or a stored procedure name preceded by "EXEC".
  • The type argument specifies the type of cursor. If you do not indicate a cursor type, a forward-only - type result set is created. You can also specify keyset, dynamic, or static cursor types.
  • The locktype argument specifies the type of locking used to support concurrency. The default is rdReadOnly.
  • The options argument was enhanced at the last minute. It either specifies if the query should be executed asynchronously rdAsyncEnable or if the ODBC driver manager should create a procedure before executing the query (rdExecDirect). The default is no options enabled. In this case, the query is executed synchronously and the driver manager creates a stored procedure to execute the query.
In some cases, when you use the OpenResultset method and the SQL statement specified contains invalid SQL syntax, the ODBC driver fails with an untrappable GPF. Not all syntax errors cause this. The following SQL query causes this type of GPF:

" Select * from authors where name '%' (?) '%'  "

To avoid this problem, verify all SQL statements for accuracy and correct syntax before using them in your application. In addition, do not permit users to enter SQL statements directly, as these might be prone to failure.
Choosing a Cursor Type
The rdoResultset is returned either as a forward-only - type result set or as one of the three types of cursors: static, keyset, or dynamic -- based on the type available and the capability of the ODBC driver manager and data source driver. Using the CursorDriver property of the rdoEnvironment object, you can also choose where the cursor keyset will be created -- on the workstation or on the server.

Forward-Only Result Sets

In cases where you need to retrieve data quickly with the least amount of overhead, use a forward-only - type rdoResultset. This type of result set can be updatable, but it only exposes one row at a time. No cursor keyset is created, and data values are not updated as they change on the data source. However, because of its efficiency, it might be faster to rebuild a forward-only - type rdoResultset than to build and maintain a keyset-type rdoResultset.

Static Cursors

A static-type rdoResultset is similar to a Jet snapshot-type Recordset object. Static cursor data appears to be static. The membership, order, and values in the result set used by a static cursor are generally fixed when the cursor is opened. Rows updated, deleted, or inserted by other users (including other cursors in the same application) are not detected by the cursor until it is closed and reopened.

Keyset Cursors

A keyset-type rdoResultset is similar to a Jet dynaset-type Recordset object. A key is built and saved for each row in the cursor and stored either on the client workstation or on the server machine. When you access each row, the key is used to fetch the current data values from the data source. In a keyset-driven cursor, membership is frozen once the keyset is fully populated, therefore, additions or updates that affect membership are not made a part of the cursor until it is rebuilt.

On some servers, modifications or additions made directly to keyset cursors using the AddNew and Edit methods are included in the result set, but additions or modifications made using Execute do not affect the cursor. Consult your server manual for details.

To build a keyset-driven cursor, you must provide sufficient resources on the client or server to hold the keys and a block of buffered data rows.

Dynamic Cursors

A dynamic-type rdoResultset is identical to a keyset-driven cursor except that membership is not frozen. Because RDO constantly checks that all qualified rows are included in the membership, this type of cursor carries the largest burden of overhead. However, a dynamic cursor may be faster to initiate than a keyset cursor, because the keyset cursor carries the overhead of building the initial keyset.

When a keyset-type or static-type rdoResultset is first created, the RowCount property is set to the number of rows in the result set if rows are returned, 0 if no rows are returned by the query, or -1 if RowCount is not available. RowCount is not applicable to dynamic cursors where the number of rows can change, or to forward-only result sets that only expose one row.

Sequencing Operations

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 rdoPreparedStatement 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 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.
For More Information
Search Visual Basic Books Online or online Help for rdoResultset object, understanding cursors and OpenResultset method.
Positioning the Current Row Pointer
At any one time, only one row in an rdoResultset is exposed for data retrieval or modification -- the row addressed by the current row pointer. You can move the current row pointer by using the Move methods, or the AbsolutePosition and PercentPosition properties. The rdoResultset object supports bookmarks which can be used to save the current location in a Variant variable. You can subsequently position back to a saved location in the rdoResultset by setting the Bookmark property with a bookmark.

When using forward-only - type rdoResultset objects, you can only use the MoveNext method -- not the MovePrevious method, MoveFirst method, or the Move method with a negative argument.

Note  This is a change from the documentation and help file which says you can use any of the forward-moving methods on a Forward-Only type cursor. The ODBC driver does not support anything but the MoveNext method - no, not even MoveLast.

When positioning the current row pointer, it is possible to position the pointer past either end of the result set or to a row that has been deleted. RDO may also leave the current row pointer positioned over an invalid row -- as when a row is deleted. Be sure to check the EOF and BOF properties to determine if the current row is positioned beyond the end or beginning of the result set. When using the Bookmark property to reposition the current row, it is possible to reposition to a row that has been deleted by another user. If this occurs, a trappable error results.

For More Information
Search Visual Basic Books Online or online Help for BOF property, EOF property, AbsolutePosition property, PercentPosition property, and Move.
Changing Data
You can use the rdoResultset object to change columns in a specific row, add a row, or delete a row. Data columns can be changed if the Updatable property is True for both the rdoResultset and rdoColumn objects.

To change columns in a specific data row in the rdoResultset

  1. Position to the row using one of the Move methods or the AbsolutePosition or PercentPosition property.
  2. Use the Edit method to activate the row to be altered. All data is copied into a temporary row buffer.
  3. Provide a new value for each column that you need to modify.
  4. Use the Update method to save the row buffer to the data source. The current row is deleted and the buffered row replaces it.
If you move the current row pointer to another row before using the Update method, any changes are abandoned. You can also abandon an edit and refresh the current row by using the Move method with a 0 argument or by using the CancelUpdate method. After the edit, the current row pointer is positioned to the row modified -- which might be located at the end of the rdoResultset. To revisit the row that was just changed, use the bookmark provided in the LastModified property.

If you use the BeginTrans method, changes made to the database with the Update method are deferred until you use the CommitTrans method to save the changes, or the RollbackTrans method to discard the changes.

In addition to changing columns in a specific row, you can add a row to an rdoResultset.

To add a row to an rdoResultset

  1. Use the AddNew method to create a row buffer to hold the new row to be added.
  2. Provide a new value for each column you need to modify.
  3. Use the Update method to save the row to the data source.
After the row is added, the current row pointer is positioned on the row that was current before the new row was added. You can use the LastModified bookmark to move to the newly added row at the end of the rdoResultset.

Once you've added rows, you can also delete them from the result set.

To delete a specific row in an rdoResultset

  1. Position to the row using one of the Move methods or the AbsolutePosition or PercentPosition property.
  2. Use the Delete method to delete the row from the cursor (if any) and the data source.
Once a row is deleted, the current row is no longer valid, so you must reposition to another valid row in the rdoResultset.
Managing Concurrency
You can control how the data source manages locking while you make changes to the data by changing the LockType option of the OpenResultset method. The following table describes the four types of locking.

LockType option Description
rdConcurLock Pessimistic concurrency. This technique uses the lowest level of locking sufficient to ensure that the row can be updated. This option locks the row or the data page containing the row(s) as soon as the Edit or AddNew method is executed, and holds it until the Update method has written the changes to the data source.
rdConcurRowver Optimistic concurrency using row versions. The ODBC cursor library and the data source compare row ID or TIMESTAMP values to determine if the row has changed.
rdConcurValues Optimistic concurrency using row values. The ODBC cursor library and the data source compare data values.
rdReadOnly (Default) The cursor is read-only. No updates are permitted.

When values or row versions are compared to test concurrency, the original row as it was prior to the Edit or AddNew method is compared with the row as it exists in the database prior to being changed by the Update method. If the row data changed in this timeframe, a trappable error results. At this point, your temporary row buffer is lost. To carry out the changes, you must refresh the current row using the Move 0 technique, use the Edit or AddNew method, fill in the new values, and repeat the Update method again.

Making Changes with Action Queries
An alternative to using the Edit, AddNew, Delete, and Update methods is to use the Execute method. By executing an SQL query that contains one or more UPDATE, INSERT, or DELETE statements, you can make changes to the database without using the RDO methods. Depending on the type of data source, and its ability to support complex multi-statement operations, these SQL statements can contain logic that performs so-called 'make-table' or SELECT INTO queries that create new permanent or temporary tables, or perform other complex operations. You must manage the errors and concurrency yourself. You can also submit transaction statements that bind the operations into one or more atomic sets using the SQL syntax supported by your data source.

The Execute method is not designed to execute queries that return rows. If you execute a stored procedure that performs a mix of "action" operations and row-returning operations, you must use the OpenResultset method and parse the result sets that are generated.

Using Server-Side Cursors
If your data source supports server-side cursors, the remote database engine builds cursor keysets on the server and extends the SQL dialect to support creation and management of data using cursors. Microsoft SQL Server 6.0 supports server-side cursors, but not all data sources do. Use the rdoDefaultCursorDriver property of the rdoEngine or the CursorDriver property of the rdoEnvironment object to choose the type of cursor driver to use. The following table describes the options available with these properties.

Option Description
rdUseIfNeeded (Default) The ODBC driver chooses the appropriate style of cursor and uses server-side cursors if available.
rdUseODBC RDO will use the ODBC cursor library. This gives better performance for small result sets, but degrades quickly for larger result sets.
rdUseServer Server-side cursors are always used if available.

Server-side cursors reduce the amount of memory and disk space required on the client machines, but shift that load to the server. For SQL Server 6.0, cursor keysets are maintained in the TEMPDB database on the server, so it must be sized to meet the needs of additional temporary cursor keysets generated by multiple clients.

Working with BLOB or Chunk Datatypes
Many DBMS's, including SQL Server, support Binary Large Object (BLOB) data types. These types are most often used to store large amounts of text or image data. Due to limitations in the ODBC cursor library, special rules apply to the use of these kinds of data types when using ODBC cursors.

The ColumnSize property on the rdoColumn object represents the actual length of the data in a BLOB column. When using the ODBC cursor library, this value will always be -1, indicating that the data length is not available. When using server-side cursors, the ColumnSize property will always return the actual data length of a BLOB column.

To get the data from a BLOB column, the user must use the GetChunk methods, which take a number of bytes to retrieve at a time. When using server-side cursors, the user can pass the value of the ColumnSize property as the number of bytes to retrieve to get all the data at once. Since the ColumnSize property is not available when using the ODBC cursor library, the user should call GetChunk repeatedly until no more data is returned. Below is a code sample that shows how to do this:


Dim s As String
Dim sTemp As String
Dim lColSize As Long

lColSize = MyResultset!MyBLOBColumn.ColumnSize
If lColSize = -1 Then
 ' Column size is not available.
 ' Loop getting chunks until no more data.
 sTemp = MyResultset!MyBLOBColumn.GetChunk(50)
 Do
  s = s & sTemp
  sTemp = MyResultset!MyBLOBColumn.GetChunk(50)
 Loop While Len(sTemp) > 0
Else
' Get all of it.
 If lColSize > 0 Then
  s = MyResultset!MyBLOBColumn.GetChunk(lColSize)
 End If

End If

In addition, when using ODBC cursor library and BLOB data types, the user must select at least one non-BLOB column in their result set so that RDO can use SQLExtendedFetch to retrieve the data. This would be the common case anyway, since you need to include a key field in the result set if you want to update the data.
Setting Limits
You can limit the number of rows returned by a query by creating an rdoPreparedStatement and setting the MaxRows property. Once the query processor returns MaxRows rows, it stops working on the query. You can also limit the amount of time the query processor works on a query by setting the QueryTimeout property on the rdoConnection or rdoPreparedStatement.

Note  When the SQL_MAX_ROWS ODBC statement option is set to a nonzero value, the maximum number of rows processed by Microsoft SQL Server is limited to n rows. This means that only n rows are returned by a query, or only n rows are inserted, updated, or deleted by an action query. SQL_MAX_ROWS is set indirectly by using the rdoPreparedStatement object's MaxRows property. If you share the hStmt created for an rdoPreparedStatement that has MaxRows set, the operations executed against the hStmt are also affected by the limitation imposed by SQL_MAX_ROWS on both the number of rows returned from a query and the number of rows processed in an action query.

In addition, if you reuse an rdoPreparedStatement that has MaxRows set, the number of rows affected by any update, delete, or insert action query will be limited to n rows.

Processing Multiple Result Sets
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.

Note  When executing Remote Data Object (RDO) queries that return more than one set of results, you can use only the ODBC cursor drivers. The Microsoft SQL Server server-side cursors do not support result sets that return more than a single set of results. To enable the ODBC cursor driver, set the rdoEnvironment object's CursorDriver property to rdUseODBC before creating the cursor.

Action queries also generate row-less result sets that must also be processed. 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:

  • Execute the OpenResultset directly against a connection.
  • Use the OpenResultset against an rdoPreparedStatement.
Both are processed in similar ways, but if you use the rdoPreparedStatement, 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.

Multiple Result Sets: An Example

This section takes you through a step-by-step procedure that demonstrates how to execute a query with multiple result sets by using the rdoPreparedStatement.
  1. Create your SQL statement and place it in a string variable -- for instance, MySQL. For SQL Server, multiple statements must be separated by semi-colons.
    
    Dim MySQL As String
    MySQL = "Select Name from Authors; "
    MySQL = MySQL & " Select City from Publishers; "
    MySQL = MySQL & " Update MyTable Set Age = 16 Where Name = 'Fred' "
    
    
  2. Use an existing rdoPreparedStatement from the rdoPreparedStatements collection, or create a new rdoPreparedStatement and Set a variable declared as rdoPreparedStatement and multiple result sets to this object -- in this case, MyPs. The example assumes an rdoConnection object (Cn) already exists. While it is possible to pass the SQL statement to the CreatePreparedStatement method, this example sets the SQL property after creating an instance of the object. This technique is also used if the rdoPreparedStatement is already created and referenced by its name or ordinal number in the rdoPreparedStatements collection.
    
    Dim MyPs As rdoPreparedStatement
    Set MyPs = Cn.CreatePreparedStatement("MyPS1", "")
    MyPs.SQL = MySQL
    
    
  3. Execute the query by using the OpenResultset method against the rdoPreparedStatement object. If you do not need the extra properties and the ability to pass parameters to the query, you can use the OpenResultset method directly against the rdoConnection object. The arguments you use here affect all result sets fetched from this query. For example, if you need to use a cursor on the second result set, you must specify a cursor type when the first result set is opened.
    
    Dim MyRs As rdoResultset
    Set MyRs = MyPs.OpenResultset(rdOpenForwardOnly, rdConcurReadOnly)
    
    
  4. You are now ready to process the first result set. Note that the rdAsyncEnable options argument was not set. Because of this, control is not returned to the application until the first row of the first result set is ready for processing. If the current rdoResultset contains rows, the RowCount property is set to a value >0, and the EOF and BOF properties are both False. Depending on the driver and data source, the RowCount property returns either -1 to indicate that the number of rows is not available, or 0 to indicate that no rows were returned by the rdoResultset.

    The following example fills a ListBox control called NameList1 with the results of the query.

    
    While Not MyRs.EOF     ' Loop through all rows.
    NameList1.AddItem = MyRs(0) ' Use the first column.
    MyRs.MoveNext    ' Position to the next row 
    ' in the result set.
    Wend
    
    
  5. The first result set is now at the end-of-file (EOF) position. 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 ...
    
    
  6. You are now ready to process the second result set. This example uses only the first few names and discards the remaining rows.
    
    ' 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 ...
    
    
  7. You are now ready to process the last set of results. Because this is an UPDATE statement, there are no rows to be returned, but you can determine the number of rows affected by using the RowsAffected property. The MoreResults method is used for the last time to release all resources connected with this query.
    
    If MyPs.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 rdoPreparedStatement object can be reused. If you use the Close method against the rdoPreparedStatement, the rdoPreparedStatement is removed from the rdoPreparedStatements collection.
Creating Parameter Queries
If the SQL query you need to execute includes one or more parameters, it is necessary to create a new rdoPreparedStatement or use an existing rdoPreparedStatement to run the query. In addition, the SQL statement you submit must use ODBC SQL syntax that includes special arguments that act as placeholders for input and output query parameters. RDO parses these parameters, which you can manipulate with the rdoParameter objects.

You can use a parameter as either an input, output, or both input and output parameter. For example, to execute the sp_password procedure (which can be used in SQL Server systems to change a user's password), use the TSQL statement as follows:


execute sp_password clyde, framis

This procedure query accepts two input parameters and passes back a return value. You could use the Execute method to run this query, but the return value would be lost. To capture the return value, and create an rdoPreparedStatement that can be used repeatedly to change user passwords, write code like the following:

Dim CPw As rdoPreparedStatement, QSQL As String
QSQL$ = "{ ? = call sp_password (?, ?) }"

The ODBC syntax for the parameter query uses question marks as placeholders for both the input and the output parameters. ODBC syntax requires the use of call rather than the commonly used execute keyword. Stored procedure calls should be surrounded by braces ({}) as shown in the preceding code fragment. Failure to use the correct syntax may not prevent the procedure from being executed. However, unless this syntax is used, ODBC may not be able to identify the parameter positions or markers.

The next line of code creates the rdoPreparedStatement and names it SetPassword. The SQL property is set with the ODBC syntax SQL query. You only need to execute this line once. The new rdoPreparedStatement object is automatically appended to the rdoPreparedStatements collection where it can be recalled later.


Set CPw = cn.CreatePreparedStatement("SetPassword",QSQL$)

When the ODBC interface executes an SQL statement, it creates one or more stored procedures on the server. These procedures contain the SQL statement specified in the rdoPreparedStatement object or the OpenResultset method and are designed to accept any parameters that might be specified for the statement. Depending on the version of the server, these procedures are either created in the current database or in the TempDB database. In some cases, several stored procedures can be created for a single statement. Generally, these procedures are not released until you close the connection or end the application. Ending the application in design mode does not clear these statements. In this case, only ending Visual Basic clears these temporary procedures.

To avoid the creation of these procedures in the first place, specify the rdExecDirect option when using the OpenResultset method. For example,


Set rs = cn.OpenResultset("Select * from Authors", rdOpenStatic, _
rdConcurValues, rdExecDirect)

By using the rdExecDirect option, the ODBC interface does not create a procedure which is used to subsequently run the SQL statement. In some cases, this can be somewhat faster to execute, but only if the statement is used infrequently.

The next step is to set the value of each input parameter and set the Direction property to indicate that the parameter is used for input, output, or both. The default Direction is rdParamInput. The ordinal number of the parameters is a function of the order in which they appear in the SQL statement. In this case, the "0th" parameter is the return value (? = ), the "1st" is the first input parameter, and the "2nd" is the second input parameter. The rdoParameters collection is zero-based. It is unnecessary to set the Direction property on the input parameters because the default Direction is rdParamInput.


Cpw.rdoParameters(0).Direction = rdParamReturnValue
Cpw.rdoParameters(2).Direction = rdParamInput

Cpw.rdoParameters(1) = "clyde"  ' Set the first input parameter.
Cpw.rdoParameters(2) = "framis"  ' Set the second input parameter.

Once the parameter direction and values are set, you can use the Execute method to run the query if it does not return rows, or the OpenResultset method if the procedure contains one or more SELECT statements:

Cpw.Execute()

Once the procedure is executed, you can examine the rdoParameters collection for the returned value:

If Cpw.rdoParameters(0) <> 0 Then
 Msgbox "Could not change password"
End If

Using Parameter Queries in SELECT Statements
You can also use the same technique to pass parameters into an SQL statement. For example, if you want to create a query to look up authors by name from the Pubs sample database, you could create an ODBC SQL statement like this:

SELECT * FROM Authors WHERE Au_Lname = ? 

You can then set up a query using the following code, which passes the value entered by the user in Text1.Text into the query:

QSQL$ = "SELECT * FROM Authors WHERE Au_Lname = ?"}
Set PSAuthors = cn.CreatePreparedStatement("",QSQL$)
PSAuthors.rdoParameters(0) = Text1.Text
Set MyRs = PSAuthors.OpenResultset()

If the user changes the value in Text1.Text, you can re-execute the query by using the Requery method against the rdoResultset (MyRs).

PSAuthors.rdoParameters(0) = Text1.Text
MyRs.Requery

When RDO executes the Requery method, it refreshes the parameter value(s) in the rdoParameters collection, builds a new SQL statement, flushes the current result set, sends the query to the data source for execution, and creates a new rdoResultset.
For More Information
Search Visual Basic Books Online or online Help for rdoPreparedStatement object, rdoParameter object, Requery method, and OpenResultset method.
Capturing Output Parameters
Using the same technique used with parameter queries in SELECT statements, you can also capture the output parameters from a procedure.

To capture output parameters

  1. Use ODBC escape syntax to establish placeholders for the output and input parameters and return values.
  2. Set the Direction property of the rdoParameter object to indicate how the parameter is used.
  3. Execute the query.

When the query has completed processing, retrieve the output parameter values from the rdoParameters collection.

The final example in the online Help topic "Creating Parameter Queries" is coded incorrectly. A correct example is shown below. Note that you use the variable set to the created rdoPreparedStatement to create the rdoResultset, which is not shown correctly in the Help example. This example executes a stored procedure that expects two input parameters and returns two output parameters along with a return value argument.


Dim SQL As String, MyOutputVal1 As Variant
Dim MyOutputVal2 As Variant, MyRetVal As Variant
Dim cn As rdoConnection, rs As rdoResultset

rdoEnvironments(0).CursorDriver = rdUseOdbc
' To permit execution on SQL Server 6.0 Set 
' cn=rdoEnvironments(0).OpenConnection(dsname:="MyDSN",
' Prompt:=rdDriverNoPrompt)

' Use ODBC parameter argument syntax.
SQL = "{ ? = call MyProcName (?, ?,?,?) }"

Dim Ps As rdoPreparedStatement   
' Create reusable rdoPreparedStatement.

Set Ps = cn.CreatePreparedStatement("PsTest",SQL)

' Set Parameter "direction" types for each parameter,
' both input and output.
Ps(0).Direction = rdParamReturnValue
Ps(1).Direction = rdParamInput
Ps(2).Direction = rdParamInput
Ps(3).Direction = rdParamOutput
Ps(4).Direction = rdParamOutput

' Set the input argument values.
Ps.rdoParameters(1) = "Test%"   
Ps.rdoParameters(2) = 1    


' Create the result set and populate the Ps values.
Set rs = Ps.OpenResultset(rdOpenStatic)

MyRetVal = Ps(0)   ' Contains the return value argument.
MyOutputVal1 = Ps(3)  ' Contains the first output parameter.
MyOutputVal2 = Ps(4)  ' Contains the second output parameter.

Using the rdoTable Object
If you need to examine the tables exposed by a data source or column detail, you can use the rdoTables collection. To improve performance, no table meta data is requested from the data source until the rdoTables collection is referenced. Once an rdoConnection is open, you can enumerate the tables and place their names in a ListBox control, as in the following example:

Dim tb As rdoTable
For Each tb in cn.rdoTables
 List1.AddItem tb.Name
Next

Each rdoTable object contains an rdoColumns collection that contains details about the data type and size of each column. While it is possible to create an rdoResultset against the rdoTable object, this query can only return all of the rows and is not recommended.
Using ODBC API Functions
Each rdoEnvironment, rdoConnection, rdoPreparedStatement, and rdoResultset object includes support for an ODBC handle that you can use to manipulate the object using the ODBC API. The hEnv, hDbc, and hStmt properties correspond to the rdoEnvironment, rdoConnection, and rdoResultset objects respectively.

The following table lists the ODBC handles that are created by RDO. You can use these handles in your own ODBC API code.

RDO object property Handle created by the ODBC API
rdoEnvironment.hEnv SQLAllocEnv
rdoConnection.hDbc SQLAllocConnect, SQLDriverConnect
rdoResultset.hStmt SQLAllocStmt

Warning While it is possible to use the ODBC API with the RDO ODBC handles, you are cautioned that incorrect use of the ODBC API can cause unpredictable behavior. For example, if you close connections or deallocate any of these handles using ODBC API code, the RemoteData control or RDO can behave unpredictably. The ODBC handles should not be saved for future use as they are subject to change without notice.

Managing Errors and Messages
Each time the ODBC driver manager is used to carry out an RDO request, an error can be generated. These errors can be of varying severity and in some cases, may cause the query to be canceled or abandoned. As errors are generated, they are placed in the rdoErrors collection. You can examine the individual members of the rdoErrors collection for details on what caused the error. Visual Basic also produces a trappable error when an error occurs. In your On Error handler you can examine the rdoErrors collection to determine what action should be taken. To change the severity of error that can trip a fatal error, set the rdoDefaultErrorThreshold or the ErrorThreshold property.

Informational messages returned from the data source do not trigger a trappable error. These messages do appear in the rdoErrors collection, which can be manually cleared with the Clear method.

For More Information
Search Visual Basic Books Online or online Help for rdoError object or rdoDefaultErrorThreshold property.

Using the RemoteData Control

The RemoteData control is similar to the Visual Basic Data control. Both the Data control and the RemoteData control are designed to connect data-aware bound controls to a data source. The primary difference is that the RemoteData control uses RDO to connect to the ODBC driver manager, while the Data control uses the Microsoft Jet database engine and DAO to connect to the ODBC driver manager. Even though both controls use the same ODBC driver manager and data source entries, the RDO and Jet engines cannot share data source connections, data objects, or other resources.

Without a RemoteData control, a Data control, or its equivalent, data-aware (bound) controls on a form can't automatically access data. You can perform most remote data access operations using the RemoteData control without writing any code at all. Data-aware controls bound to a RemoteData control automatically display data from one or more columns for the current row or, in some cases, for a set of rows on either side of the current row. The RemoteData control performs all operations on the current row.

If the RemoteData control is instructed to move to a different row, all bound controls automatically pass any changes to the RemoteData control to be saved by the ODBC data source. The RemoteData control then moves to the requested row and passes back data from the current row to the bound controls where it is displayed.

The RemoteData control automatically handles a number of contingencies including empty result sets, adding new rows, editing and updating existing rows, and handling some types of errors. However, in more sophisticated applications, you need to trap some error conditions that the RemoteData control can't handle. For example, if the remote server has a problem accessing the data source, doesn't have permission, or can't execute the query as coded, a trappable error results. If the error occurs before your application procedures start, or because of some internal errors, the Error event is triggered.

When accessing an empty, updatable results et with the RemoteData (RDC) control, you cannot depend on the EOFAction property to force the control to switch to AddNew mode. To add the first record to an empty result set, you must use the AddNew method against the RemoteData Resultset property. This will be corrected in a later release of the control.

The RemoteData control uses and creates the same RDO objects discussed in this chapter. When the appropriate RemoteData control properties are set, the RemoteData control can create an rdoResultset that can be manipulated in code like any other rdoResultset. You can also create an rdoResultset in code and set the RemoteData control's Resultset property to point to this object.

Note To use the RemoteData control, you need to add it to the Visual Basic Toolbox. To add the RemoteData control to your Toolbox, use the Custom Controls dialog box (available from the Tools menu), and select "Microsoft Remote Data Control" from the list of custom controls.

RDO is only supported on 32-bit operating systems (Windows 95 and Windows NT). To use the Remote Data Objects, you must set a reference to the Microsoft Remote Data Object 1.0 object library in the Visual Basic References dialog box.

Understanding RemoteData Control Operations
Once your application begins, if sufficient properties have been set at design time, Visual Basic uses the RemoteData control to establish a connection to the ODBC data source. This creates an rdoConnection object by using the DataSourceName, UserName, Password, Options, Type, and Connect properties.

If insufficient information is provided in the RemoteData control properties, the ODBC driver manager exposes a dialog box to gather missing parameters. If the connection is established, the RemoteData control sets or resets the Environment, Connection, DataSourceName, Transactions, and Connect properties based on the values used to establish the connection.

Once the connection is established, the RemoteData control runs a query against the data source using the SQL, CursorDriver, Options, LockType, ErrorThreshold, and ResultsetType properties. This creates an rdoResultset object and sets the Resultset, ResultsetType, and Updatable properties. By default, a read-only, forward-only - type rdoResultset is created. The StillExecuting property is set to True while the rdoResultset is created. If you choose to cancel the query, and the rdAsyncEnable option is set, you can use the Cancel method against the rdoResultset to terminate processing of the query.

A known bug prevents complete use of Image or PictureBox controls with the RemoteData control. While it is possible to read TEXT and IMAGE datatypes using the RemoteData control, it is not possible to update this type of column. It is possible to update these columns using RDO code or with the Data control.

Note that graphics columns created by Microsoft Access must be accessed with OLE controls, not Picture or Image controls.

Once the first row of the rdoResultset is available, the StillExecuting property is set to False and the RemoteData control passes column data to each bound control requesting data. The rdoResultset.RowCount property is set to a non-zero value if any rows resulted from the query. If no data is returned by the rdoResultset, the RemoteData control's behavior is determined by the EOFAction property.

Note Make sure that each bound control's DataField property corresponds to the Name property of the rdoColumn object returned by the rdoResultset. If the DataField property does not match the Name property of one of the rdoColumn objects in the rdoResultset, a trappable error occurs.

Positioning the Current Row Pointer
Users can manipulate the RemoteData control with the mouse. They can move from row to row, or to the beginning or end of the rdoResultset by clicking the control. As the user manipulates the RemoteData control buttons, the current row pointer is repositioned in the rdoResultset. The RemoteData control doesn't permit the user to move off either end of the rdoResultset using the mouse. You can't set focus to the RemoteData control.

Keep the following guidelines in mind when writing code that will reposition to the current row pointer:

  • Before each reposition, the bound controls are queried for new data for the current row. If the data changes, the Validate event is invoked, and if not canceled by the action argument, the data source is updated.
  • After the RemoteData control positions to a new row in the data source, column data is passed to the bound controls and the Reposition event is invoked.
  • Once either end of the rdoResultset is reached, the RemoteData control's behavior is determined by the EOFAction and BOFAction properties.
  • You can use the MoreResults method against the rdoResultset to complete processing of the current result set and determine if additional result sets are available. If MoreResults returns True, the process of handling the rdoResultset is restarted -- just as if a new query had been executed. The previous result set is no longer available.
Programmatic Operation
To create an rdoResultset programmatically with the RemoteData control:

  1. Set the RemoteData control properties to determine the characteristics of the rdoResultset.
  2. Use the Refresh method to begin the automated process or create the new rdoResultset. Any existing rdoResultset is discarded.
You can manipulate all of the RemoteData control properties and the new rdoResultset object independently of the RemoteData control -- with or without bound controls. The rdoConnection and rdoResultset objects each have properties and methods of their own that you can use with your procedures.

For example, the MoveNext method of an rdoResultset object moves the current row to the next row in the rdoResultset. To invoke this method with an rdoResultset created by a RemoteData control, you could use this code:


RemoteData1.rdoResultset.MoveNext

If you need to execute a parameter query using the RemoteData control, create an rdoResultset based on an rdoPreparedStatement, and then set the RemoteData control Resultset property to the newly created rdoResultset. You can also use this technique to access another rdoEnvironment.
Asynchronous Operation
If you set the Options property to rdAsyncEnable before the RemoteData control creates the rdoResultset, control returns to your application before the rdoResultset contains rows. Check the StillExecuting property of the rdoResultset object to determine when the first data row is available. To cancel the query, use the rdoResultset.Cancel method. Once the query is complete, the QueryCompleted event is invoked, which indicates that new data is now available from the rdoResultset.

If you do not specify asynchronous operation with rdAsyncEnable, no other Visual Basic operations or events can occur until the first data row of the rdoResultset is fetched. However, other Windows - based applications can continue executing while the rdoResultset is being created.

When you use a RemoteData control to create an rdoResultset object, the remote database engine automatically populates the rdoResultset object as a background task. As a result, any existing bookmarks are saved, the user doesn't need to manipulate the RemoteData control, and you don't need to invoke the MoveLast method in code. In addition, page locks used to create the rdoResultset are released more quickly, making it possible for other rdoResultset objects to access the same data.

You can also create an rdoResultset object using the OpenResultset method and set the Resultset property to the new rdoResultset. If this is done, all other appropriate RemoteData control properties are reset to reflect the new rdoResultset and rdoConnection.

Validation
Use the Validate event and the DataChanged property to perform last minute checks on the rows being written to the database. The Validate event is invoked before each reposition of the current row pointer. If the data changes, the Validate event is invoked, and if not canceled by the action argument, the data source is updated.
For More Information
Search Visual Basic Books Online or online Help for Validate event.  
Last Updated: 8/31/98  

1998 Microsoft Corporation. All rights reserved. Terms of Use.  

error 'ASP 0113'

Script timed out

/vbasic/technical/articles/remote/default.asp

The maximum amount of time for a script to execute was exceeded. You can change this limit by specifying a new value for the property Server.ScriptTimeOut or by changing the value in the IIS administration tools.