PRB: ADO adMarshalModified Causes Problem with Late Binding

• ActiveX Data Objects (ADO), versions 1.5, 2.0, 2.1

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 using the adMarshalModifiedOnly option before passing 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 sending the recordset back to the server for updating.



2. Use early binding when connecting to the business object.


These workarounds are demonstrated in the below code.

STATUS

Microsoft is researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

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.

1. Create a new Visual Basic 6.0 Standard EXE and add a reference to the ADO 2.1 library.



2. Place a CommandButton on the form and place the following code behind the CommandButton:




Private Sub Command1_Click()
    
    Dim rs As ADODB.Recordset
    Dim x As Long
    Const Connect = "Provider=MSDASQL.1;Persist Security Info=False;User ID=sa;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 



3. Create a new Visual Basic ActiveX EXE project and add a reference to the ADO 2.1 library. Rename the project name to ADOMarshalTest.



4. 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 

5. Compile this project into an ActiveX EXE.



6. Return to the first project and set a reference to the newly-created ActiveX component (ADOMarshalTest).



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

REFERENCES

For more information, please see the following article in the Microsoft Knowledge Base:

Q190717 INFO: Disconnected Recordsets with ADO or RDS

Additional query words:

Keywords : kbADO kbDCOM kbMDAC kbRDS kbGrpVBDB kbGrpMDAC kbDSupport
Version : WINDOWS:1.5,2.0,2.1
Platform : WINDOWS
Issue type : kbprb