Updating Records

The Recordset control includes features that make it easy for you to allow users to update the current record in a recordset. For example, if you use a RecordsetNavBar control on your page, you can set an option that automatically updates the current record with user changes when a user navigates to another record.

Not all recordsets allow changes to be written to the database. The Recordset object's cursor type must be set to Keyset or Dynamic. You must also have permission to update the database. Finally, queries can produce recordsets that do not contain sufficient information to allow updates. For details, see Determining When Query Results Can Be Updated.

To enable automatic navigation updates

• Set the RecordsetNavBar control's updateOnMove property to True.

  Note   Setting this option to True causes the recordset to be updated each time the user navigates, even if no changes have been made. If you anticipate that users will seldom make changes, you might want to use a strategy with less overhead.

If you are working with data-bound design-time controls, they automatically copy their values to the current record before the update is made. However, if you are working with a control that is not a data-bound design-time control, you must manually update the current record before the update is made.

To set values in the current record

• Call the setValue method for the fields collection and specify which field you want. You specify the field to update and the value.

  Tip   A good time to get the values from the current record is in a handler for the recordset's onbeforeupdate event.

  For example, the following statement sets the Name field of the current record to the value of a textbox called Name:

  rsEmployeeList.fields.setValue("Name", txtName.value)
  

Note   You can only copy from a control to the recordset if the control and recordset use the same target scripting platform. For more details, see The Scripting Object Model and Creating Forms with Design-Time Controls.

You can also update records in script. The basic procedure is to call a method that writes the current record to the database.

To update records in scripts

1. Be sure there is a Recordset control on your page. For details, see Getting Records. Note the Recordset control's name.
   
   
2. If you have controls on the page that are not data-bound design-time controls, copy their values into the current record as described in the preceding procedure.
   
   
3. After you have finished setting all the values you need in the current record, call the updateRecord method.

The following example shows a handler for a Save button's onclick event, which saves the current record to the database.

Sub btnSave_onclick
   rsEmployeeList.updateRecord
End sub

The following example shows the same procedure, but copies values from non-data-bound controls to the current record before saving it.

Sub btnSave_onclick
   ' Copying data to the current record is required only for 
   ' controls that are not data-bound design-time controls
   rsEmployeeList.fields.setValue("Name", txtName.value)
   rsEmployeeList.fields.setValue("Insured", chkInsured.value)
   rsEmployeeList.fields.setValue("LastUpdate", date)
   rsEmployeeList.updateRecord
End sub

To help you trap errors when updating a recordset, you can write handlers for the recordset object's onbeforeupdate and onafterupdate events. Typically you write a handler for the onbeforeupdate event to make sure that data in the current record is correct. You can use the onafterupdate event to determine whether the update was successful.

For more information about writing event handlers for design-time controls, see Writing Scripts for Script Objects.