MarshalOptions Property Example (ADO)


		Contents Index Topic Contents

MarshalOptions Property Example

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 Employee table.
	strCnn = "driver={SQL Server};server=srv;" & _
		"uid=sa;pwd=;database=pubs"
	Set rstEmployees = New ADODB.Recordset
	rstEmployees.CursorType = adOpenKeyset
	rstEmployees.LockType = adLockOptimistic
	rstEmployees.CursorLocation = adUseClient
	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?"
	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