MarshalOptions Property Example (VB)

MarshalOptions Property Example (VB)

This example uses the MarshalOptions property to specify what rows are sent back to the server—All Rows or only Modified Rows.

Public Sub MarshalOptionsX()

    Dim rstEmployees As ADODB.Recordset
    Dim strCnn As String
    Dim strOldFirst As String
    Dim strOldLast As String
    Dim strMessage As String
    Dim strMarshalAll As String
    Dim strMarshalModified 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.CursorLocation = adUseClient
    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?"
    strMarshalAll = "Would you like to send all the rows " & _
                    "in the recordset back to the server?"
    strMarshalModified = "Would you like to send only " & _
                    "modified rows back to the server?"
 
    If MsgBox(strMessage, vbYesNo) = vbYes Then
        If MsgBox(strMarshalAll, vbYesNo) = vbYes Then
            rstEmployees.MarshalOptions = adMarshalAll
            rstEmployees.Update
        ElseIf MsgBox(strMarshalModified, vbYesNo) = vbYes Then
            rstEmployees.MarshalOptions = adMarshalModifiedOnly
            rstEmployees.Update
        End If
    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