Microsoft KB Archive/229654

= PRB: ADO adMarshalModified Causes Problem with Late Binding =

Article ID: 229654

Article Last Modified on 11/3/2003

-

APPLIES TO


 * Microsoft ActiveX Data Objects 1.5
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q229654



SYMPTOMS
Using disconnected recordsets in ADO, it is possible to encounter a problem when passing recordsets out of process to a custom business object for updating. The problem occurs when you use the adMarshalModifiedOnly option before you pass the recordset. If the client code uses late binding to the custom business object with the adMarshalModifiedOnly property, the recordset will be passed to the business object ByVal. In addition, it will be passed back to the client and overwrite the original recordset making any changes to the recordset by the business object visible to the client. Consequently, the client will only see the records that were marshalled to the business object, for example, only the modified rows. This problem does not occur if early binding to the business object.



RESOLUTION
There are two possible workarounds to this problem:
 * 1) Do not use the adMarshalModifiedOnly option when you send the recordset back to the server for updating.
 * 2) Use early binding when you connect to the business object.

These workarounds are demonstrated in the below code.



STATUS
This is by design.

When you use late-binding, IDispatch, the Recordset will always be passed by reference.



MORE INFORMATION
Below are the steps necessary to reproduce this problem. It is assumed that the reader will be running both the client and server components on the same machine. This code also makes use of the SQL Server pubs database. You may need to alter the connection string and database query for your specific environment.  Create a new Visual Basic 6.0 Standard EXE and add a reference to the Microsoft ActiveX Data Objects Library.  Place a CommandButton on the form and place the following code behind the CommandButton.

Note You must change User ID= to the appropriate username before you run this code. Make sure that the corrected UID has the appropriate permissions to perform the required operations on the specified database. Private Sub Command1_Click Dim rs As ADODB.Recordset Dim x As Long Const Connect = "Provider=MSDASQL.1;Persist Security Info=False;User ID= ;Data Source=YOUR_DSN;Initial Catalog=pubs" Const Query = "SELECT * FROM AUTHORS" Debug.Print "EARLY BINDING-" Dim obj As ADOMarshalTest.Class1 Set obj = New ADOMarshalTest.Class1 Set rs = obj.Get_Disconnected_RS(Connect, Query) rs.MoveFirst rs(1).Value = "mike" rs(2).Value = "ruth" rs.Update rs.MarshalOptions = adMarshalModifiedOnly Debug.Print "Recordset Count Before Update: " & rs.RecordCount If (obj.UpdateBatchX(Connect, rs) <> 0) Then Debug.Print "ERROR ON UPDATE" Exit Sub End If   Debug.Print "Recordset Count After Update: " & rs.RecordCount Set obj = Nothing Debug.Print "LATE BINDING-" Dim obj2 As Object Set obj2 = CreateObject("ADOMarshalTest.Class1") Set rs = obj2.Get_Disconnected_RS(Connect, Query) rs.MoveFirst rs(1).Value = "Mike" rs(2).Value = "Ruth" rs.Update 'Comment this line to preserve the recordset on the client rs.MarshalOptions = adMarshalModifiedOnly Debug.Print "Recordset Count Before Update: " & rs.RecordCount If (obj2.UpdateBatchX(Connect, rs) <> 0) Then Debug.Print "ERROR ON UPDATE" Exit Sub End If   Debug.Print "Recordset Count After Update: " & rs.RecordCount

End Sub  Create a new Visual Basic ActiveX EXE project and add a reference to the Microsoft ActiveX Data Objects Library. Rename the project name to ADOMarshalTest.  Add the following code to the default class (Class1)in the project: Option Explicit Public Function UpdateBatchX(ByVal szConnect As String, ByVal rs As ADODB.Recordset) As Long

On Error GoTo errhandler Dim cn As ADODB.Connection Set cn = New ADODB.Connection With cn       .ConnectionString = szConnect .ConnectionTimeout = 15 .CursorLocation = adUseClient .Open End With With rs       .ActiveConnection = cn        .UpdateBatch adAffectAllChapters End With UpdateBatchX = 0 Exit Function

errhandler: UpdateBatchX = Err.Number End Function

Public Function Get_Disconnected_RS(ByVal szConnect As String, ByVal SQL As String) As ADODB.Recordset

On Error GoTo errhandler

Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Set cn = New ADODB.Connection With cn       .ConnectionString = szConnect .ConnectionTimeout = 15 .CursorLocation = adUseClient .Open End With Set rs = CreateObject("ADODB.Recordset") rs.Open SQL, cn, adOpenStatic, adLockBatchOptimistic Set rs.ActiveConnection = Nothing Set Get_Disconnected_RS = rs   Exit Function

errhandler: Set rs = Nothing Err.Raise Err.Number, "mrADOComponent..Get_Disconnected_RS", Err.Description End Function  Compile this project into an ActiveX EXE. Return to the first project and set a reference to the newly-created ActiveX component (ADOMarshalTest).</li> Run the client code and note that when late binding is used after the recordset has returned from the server, only the modified records are visible. This problem can be avoided by commenting the line of code that sets the adMarshalModifiedOnly option.</li></ol>

<div class="references_section">