Microsoft KB Archive/213856

= INFO: Using Disconnected Hierarchical Recordsets =

Article ID: 213856

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft Data Access Components 2.0
 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.7
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition

-



This article was previously published under Q213856



SUMMARY
Hierarchical recordsets can be used as an alternative to JOIN and GROUP BY syntax when you need to access parent-child and summary data. These recordsets can be created by using the SHAPE provider, MSDataShape. Many applications now use a 3-tier architecture and might find it useful to create these recordsets, disconnect them from their data source, and then pass them back to the client. This article discusses what is necessary to pass disconnected hierarchical recordsets out-of-process.

This article assumes that you are already familiar with 3-tiered applications, hierarchical recordsets, and passing ADO recordsets as parameters to and from functions. For more information on these topics, please see the following articles on the Microsoft Knowledge Base:

189657 HOWTO: Use the ADO SHAPE Command

186342 HOWTO: Create a 3-Tier App using VB, MTS and SQL Server

182442 FILE: Adomts.exe Shows Using ADO w/ an MTS Component via DCOM

Working with recordsets based on SHAPE queries in 3-tier applications is similar to working with regular recordsets. However, there are some differences. Below are two of these differences that are specific to working with disconnect hierarchical recordsets:   Recordsets based on parameterized SHAPE queries cannot be passed out-of-process. Here is an example of a parameterized SHAPE query: SHAPE {SELECT * FROM Employees WHERE LastName='Davolio'} APPEND ({SELECT * FROM Orders WHERE EmployeeID = ?} AS EmpOrders     RELATE EmployeeID TO PARAMETER 0) The reason these recordsets cannot be passed out-of-process is that parameterized SHAPE commands do not retrieve all the rows at creation-time. The child records are fetched on demand from the data source as the user moves through the parent records. If these recordsets were marshalled out-of-process there would not be enough information marshalled for the child records to be retrieved.  When sending hierarchical recordsets back to the server for updating, you need to manually loop through all of the child recordsets and issue an UpdateBatch for each one. While looping through the child recordsets, be sure to set the ActiveConnection property of each of the children recodsets to a valid connection object before issuing the UpdateBatch method. If you fail to set the ActiveConnection to a valid connection before issuing the UpdateBatch, no updates are sent to the database.

Below is an example of a function that can be used to update hierarchical recordsets on the server side. This function accepts a hierarchical recordset as an incoming parameter and uses recursive calls to loop through all of the child recordsets and send the updates to the server.

Steps to Reproduce Behavior
 Start Visual Basic. Choose the default project type. Set a project reference to the Microsoft ActiveX Data Objects 2.x Library. Place a CommandButton on the form.</li>  Paste the following code into the Code Window:

Note You must change Server=<ServerName>, Uid=, and Pwd= to the appropriate server name, username, and password before you run this code. Make sure that the changed uid has the appropriate permissions to perform the required operations on the specified database. Dim m_cn As ADODB.Connection

Private Sub Command1_Click Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset Dim rsChapter As Recordset cnn.CursorLocation = adUseClient cnn.Open "Provider=MSDataShape;Data Provider=sqloledb;" _ & "Server=<ServerName>;uid= ;pwd= ;Database=pubs;" rst.Open "SHAPE {select * from authors}APPEND " & _ "({select * from titleauthor} AS chapter RELATE au_id TO au_id)", _ cnn, adOpenStatic, adLockBatchOptimistic Set rst.ActiveConnection = Nothing rst.MoveFirst rst("au_lname") = "Smith" rst.Update If (UpdateBatchX_ALLChildren("Provider=MSDataShape; " _ & "Data Provider=sqloledb;Server=<ServerName>;" _ & "uid= ;pwd= ;Database=pubs;", rst) <> 0) Then _

Debug.Print Err.Number & " -- " & Err.Description Exit Sub End If End Sub Public Function UpdateBatchX_ALLChildren(ByVal szConnect As String, _      ByVal rs As ADODB.Recordset) As Long

'On Error GoTo errhandler Dim rsChild As ADODB.Recordset Dim i As Integer 'Connect to the database .. m_cn is a module level ADODB connection if Not (szConnect = "") Then Set m_cn = CreateObject("ADODB.Connection") With m_cn .ConnectionString = szConnect .ConnectionTimeout = 15 .CursorLocation = adUseServer .Open End With End If   'Update each of the child recordsets ... call recursively to   'handle all of the children With rs       .ActiveConnection = m_cn .UpdateBatch adAffectAllChapters 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 UpdateBatchX_ALLChildren "", rsChild rsChild.Close Set rsChild = Nothing End If       Next i    End With 'Only close the connection on the original call If Not (szConnect = "") Then m_cn.Close Set m_cn = Nothing End If   UpdateBatchX_ALLChildren = 0 Exit Function

errhandler: UpdateBatchX_ALLChildren = Err.Number Err.Raise Err.Number, " UpdateBatchX ", Err.Description End Function </li></ol>

<div class="references_section">