Visual Basic: RDO Data Control

  • Article

RemoteData Control

See Also    Example    Properties    Methods    Events

Provides access to data stored in a remote ODBC data source through bound controls. The RemoteData control enables you to move from row to row in a result set and to display and manipulate data from the rows in bound controls.

Syntax

RemoteData

Remarks

The RemoteData control provides an interface between Remote Data Objects (RDO) and data-aware bound controls. With the RemoteData control, you can:

  • Establish a connection to a data source based on its properties.

  • Create an rdoResultset.

  • Pass the current row's data to corresponding bound controls.

  • Permit the user to position the current row pointer.

  • Pass any changes made to the bound controls back to the data source.

Overview

Without a RemoteData control, a Data control or its equivalent, data-aware (bound) controls on a form can't automatically access data. The RemoteData and Data controls are examples of DataSource Controls. You can perform most remote data access operations using the DataSource controls without writing any code at all. Data-aware controls bound to a DataSource 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. DataSource controls perform all operations on the current row.

The RemoteData DataSource Control

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 to 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's displayed.

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

Operation

Use the RemoteData control properties to describe the data source, establish a connection, and specify the type of cursor to create. If you alter these properties once the result set is created, use the Refresh method to rebuild the underlying rdoResultset based on the new property settings.

The RemoteData control behaves like the Jet-driven Data control in most respects. The following guidelines illustrate a few differences that apply when setting the SQL property.

You can treat the RemoteData control's SQL property like the Data control's RecordSource property except that it cannot accept the name of a table by itself, unless you populate the rdoTables collection first. Generally, the SQL property specifies an SQL query. For example, instead of just "Authors", you would code "SELECT * FROM AUTHORS" which provides the same functionality. However, specifying a table in this manner is not a good programming practice as it tends to return too many rows and can easily exhaust workstation resources or lock large segments of the database.

The result set created by the RemoteData control might not be in the same order as the Recordset created by the Data control. For example, if the Data control's RecordSource property is set to "Authors" and the RemoteData control's SQL property is set to "SELECT * FROM AUTHORS", the first record returned by Jet to the Data control is based on the first available index on the Authors table. The RemoteData control, however, returns the first row returned by the remote database engine based on the physical sequence of the rows in the database, regardless of any indexes. In some cases, the order of the records could be identical, but not always.

This difference in behavior can affect how bound controls handle the resulting rows especially multiple-row bound controls like the DataGrid control. You can manipulate the RemoteData control with the mouse to move the current row pointer from row to row, or to the beginning or end of the rdoResultset by clicking the control. As you manipulate the RemoteData control buttons, the current row pointer is repositioned in the rdoResultset. You cannot move off either end of the rdoResultset using the mouse. You also can't set focus to the RemoteData control.

Other Features

You can use the objects created by the RemoteData control to create additional rdoConnection, rdoResultset, or rdoQueryobjects.

You can set the RemoteData control Resultset property to an rdoResultset created independently of the control. If this is done, the RemoteData control properties are reset based on the new rdoResultset and rdoConnection.

You can set the Options property to enable asynchronous creation of the rdoResultset (rdAsyncEnable) or to execute the query without creating a temporary stored procedure (rdExecDirect).

The Validate event is triggered before each reposition of the current row pointer. You can choose to accept the changes made to bound controls or cancel the operation using the Validate event's action argument.

The RemoteData control can also manage what happens when you encounter an rdoResultset with no rows. By changing the EOFAction property, you can program the RemoteData control to enter AddNew mode automatically.

Note If you have an Image control bound to an image-containing field in a RemoteData control, and the RemoteData control uses batch cursors (that is, CursorDriver = rdUseClientBatch), the Image control doesn't display the image.

Programmatic Operation

To create an rdoResultset programmatically with the RemoteData control:

  • Set the RemoteData control properties to describe the desired characteristics of the rdoResultset.

  • Use the Refresh method to begin the automated process or to create the new rdoResultset. Any existing rdoResultset is discarded.

All of the RemoteData control properties and the new rdoResultset object may be manipulated independently of the RemoteData controlwith or without bound controls. The rdoConnection and rdoResultset objects each have properties and methods of their own that can be used with procedures that you write.

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.Resultset.MoveNext

Resultset Does Not Automatically Update Bound Controls

Assigning a resultset to a RemoteData Control (RDC) doesn't update bound controls. When you bind a control to the resultset of an RDC, the resultset doesn't automatically display in the control. To illustrate this:

  1. Start Visual Basic and open a Standard EXE project.

  2. Reference the RDC.

  3. Place an RDC on the form.

  4. Place a TextBox control on the form.

  5. Set the following TextBox properties:

    DataSource: MSRDC1

    DataField: au_lname

  6. Place a CommandButton control on the form and add the following code to its Click event:

    Dim cn As New rdoConnection
cn.Connect = _
"dsn=pinkpearl;database=rdobugs;uid=rdo;pwd="
cn.EstablishConnection
Set MSRDC1.Resultset = cn.OpenResultset("select * _
from authors]")

  7. Run the project (F5).

  8. Click the CommandButton.

Notice that the bound control does not populate with data as you would expect. You must issue the command MSRDC1.Refresh for the bound control to populate, which causes the server to send the entire resultset again. (Note that this can take a long time in some situations.)

To work around this problem, set any bound control's datafield after setting the resultset in code. For example, after the line:

Set MSRDC1.Resultset = cn.OpenResultset("select * _
from authors]")

you would add:

Text1.DataField = "au_lname"

which forces the binding manager to set and update the bindings, which populates the bound control with data.

Bound Image or PictureBox Control Doesn't Display Picture When RDC Uses Batch Cursors

When you are using an Image or PictureBox control bound to an image-containing field in an RDC, and the RDC uses batch cursors (CursorDriver = rdUseClientBatch), be aware that the Image or PictureBox control doesn't display the image. To correctly display the image, either set the RDC's Options property to 128 (rdFetchLongColumns), or use a different cursor.

Do Not Use Forward-only Resultsets

When you attempt to assign a forward-only resultset to an RDC, you get an "invalid object" error. To illustrate this situation:

  1. Start Visual Basic.

  2. Place a RemoteData control on Form1.

  3. Add a reference to RDO through the References command on the Project menu.

  4. Add the following code to the Form_Load event:

    Dim x as new rdoConnection
Dim y as rdoQuery
x.Connect = "DSN=Union;UID=rdo;PWD="
x.EstablishConnection
Set y = x.CreateQuery("Query1", "SELECT * FROM _
authors")
x.Query1
' invalid object error occurs on next line
Set MSRDC1.Resultset = x.LastQueryResults

  5. Press F5.

The reason this error occurs is that it uses a forward-only resultset which cannot be assigned to the RDC. In order to assign a resultset to an RDC, it must be either keyset or static. For example:

Dim x As New rdoConnection
Dim y As rdoQuery
x.Connect = "DSN=Union;database=rdobugs;UID=rdo;PWD="
  x.EstablishConnection
Set y = x.CreateQuery("Query1", "SELECT * FROM _
  authors")
y.CursorType = rdOpenKeyset
y.LockType = rdConcurRowVer
x.Query1
Set MSRDC1.Resultset = x.LastQueryResults