PRB: Child Recordset Submits Change w/o Active Connection

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

SYMPTOMS

When the records in a child recordset of a hierarchical recordset are updated, the changes are committed to the database even when the ActiveConnection property of the child recordset is set to nothing. This problem does not affect the parent recordset. The parent recordset behaves correctly and does not issue the update to the database until the ActiveConnection is set to a valid connection.

NOTE: This problem does not occur if the hierarchical recordset is passed out-of-process.

RESOLUTION

Currently, the only workaround to this problem is to use the lock type of adLockBatchOptimistic and issue an UpdateBatch only when you are ready to commit the entries to the database. For local updates to the recordset you would use the Update method.

STATUS

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

MORE INFORMATION

To reproduce this problem, follow the steps below. Please note that this code assumes that you are using the pubs database that ships with SQL Server. If you are using a different database, then you will need to change the SHAPE query to something that will return data from your database:

1. Start a new Visual Basic 6.0 standard EXE project and add a reference to the Microsoft ActiveX Data Objects 2.0 Library.



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




    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim rsChild As ADODB.Recordset
    Dim SQL As String
    Dim Connect As String
    Dim i As Integer
    'This should be modified to point to your database        
    Connect = "Provider=MSDataShape;Driver={SQL Server};Database=pubs;Server=yourserver;UID=sa;PWD=;"
    
    Set cn = New ADODB.Connection
    With cn
        .ConnectionString = Connect
        .ConnectionTimeout = 15
        .CursorLocation = adUseClient
        .Open
    End With
    
    SQL = "SHAPE {SELECT pub_id, pub_name FROM publishers} " & _
        "APPEND ({SELECT title_id, title, price, pub_id FROM titles} " & _
        "AS TitlesRS RELATE pub_id TO pub_id)"

    Set rs = New ADODB.Recordset
    With rs
        .CursorLocation = adUseClient
        .Open SQL, cn, adOpenStatic, adLockBatchOptimistic
        Set .ActiveConnection = Nothing
    End With
    cn.Close
   
    'Get the child recordset
    For i = 0 To rs.Fields.Count - 1
        If (rs.Fields(i).Type = adChapter) Then
            Set rsChild = New ADODB.Recordset
            Set rsChild = rs.Fields(i).Value
                    
        End If
    Next i
    
    rs(1).Value = "TURTLE"
    rsChild(1).Value = "TURTLE"
    
    'The active connection is set to nothing on both of these objects so
    'this should not be updated into the database ... the parent behaves
    'correctly but the child does not.
    rs.UpdateBatch
    rsChild.UpdateBatch
    
    rsChild.Close
    rs.Close 

3. Run the code (you may have to change the connection info). Note that even though the recordset is disconnected, the changes are committed to the database when the UpdateBatch is issued against the child recordset. To verify this, you can use the ISQL or Query Analyzer tool (or a similar tool depending on the database you are using) to query the underlying tables and see that the changes for the child recordset have been written to the database.

Additional query words:

Keywords          : kbADO200bug kbDatabase kbGrpVBDB 
Version           : WINDOWS:2.0,2.1,2.1 SP1
Platform          : WINDOWS 
Issue type        : kbprb 

Last Reviewed: May 17, 1999