HOWTO: Produce a Manufactured Hierarchical Recordset Base on an Existing Recordset

• Microsoft Visual Basic Professional and Enterprise Editions for Windows, version 6.0
• ActiveX Data Objects (ADO), versions 2.1, 2.1 SP1, 2.1 SP2

SUMMARY

Some corporate policies only allow read-access to database tables with all updates being performed through stored procedures. If you want to bind a data control, such as the Hierarchical Flex Grid or List View control to a hierarchical recordset, this can pose a problem keeping the recordset in sync with the data if you allow adding, deleting, and modifying records, but you cannot edit it directly through the hierarchical recordset.

MORE INFORMATION

If you want to keep a hierarchical recordset in sync with the server, but you cannot add records via the hierarchical recordset, there are a number of workarounds:

1. You can requery the recordset. This has the disadvantage of poor performance.



2. You can disconnect the recordset and perform additions and other modifications in parallel with stored procedures. The problem is that certain fields are read-only, such as Identity and TimeStamp, and you cannot set their value. This becomes problematic if the Identity column in the parent record is used to link the child recordset(s) or is supplied to the stored procedures to identify the record to be updated, or if the TimeStamp column is passed to the stored procedure to determine if someone else has editied the record in the mean-time. There are a number of workarounds:
   
   
   1. You can copy the data to a local database, such as a Jet MDB, and use datatypes that do not make the field read-only. You can then update the hierarchical recordset in parallel to using stored procedures and keep the data in sync.
   
   
   
   2. You can manufacture a hierarchical recordset based on the original and copy the data into it. The fields will all be updatable and you can update the recordset in parallel to using stored procedures.
   
   
   

1. It opens a hierarchical recordset.



2. It calls GenerateShapeSQL to provide a SHAPE statement to open an equivalently structured hierarchical recordset that is not based on any provider and hence does not have any read-only fields.



3. It calls CopyData to copy the data from the original recordset into the manufactured recordset.



4. It closes the original recordset.



5. It binds the Hierarchical Flex Grid to the manufactured recordset.

Sample Application

http://www.microsoft.com/mcsp/

http://www.microsoft.com/support/supportnet/overview/overview.asp

1. Open a new project in Microsoft Visual Basic 6.



2. Using the Projects | Components menu, select the "Microsoft Hierarchical FlexGrid 6.0 (OLEDB)" control and place it on the form. Make it fairly large.



3. Using the Projects | References menu, select "Microsoft ActiveX Data Objects 2.1 Library".



4. Add the following code to the form module:

   
   Option Explicit
   
   Dim rsManufactured As ADODB.Recordset
   
   Private Sub Form_Load()
   Dim cn As ADODB.Connection, rsSource As ADODB.Recordset, SQL As String
   '
   ' Get original data
   '
     Set cn = New ADODB.Connection
     Set rsSource = New ADODB.Recordset
     cn.Open "Provider=MSDATASHAPE;Data Provider=SQLOLEDB;Server=MyServer;uid=xxx;pwd=yyy;database=northwind"
     rsSource.CursorLocation = adUseClient
     rsSource.Open "SHAPE {SELECT * FROM Customers} APPEND ({SELECT * FROM Orders} RELATE CustomerID TO CustomerID)", _
                   cn, adOpenStatic, adLockOptimistic, adCmdText
   '
   ' Generate SHAPE statement for manufactured recordset
   '
     SQL = GenerateShapeSQL(rsSource)
   '
   ' Build manufactured recordset
   '
     Set cn = New ADODB.Connection
     Set rsManufactured = New ADODB.Recordset
     cn.Open "Provider=MSDATASHAPE;Data Provider=none"
     rsManufactured.Open SQL, cn, adOpenStatic, adLockOptimistic
     CopyData rsSource, rsManufactured
     rsSource.Close
     Set rsSource = Nothing
     Set cn = Nothing
   '
   ' Bind manufactured recordset
   '
     Set MSHFlexGrid1.DataSource = rsManufactured
   End Sub
   
   Private Function GenerateShapeSQL(rs As ADODB.Recordset, Optional ByVal Level As Long = 0) As String
   Dim FieldList As String, SQL As String, I As Long, F As ADODB.Field
   '
   ' SQL Prefix
   '
     If Level = 0 Then
       SQL = "SHAPE APPEND "
     Else
       SQL = ", ((SHAPE APPEND "
     End If
   '
   ' Add fields
   '
     FieldList = ""
     For Each F In rs.Fields
       If F.Type = adChapter Then
         FieldList = FieldList & GenerateShapeSQL(F.Value, Level + 1) & ") AS [" & F.Name & "] " & _
                     GenerateRelateClause(F, rs) & ")"
       Else
         FieldList = FieldList & ", New " & TypeName(F) & " AS [" & F.Name & "]"
       End If
     Next F
   '
   ' SQL Suffix
   '
     GenerateShapeSQL = SQL & Mid$(FieldList, 3)
   End Function
   
   Private Function GenerateRelateClause(F As ADODB.Field, rsParent As ADODB.Recordset) As String
   Dim I As Long, FieldInfo() As Byte, FieldList As String, rsChild As ADODB.Recordset
     FieldInfo = F.Properties("RELATIONCONDITIONS").Value
     Set rsChild = F.Value
     For I = 0 To UBound(FieldInfo) - 11 Step 12
       FieldList = FieldList & ", [" & rsParent(FieldInfo(I) - 1).Name & "] To [" & rsChild(FieldInfo(I + 4) - 1).Name & "]"
     Next I
     GenerateRelateClause = "RELATE" & Mid$(FieldList, 2)
   End Function
   
   Private Function TypeName(ByVal F As ADODB.Field) As String
   '
   ' Returns the text name of the field type and optional size specification
   '
     Select Case F.Type
       Case adBinary
         TypeName = "adBinary(" & F.DefinedSize & ")"
       Case adBoolean
         TypeName = "adBoolean"
       Case adChar
         TypeName = "adChar"
       Case adCurrency
         TypeName = "adCurrency"
       Case adDBTimeStamp
         TypeName = "adDBTimeStamp"
       Case adDouble
         TypeName = "adDouble"
       Case adInteger
         TypeName = "adInteger"
       Case adLongVarBinary
         TypeName = "adLongVarBinary"
       Case adLongVarChar
         TypeName = "adLongVarChar"
       Case adLongVarWChar
         TypeName = "adLongVarWChar"
       Case adNumeric
         TypeName = "adNumeric"
       Case adSingle
         TypeName = "adSingle"
       Case adSmallInt
         TypeName = "adSmallInt"
       Case adTinyInt
         TypeName = "adTinyInt"
       Case adUnsignedTinyInt
         TypeName = "adUnsignedTinyInt"
       Case adVarBinary
         TypeName = "adVarBinary (" & F.DefinedSize & ")"
       Case adVarChar
         TypeName = "adVarChar (" & F.DefinedSize & ")"
       Case adVarWChar
         TypeName = "adVarWChar(" & F.DefinedSize & ")"
       Case adWChar
         TypeName = "adWChar(" & F.DefinedSize & ")"
       Case Else
         MsgBox "UnKnown Field Type: " & F.Name & ", " & F.Type
     End Select
   End Function
   
   Private Sub CopyData(rsSource As ADODB.Recordset, rsDest As ADODB.Recordset)
   '
   ' Copies all data from rsSource to rsDest
   '
   Dim I As Long
     Do While Not rsSource.EOF
       ' add record copy standard field values
       rsDest.AddNew
       For I = 0 To rsSource.Fields.Count - 1
         If rsSource(I).Type <> adChapter Then
           rsDest(I).Value = rsSource(I).Value
         End If
       Next I
       rsDest.Update
       ' call CopyData recursively for each child recordset
       For I = 0 To rsSource.Fields.Count - 1
         If rsSource(I).Type = adChapter Then CopyData rsSource(I).Value, rsDest(I).Value
       Next I
       rsSource.MoveNext
     Loop
   End Sub 




5. Modify the connect string in the Form_Load procedure and run the project. The customers data will be displayed in the grid. If you scroll to the right, you will be able to see the order records for each customer.

© Microsoft Corporation 1999, All Rights Reserved.
Contributions by Malcolm Stewart, Microsoft Corporation

Additional query words:

Keywords : kbADO kbADO210 kbMDAC kbVBp600 kbGrpVBDB kbGrpMDAC kbADO210sp2
Version : WINDOWS:2.1,2.1 SP1,2.1 SP2,6.0
Platform : WINDOWS
Issue type : kbhowto