ADO Recordset Object Update Method

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

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:

· Assign values to a ADO Field Object object's ADO Field Object Value Property and call the ADO Recordset Object Update Method.

· Pass a field name and a value as arguments with the Update call.

· Pass an array of field names and an array of values with the Update call.

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