Update and CancelUpdate Methods Example (VB)

Update and CancelUpdate Methods Example (VB)

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 Employees 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 Employees 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 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