Product Home Page | Developer Site | Version | |
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. |
recordset.Update Fields, Values
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.
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.
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
Copyright © 2003 Sun Microsystems, Inc. All rights reserved.