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:
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.
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. |
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:
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:
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.
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:
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:
Once a row is deleted, the current row is no longer valid, so you must reposition to another valid row in the rdoResultset.
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.