Visual Basic Concepts

Updating RDO Data

Not all rdoResultset objects are updatable. In some cases, you might be unable to update a result set using a cursor, but be able to update using an action query. In order for a cursor to be used to update base tables, a number of conditions must be met:

The table has a unique index that the cursor library can use as a unique row identifier. Not having a unique key is the most common fault when you discover that a table cannot be updated.
The logged-on user has read-write access to the underlying columns, tables, and database. In some cases, the only way to update a table might be by stored procedures.
The remote system's database administrator permits direct update access to tables (many don't).
The result set is based on a complex multitable join. While it is possible to update a join, not all joins are updatable.
The type of cursor supports updates.
Other users are not accessing the data. Could other users have the data pages locked?
The Updatable property for both the rdoResultset and rdoColumn objects returns True. Even when this property is True, the result set or specific columns still might not be updatable.

Updating Through Stored Procedures

Many multiuser systems do not attempt to create applications that depend on the direct updatability of base tables. While it seems very easy to create a cursor to update a specific row in a chosen table, your system administrator might prohibit this particularly dangerous activity. In addition, database business rules and relational integrity often depend on synchronizing other tables in the same logical operation. Your database might include triggers or other mechanisms to perform some or all of these operations automatically, so your application can be simpler and not have to deal with the details.

To update a base table and perform ancillary operations with RDO cursors, you can begin a transaction and manage all aspects of the update in your application, perhaps by executing a number of independent update operations. However, this fairly complex approach must be duplicated for each application that needs update access to the tables. Once the business rules change or the referential integrity schema is tuned, your application must often be recompiled and redistributed.

To implement a better solution, many sites use stored procedures to return rowsets and subsequently update specific rows. This approach leverages most remote database and network security schemes and has been successfully implemented in systems supporting 1,000 or more users. Another benefit of this approach is performance. Because your applications are usually written with limited-scope result sets and do not depend solely on cursors, the workstation, network, and remote server all work together more efficiently.

Updating with Stored Procedures Executed via the WillUpdateRows Event

RDO 2.0 also permits you to execute a stored procedure when using a cursor to update the database. This means that you can use your own stored procedures to add, change, and delete data. If you code a procedure for the WillUpdateRows event, you can submit a stored procedure to perform the update. Or you can simply code a number of update or other operations that are executed instead of, or in addition to, the cursor-based operations.

If the result set is not in batch mode, the WillUpdateRows event is fired for each call to the Update method just before the ODBC update is sent to the server. In batch mode, RDO fires the WillUpdateRows event when the BatchUpdate method is executed.

By setting the WillUpdateRows event ReturnCode argument, you can indicate the success or failure of your update operation. The following table summarizes the options you can use for the ReturnCode argument.

ReturnCode	Indicates
rdUpdateSuccessful	Your update operation was successful. RDO notifies no other handlers (if there are more than one handler for this event) and marks columns as updated.
rdUpdateWithCollisions	Your update operation was successful, but some rows produced collisions (batch mode only). RDO notifies no other handlers (if there is more than one handler for this event). Columns are not marked as updated.
rdUpdateFailed	Your update operation was not successful. RDO notifies no other handlers (if there is more than one handler for this event) and generates a trappable run-time error. Columns are not marked as updated.
rdUpdateNotHandled	(Default) Your procedure did not handle the update. RDO continues notifying other handlers (if any). If no other procedures handle the update, RDO updates the data and marks the columns as updated.

Updating Data Sources Using Cursors

If your database does not implement sophisticated referential integrity or complex business rules and can permit direct access to base tables, you can use an rdoResultset cursor to update data rows. Keep in mind that many of the problems associated with updating have to do with managing concurrency — row and page locking, and sharing data. Your error handlers should be prepared to deal with a wide range of contingencies, including row and page lock conflicts, loss of connection, update conflicts, and many others.

Changing data row values in an rdoResultset involves the following steps:

Position to the row using one of the current row repositioning techniques discussed in "Positioning the Current Row Pointer."
Use the Edit method to activate the row to be altered. All data is copied into a temporary row buffer.
Provide a new value for each column that you need to modify. To address the temporary row buffer, simply address the rdoResultset object's columns as you would when retrieving data from the columns.
When all columns are changed to the correct values, use the Update method to save the row buffer to the data source. The current row is deleted and the buffered row replaces it. At this point the LastModified property returns a bookmark to the modified row.

Abandoning an Edit

No changes are made until you use the Update method, when your edits are committed to the database. However, if you decide to abandon the edit and revert back to the unedited version of the current row, you have a number of choices:

Move the current row pointer to another row.
Use the Move method with a 0 argument to refresh the current row.
Use the CancelUpdate method.
Use the Rollback method to undo the transaction, assuming you preceded the operation with the BeginTrans method.
Set the Cancel argument to True in the WillUpdate event to bypass the actual ODBC update operation.

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.

Adding and Deleting rdoResultset Rows

In addition to changing columns in a specific row, you can add a row to an rdoResultset which adds a row to a base table. This operations assumes that your application has read-write access and permissions to add rows to the underlying table(s). Because this is often not the case, be sure to investigate the WillUpdate event and the topics on using stored procedures elsewhere in this chapter.

Use these steps to add a specific row in an rdoResultset:

Use the AddNew method to create a temporary row buffer to hold the new row to be added.
Provide a new value for each column you need to modify. To address the temporary row buffer, simply address the rdoResultset object's columns as you would when retrieving data from the columns.
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 — if the cursor supports addressing new rows. If this is the case, 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, assuming you also have permission to delete data (you might not).

Use these steps to delete a specific row in an rdoResultset:

Position to the row using one of the Move methods or the AbsolutePosition or PercentPosition property.
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.

Making Changes with RDO Action Queries

A more efficient 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 multistatement 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. Because the Execute method also supports the rdAsyncEnable option, these operations can be performed asynchronously.

Note 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.