Results 1 to 12 of 12

Thread: How to determine how many chapters fields exists in a hierarchical recordset?

  1. #1

    Thread Starter
    Member
    Join Date
    Oct 2021
    Posts
    43

    Question How to determine how many chapters fields exists in a hierarchical recordset?

    Does anybody know how to traverse a hierararchical recordset (probally through recursion) and determine the total number of chapters fields (including, child, grandchild, ............, and so on).

    Unfortunately, I know what to do, but not how to do it. Any help would be appreciated.

    * - For those who don't know, a Chapter field is a sub recordeset included into the parent (base) recordset.
    Last edited by Laspbr; Jun 20th, 2024 at 03:01 PM.

  2. #2

    Thread Starter
    Member
    Join Date
    Oct 2021
    Posts
    43

    Re: How to determine how many chapters fields exists in a hierarchical recordset?

    After some research on the internet I came across with a VBNet code that does exactly what my enquire is requesting. Does anybody could help me to adapt it for a VB6 version?


    (The contents of text1):

    Me.TextBox1.Text = "SHAPE {SELECT * FROM CUSTOMERS} APPEND ({SELECT * FROM ORDERS} " & _
    "AS CustOrders RELATE CustomerID TO CustomerID)


    Dim cn As OleDbConnection
    Dim cmd As OleDbCommand
    Dim drCust As OleDbDataReader

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles Button1.Click
    cn = New OleDbConnection("Provider=MSDataShape;Data Provider=SQLOLEDB;" & _
    "Data Source=(local);Initial Catalog=Northwind;User ID=<username>;Password=<strong password>")
    cn.Open()
    cmd = New OleDbCommand(TextBox1.Text, cn)
    drCust = cmd.ExecuteReader()
    TextBox2.Clear()
    ListChapteredFields(drCust, 0)
    End Sub

    Private Sub ListChapteredFields(ByVal dr As OleDbDataReader, ByVal lngLevel As Long)
    Dim i As Integer
    Dim drOrders As OleDbDataReader
    dr.Read()
    For i = 0 To dr.FieldCount - 1
    LogText(Space(lngLevel * 3) & dr.GetName(i) & vbTab)
    'Looking for FieldType of System.Data.IDataReader
    If TypeOf dr(i) Is IDataReader Then
    drOrders = dr.GetValue(i)
    ListChapteredFields(drOrders, lngLevel + 1)
    End If
    Next
    End Sub

    Private Sub LogText(ByVal strLine As String)
    If TextBox2.Text = "" Then
    TextBox2.Text = strLine
    Else
    TextBox2.Text = TextBox2.Text & vbCrLf & strLine
    End If
    End Sub

  3. #3
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,075

    Re: How to determine how many chapters fields exists in a hierarchical recordset?

    try this and see if the results are as you desire
    you need to add a MSHGrid and a Commandbutton to a Form

    Code:
    Option Explicit
    
    
    Dim adoConnection As ADODB.Connection
    Dim rs As ADODB.Recordset
    
    
    Private Sub Command1_Click()
    Dim strSql As String
    
    '      strSql = "SHAPE {SELECT * FROM CUSTOMERS} APPEND ({SELECT * FROM ORDERS} " & _
    '          "AS CustOrders RELATE CustomerID TO CustomerID)"
    
          strSql = "SHAPE {SELECT CustomerID, CompanyName FROM CUSTOMERS} APPEND ({SELECT * FROM ORDERS} " & _
              "AS CustOrders RELATE CustomerID TO CustomerID)"
    
    
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.Open strSql, adoConnection, adOpenForwardOnly, adLockOptimistic
    
          Set MSHFlexGrid1.DataSource = rs
        MSHFlexGrid1.CollapseAll
    
    rs.Close
    End Sub
    
    Private Sub Form_Load()
      If (Not openTheDatabase()) Then
         MsgBox "Database error..."
        Exit Sub
    End If
      MsgBox "Database open..."
    End Sub
    
    
    
    Public Function openTheDatabase() As Boolean
    Dim sConnectionString As String
    On Error GoTo dbError
    '-- New connection --
    Set adoConnection = New ADODB.Connection
    With adoConnection
             .CursorLocation = adUseClient
             .Mode = adModeShareDenyNone
             .Provider = "MSDataShape.1"
             .Properties("Data Provider") = "Microsoft.Jet.OLEDB.4.0"
             .Properties("Data Source") = "E:\Northwind1.mdb"
    End With
    
    adoConnection.Open sConnectionString
    openTheDatabase = True
    Exit Function
    dbError:
    MsgBox (Err.Description)
    openTheDatabase = False
    End Function
    here a Image of the results
    Name:  MSHGrid.jpg
Views: 61
Size:  29.8 KB
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  4. #4

    Thread Starter
    Member
    Join Date
    Oct 2021
    Posts
    43

    Re: How to determine how many chapters fields exists in a hierarchical recordset?

    To CrisE: First of all, I would like to thank you for your reply. The code that you provided is a good example on how to deal with a hierarchical recordset attached to MSHFlexGrid. Unfortunately, it does not solve my problem. I need to know how many chapters fields exists into the parent recordset. I am trying to translate the the VBnet code above to a VB6 version. As you can see, the code can furnish both the name of the chapter fields and the total number of them. It's exactly what I need.
    Last edited by Laspbr; Jun 23rd, 2024 at 08:38 AM.

  5. #5
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,075

    Re: How to determine how many chapters fields exists in a hierarchical recordset?

    the .NET Code just returns the Fieldnames of the Tables? so what do you mean with ..how many chapters fields exists... ?
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  6. #6

    Thread Starter
    Member
    Join Date
    Oct 2021
    Posts
    43

    Re: How to determine how many chapters fields exists in a hierarchical recordset?

    To ChrisE: the VBNet code above do not return the field names of the the tables. If you pay a closer look to it you will realize that it opens a recordset, read it and when it gets to a chapter field (a field that holds an entire sub recordset) it gets the name of this field(chapter) and adds to a counter the number of chapters fields found. I have learned that regarding to VB6 the type of a chapter field is the constant number 136. I am trying to translate the VBNet code to a VB6 version. When I get a draft code of this version I will post it to get further help.

  7. #7
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,314

    Re: How to determine how many chapters fields exists in a hierarchical recordset?

    In 'your' northwind database, which fields are your 'chapter fields'. I'm at a loss as to what they are....
    Sam I am (as well as Confused at times).

  8. #8

    Thread Starter
    Member
    Join Date
    Oct 2021
    Posts
    43

    Re: How to determine how many chapters fields exists in a hierarchical recordset?

    To Sam: First of all, I would like to thank you for your reply.

    Sam I would like to clarify to you that a chapter field does not lie in the database, it is created at the outcome recordset through a shape statement. In this especific case the chapter field is CustOrders (look the shape statement in the VBnet code above in the text1 contents section. Later today I will post a rough version (Vb6) of the code above. Wait please.

  9. #9
    Hyperactive Member
    Join Date
    Jan 2018
    Posts
    286

    Re: How to determine how many chapters fields exists in a hierarchical recordset?

    It doesn't look too tricky as long as data shaping doesn't allow circular references.

    Just enumerate your recordset fields recursively and check for ADODB.DataTypeEnum of adChapter.

  10. #10

    Thread Starter
    Member
    Join Date
    Oct 2021
    Posts
    43

    Re: How to determine how many chapters fields exists in a hierarchical recordset?

    To aHenry: First of all I Would like to thank you for your reply. That's exactly what I did. After some frustrated tentatives, due to recordset.eof clause I just found out that it is not necessary in this context. I am going to post a functional VB6 version within few moments.

  11. #11

    Thread Starter
    Member
    Join Date
    Oct 2021
    Posts
    43

    Re: How to determine how many chapters fields exists in a hierarchical recordset?

    ' Developed by: Luiz Antonio S. Pereira (LaspBR) June 24/2024.
    ' Location: Santos/SP - Brazil

    ' I have realized that maybe some readers of this post maybe not acquainted with hierarchical
    ' recordsets, thus I am trying to furnish through comments the most amount of information I am able
    ' to provide.

    'Library References:

    'Microsoft Activex Data Objects 2.8 Library
    '==========================================

    Option Explicit

    Option Base 1

    ' Indicate the depth from parent recordset (Parent recordset = 0 (top level in the hierarchy),
    ' each level subsequent to "0" indicates a Chapter Field
    Dim Level As Integer


    Dim Counter As Integer
    Dim FieldIndex As Integer
    Dim SubscriptIndex As Integer

    Dim ChapterFieldsName() 'Dynamic array to hold the names of each Chapter field
    Dim SHPSQL As String ' The Shape Language statement
    Dim objConn As ADODB.Connection
    Dim ChapterFieldCounter As Integer
    Dim HierarchicalRecordSet As ADODB.Recordset

    Private Sub Form_Load()

    Text1.Text = ""

    SHPSQL = "SHAPE TABLE Customers APPEND ((SHAPE TABLE Orders APPEND ({SELECT OrderID, ProductID, Quantity, UnitPrice, Discount, ((UnitPrice * Quantity * (1 - Discount) / 100) * 100) As TotalUnitPrice FROM [Order Details];} RELATE OrderID TO OrderID) AS cptOrderDetails, SUM(cptOrderDetails.TotalUnitPrice) AS TotalOrderPrice) RELATE CustomerID TO CustomerID) AS cptOrders"

    '==========================================================================
    ' The outcome recordset from this shape statement is:
    '
    ' The parent(base) recordset: "Customers"
    ' The Child sub recordset: "cptOrders"
    ' The GrandChild sub recordset "cptOrderDetails"
    ' A dynamic calculation field "TotalUnitPrice" appended to cptOrders
    ' A dynamic calculation field "TotalOrderPrice" appended to cptOrderDetails
    '===========================================================================

    Text1.Text = SHPSQL

    End Sub

    Private Sub Command1_Click()

    On Error GoTo ErrorHandler_01

    'Note: I am using SQLServer 2008 R2 as my Back-end Database.
    'If necessary change connection string to suit your back-end Database,
    'but keep the provider as being MSDataShape.1

    Level = 0
    FieldIndex = 0

    Set objConn = New ADODB.Connection
    objConn.ConnectionString = "Provider=MSDataShape.1;Extended Properties='';Initial File Name='';Server SPN='';Persist Security Info=False;Data Source=(local);Integrated Security=SSPI;Initial Catalog=NORTHWIND;Data Provider=SQLNCLI10"
    objConn.Open

    Set HierarchicalRecordSet = New ADODB.Recordset
    HierarchicalRecordSet.Open SHPSQL, objConn, adOpenForwardOnly, adLockReadOnly, adCmdText

    'A recursive function that traverses the entire recordset (Parent, Child and GrandChild)
    'and gets the name of each Chapter Fields as well as Total number of them

    Call ListChaptersFields(HierarchicalRecordSet, Level)

    MsgBox "There are " & Level & " Chapters Fields in the current Hierarchical Recordset:"

    Exit Sub

    ErrorHandler_01:

    Select Case Err.Number

    Case Is = 0

    Case Else

    MsgBox "Error: " & Err.Number & vbCrLf & vbCrLf & "Description: " & Err.Description

    End Select

    End Sub


    Private Function ListChaptersFields(ByVal objRecordSet As ADODB.Recordset, ByVal LevelIndex)

    On Error GoTo ErrorHandler_02

    For FieldIndex = 0 To objRecordSet.Fields.Count - 1

    If objRecordSet(FieldIndex).Type = adChapter Then

    LevelIndex = LevelIndex + 1

    ReDim Preserve ChapterFieldsName(LevelIndex)
    ChapterFieldsName(LevelIndex) = objRecordSet(FieldIndex).Name

    MsgBox "Chapter Field found: " & vbCrLf & vbCrLf & ChapterFieldsName(LevelIndex)

    'The next statements access the chapter fields
    Dim objSubRecordset As ADODB.Recordset
    Set objSubRecordset = objRecordSet(FieldIndex).Value

    Level = LevelIndex

    Call ListChaptersFields(objSubRecordset, LevelIndex)

    Exit For

    End If

    Next

    Exit Function

    ErrorHandler_02:

    Select Case Err.Number

    Case Is = 0

    Case Else

    MsgBox "Error: " & Err.Number & vbCrLf & vbCrLf & "Description: " & Err.Description

    End Select

    End Function
    Attached Files Attached Files
    Last edited by Laspbr; Jun 25th, 2024 at 09:52 AM.

  12. #12

    Thread Starter
    Member
    Join Date
    Oct 2021
    Posts
    43

    Re: How to determine how many chapters fields exists in a hierarchical recordset?

    Please note: I still have to test with more in-depth shape language statements, but by now, with a hierarchy with 3 levels (level 0,1and 2) the program is working perfectly.
    I will only mark this post solved when I make those tests
    Last edited by Laspbr; Jun 25th, 2024 at 09:47 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width