Update and CancelUpdate Methods Example

This example demonstrates the Update method in conjunction with the 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 = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
   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

This example demonstrates 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 = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
   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