PRB: ADO adMarshalModified Causes Problem with Late Binding

ID: Q229654


The information in this article applies to:


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.


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


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

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


  5. Add the following code to the default class (Class1)in the project:


  6. 
    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 
  7. Compile this project into an ActiveX EXE.


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


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

Last Reviewed: June 22, 1999