Results 1 to 5 of 5

Thread: [RESOLVED] help , treeview and sql group by

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2020
    Posts
    9

    Resolved [RESOLVED] help , treeview and sql group by

    hello
    i want to show Ado recordset fields on Treeview
    but 2 problems


    field1- country / field2- city
    Italy Rome
    Italy Napoli
    Italy Torino
    France Paris
    France Lyon
    ...

    Europe (root)
    I
    ı_____ Italy
    ______I_____ Rome
    ___________ Napoli
    ___________ Torino
    ı_____ France
    ______I_____ Paris
    ______ ____ Lyon

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim oCmd As ADODB.Command
    Dim strCon As String
    Dim strSql As String

    Dim MyDataFile As String
    Dim NameKey As String
    Dim countryName As String
    Dim cityKey As String
    Dim cityName As String


    MyDataFile = App.Path & "\DataFile" & "abc.mdb"
    strCon = "Provider = Microsoft.Jet.OLEDB.4.0;data source=" & MyDataFile & ""

    strSql = "SELECT * FROM COUNTRIES GROUP BY COUNTRY " --------------error cannot group selected with "*"
    ' or change it as select COUNTRY , MAX(CITY) AS CITY FROM COUNTRIES GROUP BY COUNTRY "

    Set cn = New ADODB.Connection
    cn.CursorLocation = adUseClient
    cn.Open strCon

    Set oCmd = New ADODB.Command
    Set oCmd.ActiveConnection = cn
    oCmd.CommandType = adCmdText

    Set rs = New ADODB.Recordset
    rs.Open strSql, cn, adOpenDynamic, adLockOptimistic

    TreeView1.Nodes.Clear
    TreeView1.LineStyle = tvwRootLines

    TreeView1.ImageList = ImageList1


    TreeView1.Nodes.Add , , "RootDB", "Europe", 3

    rs.MoveFirst

    Dim i As Integer
    i = 0

    Do Until rs.EOF


    i = i + 1
    countryName = rs.Fields("COUNTRY").Value
    NameKey = "N" & Str(i)

    TreeView1.Nodes.Add "RootDB", tvwChild, NameKey, countryName, 1



    cityName = rs.Fields("CITY").Value
    cityKey = "C" & Str(i)

    TreeView1.Nodes.Add NameKey, tvwChild, cityKey, cityName , 5


    rs.MoveNext

    Loop

    ' --------------------------------------and problem with loop results , what should i change ?
    Europe (root)
    I
    ı_____ Italy
    I_____ Rome
    ı_____ France
    I_____ Paris
    Last edited by coincollector; May 4th, 2021 at 11:19 AM.

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: help , treeview and sql group by

    First off... please use when posting code use [code][/code] tags around it... it preserves the formatting and makes it nicer to look at. You can also jsut paste the code in, select it, then click the # button in the toolbar above and it will put the tags around it for you.

    Secondly, correct, you cannot use a GROUP BY with * and you cannot use it without an aggregate function (min, max, avg, etc) (we you can, but it's not recommended, and if you aren't using an aggregate funciton, then you have to ask why are you using group by).

    Last, the reason everyhting ends up under hte root node is because that's where you add it.
    Code:
    Treeview.Nodes.Add
    By default has no context, so it adds it as a root node.

    What you should be doing is creating individual TreeViewNodes ... and adding them to the parent TreeViewNodes as appropriate... then adding that to your root node, then add that to your treeview....

    Something like this (untested):
    Code:
    Dim rootNode as TreeViewNode 
    Dim countryNode as TreeViewNode
    Dim cityNode as TreeViewNode
    
    set rootNode = new TreeViewNode
    rootNode.key = "RootDb"
    rootNode.Text = "Europe"
    
    set countryNode = new TreeViewNode
    countryNode.key = "Italy"
    countryNode.Text = "Italy"
    set cityNode = new TreeViewNode
    cityNode.key = "Rome"
    cityNode.Text = "Rome"
    countryNode.Nodes.Add cityNode
    set cityNode = new TreeViewNode
    cityNode.key = "Pisa"
    cityNode.Text = "Pisa"
    countryNode.Nodes.Add cityNode
    
    rootNode.Nodes.Add countryNode
    
    set countryNode = new TreeViewNode
    countryNode.key = "France"
    countryNode.Text = "France"
    set cityNode = new TreeViewNode
    cityNode.key = "Paris"
    cityNode.Text = "Paris"
    countryNode.Nodes.Add cityNode
    set cityNode = new TreeViewNode
    cityNode.key = "Nice"
    cityNode.Text = "Nice"
    countryNode.Nodes.Add cityNode
    
    rootNode.Nodes.Add countryNode
    
    TreeView1.Nodes.Add rootNode
    Can't guarantee it'll work as is, but it should get you going in the right direction.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    New Member
    Join Date
    Aug 2020
    Posts
    9

    Re: help , treeview and sql group by

    Thank you techgnome , yes you are right , i must use code tags .

    Code:
    Dim rootNode as TreeViewNode
    user-defined type not defined error. I use "Dim rootNode as Node"

    I need help with ado recordset code.

    I hope you understood what i want to tell. I have a table and 2 fields . There are more records in this table and i want to group country names and cities as child nodes. . I save new records to recordset and I must write the code ADO connection

    Name:  countries.png
Views: 167
Size:  10.5 KB

  4. #4

    Thread Starter
    New Member
    Join Date
    Aug 2020
    Posts
    9

    Re: help , treeview and sql group by

    hello , i found what i look for, thanks
    Code:
    Dim cn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim MyDataFile As String
    Dim oCmd As ADODB.Command
    Dim strCon As String
    Dim strSql As String
    Dim dbNode As Node
    
    
       MyDataFile = App.Path & "\DataFile\" & "coins.mdb"
       strCon = "Provider = Microsoft.Jet.OLEDB.4.0;data source=" & MyDataFile & ""
       strSql = "SELECT * FROM TABLE "
       
       Set cn = New ADODB.Connection
       cn.CursorLocation = adUseClient
       cn.Open strCon
    
       Set rst = New ADODB.Recordset
       rst.Open strSql, cn, adOpenDynamic, adLockOptimistic
    
    
       TreeView1.Nodes.Clear
       TreeView1.LineStyle = tvwRootLines
      
       TreeView1.ImageList = ImageList1
       
    
       Set dbNode = TreeView1.Nodes.Add(, , "RootDB", "COUNTRIES", 1)
     
        ' set root node of TreeView.
        
      
        Dim ParentName As String
        Dim FieldIndex As Integer
        Dim NodeName As String
        Dim NewNode As Node
    
      
    With rst
     
                Do Until .EOF
                    ParentName = vbNullString
                    For FieldIndex = 0 To .Fields.Count - 1
                        NodeName = ParentName & "|" & .Fields(FieldIndex).Value
                        On Error Resume Next
                        Set NewNode = TreeView1.Nodes.Item(NodeName)
                        If Err Then
                            On Error GoTo 0
                            If Len(ParentName) = 0 Then
                                Set NewNode = TreeView1.Nodes.Add(, _
                                                                  tvwNext, _
                                                                  NodeName, _
                                                                  .Fields(FieldIndex).Value, 2)
                            Else
                                Set NewNode = TreeView1.Nodes.Add(ParentName, _
                                                                  tvwChild, _
                                                                  NodeName, _
                                                                 .Fields(FieldIndex).Value, 3)
                            End If
                            NewNode.Expanded = True
                            NewNode.Sorted = True
                        Else
                            On Error GoTo 0
                        End If
                        ParentName = NodeName
                    Next
                    .MoveNext
                Loop
                .Close
       
    End With

  5. #5
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: [RESOLVED] help , treeview and sql group by

    You can also use ADO's Data Shaping Service with a "grouping" SHAPE command in order to create hierarchical Recordsets for this sort of thing.

    I'm not sure many people are even aware it exists but it has been covered in the documentation that comes with VB6 for quite a long time. It is certainly covered in the October 2001 MSDN Library CHM Help, the last edition supporting VB6.

    Code:
    Option Explicit
    
    Private Sub Form_Load()
        Dim RSEurope As ADODB.Recordset
        Dim RSChapters As ADODB.Recordset
        Dim FldCountry As ADODB.Field
        Dim FldCity As ADODB.Field
        Dim Europe As ComctlLib.Node
        Dim Country As ComctlLib.Node
    
        Set RSEurope = New ADODB.Recordset
        With RSEurope
            .CursorLocation = adUseClient
            .Open "SHAPE{SELECT [Country], [City] FROM [Europe]} AS [Rows] " _
                & "COMPUTE [Rows] AS [Chapters] BY [Country]", _
                  Module1.Connection, _
                  adOpenStatic, _
                  adLockReadOnly, _
                  adCmdText
            With MSHFlexGrid1
                Set .DataSource = RSEurope
                .ColWidth(0, 0) = 240
                .ColWidth(0, 1) = 0
            End With
    
            Set FldCountry = .Fields("Country")
            Set RSChapters = .Fields("Chapters").Value
            Set FldCity = RSChapters.Fields("City")
            Set Europe = TreeView1.Nodes.Add(, , , "Europe", "Europe")
            Europe.Expanded = True
            Do Until .EOF
                Set Country = TreeView1.Nodes.Add(Europe, tvwChild, , FldCountry.Value, "Country")
                Country.Expanded = True
                With RSChapters
                    Do Until .EOF
                        TreeView1.Nodes.Add Country, tvwChild, , FldCity.Value, "City"
                        .MoveNext
                    Loop
                End With
                .MoveNext
            Loop
    
            .Close
        End With
    End Sub
    
    Private Sub Form_Resize()
        If WindowState <> vbMinimized Then
            With MSHFlexGrid1
                .Move 0, 0, ScaleWidth, 2100
                TreeView1.Move 0, .Height, ScaleWidth, ScaleHeight - .Height
            End With
        End If
    End Sub
    Displayed automatically within an MSHFlexGrid
    and manually within a TreeView control

    Name:  sshot.png
Views: 115
Size:  3.2 KB
    Attached Files Attached Files

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