Update and CancelUpdate Methods Example (VB)

This example demonstrates the Update method in conjunction with the CancelUpdate method.

'BeginUpdateVB
Public Sub UpdateX()

    'To integrate this code
    'replace the data source and initial catalog values
    'in the connection string

    ' recordset and connection variables
   Dim rstEmployees As ADODB.Recordset
   Dim Cnxn As ADODB.Connection
   Dim strCnxn As String
   Dim strSQLEmployees As String
    ' buffer variables
   Dim strOldFirst As String
   Dim strOldLast As String
   Dim strMessage As String
   
   ' Open connection
   Set Cnxn = New ADODB.Connection
   strCnxn = "Provider=sqloledb;Data Source=MyServer;Initial Catalog=Pubs;User Id=sa;Password=; "
   Cnxn.Open strCnxn
    
   ' Open recordset to enable changes
   Set rstEmployees = New ADODB.Recordset
   strSQLEmployees = "SELECT fname, lname FROM Employee ORDER BY lname"
   rstEmployees.Open strSQLEmployees, Cnxn, adOpenKeyset, adLockOptimistic, 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
    
    ' clean up
   rstEmployees.Close
   Cnxn.Close
   Set rstEmployees = Nothing
   Set Cnxn = Nothing

End Sub
' EndUpdateVB

This example demonstrates the Update method in conjunction with the AddNew method.

' BeginUpdate2VB
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
   Dim strCnn As String

   ' Open a connection.
   Set cnn1 = New ADODB.Connection
      strCnn = "Provider=sqloledb;" & _
      "Data Source=MyServer;Initial Catalog=Pubs;User Id=sa;Password=; "
   cnn1.Open strCnn

   ' Open recordset with data from Employees 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
'EndUpdate2VB

Update and CancelUpdate Methods Example (VB)

See Also