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.
'BeginMarshalOptionsVB

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

Public Sub MarshalOptionsX()

   Dim rstEmployees As ADODB.Recordset
   Dim Cnxn As ADODB.Connection
   Dim strCnxn As String
   Dim strSQLEmployees As String
   
   Dim strOldFirst As String
   Dim strOldLast As String
   Dim strMessage As String
   Dim strMarshalAll As String
   Dim strMarshalModified As String
   
   ' Open connection
   strCnxn = "Provider=sqloledb;Data Source=MyServer;Initial Catalog=Pubs;User Id=sa;Password=; "
   Set Cnxn = New ADODB.Connection
   Cnxn.Open strCnxn
   
   ' open recordset with names from Employees table
   ' and set some properties through object refs
   Set rstEmployees = New ADODB.Recordset
   rstEmployees.CursorType = adOpenKeyset
   rstEmployees.LockType = adLockOptimistic
   rstEmployees.CursorLocation = adUseClient
   
   strSQLEmployees = "SELECT fname, lname FROM Employee ORDER BY lname"
   
   rstEmployees.Open strSQLEmployees, Cnxn, , , adCmdText
    ' empty properties have been set above

    ' 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
   
   ' sShow 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
'EndMarshalOptionsVB
MarshalOptions Property Example (VB)

See Also
