Sun
ONE Active Server Pages Product Home Page Developer Site Version
 

ContentsPreviousNextIndex



ADO Recordset Object Update Method

Saves any changes you make to the current record of a Recordset object.

Note icon Note This method is not available for some databases and ODBC drivers.
Update Method Syntax
recordset.Update Fields, Values
Update Method Parameters

Fields

An optional Variant representing a single name or a Variant array representing names or ordinal positions of the field or fields you wish to modify.

Values

An optional Variant representing a single value or a Variant array representing values for the field or fields in the new record.

Update Method Remarks

Use the Update method to save any changes you make to the current record of a Recordset object since calling the ADO Recordset Object AddNew Method or since changing any field values in an existing record. The Recordset object must support updates.

To set field values, do one of the following:

When you use arrays of fields and values, there must be an equal number of elements in both arrays. Also, the order of field names must match the order of field values. If the number and order of fields and values do not match, an error occurs.

If the Recordset object supports batch updating, then you can cache multiple changes to one or more records locally until you call the ADO Recordset Object UpdateBatch Method. If you are editing the current record or adding a new record when you call the UpdateBatch method, ADO will automatically call the Update method to save any pending changes to the current record before transmitting the batched changes to the provider. Batch updating is not currently supported on UNIX.

If you move from the record you are adding or editing before calling the Update method, ADO will automatically call Update to save the changes. You must call the ADO Recordset Object CancelUpdate Method if you want to cancel any changes made to the current record or to discard a newly added record.

The current record remains current after you call the Update method.

Update Method Examples

The following Visual Basic examples show how to use the Update method.

The first example demonstrates using the Update method in conjunction with CancelUpdate method.

Public Sub UpdateX() 
Dim rstEmployees As ADODB.Recordset 
Dim strOldFirst As String 
Dim strOldLast As String 
Dim strMessage As String
` Open recordset with names from Employee table. 
strCnn = "driver={SQL Server};server=srv;" & _ 
"uid=sa;pwd=;database=pubs" 
Set rstEmployees = New ADODB.Recordset 
rstEmployees.CursorType = adOpenKeyset 
rstEmployees.LockType = adLockOptimistic 
rstEmployees.Open "SELECT fname, lname " & _ 
"FROM Employee ORDER BY lname", strCnn, , , adCmdText
` Store original data. 
strOldFirst = rstEmployees!fname 
strOldLast = rstEmployees!lname 
` Change data in edit buffer. 
rstEmployees!fname = "Linda" 
rstEmployees!lname = "Kobara"
` Show contents of buffer and get user input. 
strMessage = "Edit in progress:" & vbCr & _ 
" Original data = " & strOldFirst & " " & _ 
strOldLast & vbCr & " Data in buffer = " & _ 
rstEmployees!fname & " " & rstEmployees!lname & vbCr & vbCr & _ 
"Use Update to replace the original data with " & _ 
"the buffered data in the Recordset?"
If MsgBox(strMessage, vbYesNo) = vbYes Then 
rstEmployees.Update 
Else 
rstEmployees.CancelUpdate 
End If
` Show the resulting data. 
MsgBox "Data in recordset = " & rstEmployees!fname & " " & _ 
rstEmployees!lname
` Restore original data because this is a demonstration. 
If Not (strOldFirst = rstEmployees!fname And _ 
strOldLast = rstEmployees!lname) Then 
rstEmployees!fname = strOldFirst 
rstEmployees!lname = strOldLast 
rstEmployees.Update 
End If 
rstEmployees.Close 
End Sub

The following example demonstrates using the Update method in conjunction with the AddNew method:

Public Sub UpdateX2() 
Dim cnn1 As ADODB.Connection 
Dim rstEmployees As ADODB.Recordset 
Dim strEmpID As String 
Dim strOldFirst As String 
Dim strOldLast As String 
Dim strMessage As String
' Open a connection. 
Set cnn1 = New ADODB.Connection 
strCnn = "driver={SQL Server};server=srv;" & _ 
"uid=sa;pwd=;database=pubs" 
cnn1.Open strCnn
' Open recordset with data from Employee table. 
Set rstEmployees = New ADODB.Recordset 
rstEmployees.CursorType = adOpenKeyset 
rstEmployees.LockType = adLockOptimistic 
rstEmployees.Open "employee", cnn1, , , adCmdTable 
rstEmployees.AddNew 
strEmpID = "B-S55555M" 
rstEmployees!emp_id = strEmpID 
rstEmployees!fname = "Bill" 
rstEmployees!lname = "Sornsin"
' Show contents of buffer and get user input. 
strMessage = "AddNew in progress:" & vbCr & _ 
"Data in buffer = " & rstEmployees!emp_id & ", " & _ 
rstEmployees!fname & " " & rstEmployees!lname & vbCr & vbCr & _ 
"Use Update to save buffer to recordset?"
If MsgBox(strMessage, vbYesNoCancel) = vbYes Then 
rstEmployees.Update 
` Go to the new record and show the resulting data. 
MsgBox "Data in recordset = " & rstEmployees!emp_id & ", " & _ 
rstEmployees!fname & " " & rstEmployees!lname 
Else 
rstEmployees.CancelUpdate 
MsgBox "No new record added." 
End If
' Delete new data because this is a demonstration. 
cnn1.Execute "DELETE FROM employee WHERE emp_id = '" & strEmpID & "'" 
rstEmployees.Close 
End Sub


ContentsPreviousNextIndex