How to create a tree view from MySQL as database using VB.NET treeview

Question

I been try to fix this problem, in code but until now i don't know how to get the correct result this is my table

+--------+-----------+----------+
| id     | name      | node_id  |
+--------+-----------+-----------
|      1 | Color     |     NULL |
|      2 | Black     |        1 |
|      3 | Red       |        1 |
|      4 | White     |        1 |
|      5 | Animal    |     NULL |
|      6 | Dog       |        5 |
|      7 | Cat       |        5 |
+--------+-----------+----------+

I want to display this in my Form in Tree view like this, but it was messed up as I expected. The Color, Animal is the Parent node, and Black , White, Dog, Cat is the child node

Color
   |-Black
   |-Red
Animal
   |-Dog
   |-Cat

This is the code for the tree view using VB.NET:

'select the min Id as the starting of node
Dim SQL As String = "select min(node_id), name from mytable;"
Dim cmd As MySqlCommand = New MySqlCommand(SQL, cn)
cmd.ExecuteNonQuery()
Dim reader As MySqlDataReader = cmd.ExecuteReader()
Dim id As Integer
Dim name As String
Dim node1, node2 As TreeNode

While reader.Read
    id = reader.GetString(0)
    name = reader.GetString(1)
End While

reader.Close()
cmd.CommandText = "select id, name from mytable"
cmd.ExecuteNonQuery()
Dim ds As New DataSet
Dim da As New MySqlDataAdapter
'add the first find of min id = name
node1 = TreeView1.Nodes.Add(id, name)
If node1 is Nothing Then
    node2 = TreeView1.Nodes.Add(id, name)
Else
    node2 = node1.Nodes.Add(id, name)
End If
Dim dr = cmd.ExecuteReader

Do While dr.Read()
    TreeView1.Nodes.Add(dr("id"), dr("name"))
    TreeView1.Nodes(node2.Level).Nodes.Add(dr("id"), dr("name"))
Loop
dr.Close()

If there is a parent-child relationship (Color - Red) why isnt the database set up that way? — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Jun 3, 2016 at 14:18

Ňɏssa Pøngjǣrdenlarp · Accepted Answer · 2016-06-04 14:42:09Z

2

Rather than rows referencing other rows in the same table to identify the parent and then multiple queries to unravel it, your db ought to have 2 tables since there are 2 actors: parent nodes/items and the child item/nodes related to each of them. However, SO is more about handing out fish, than fishing rods and since we can join the table to itself for the same result, this should work.

Dim SQL = <sql>SELECT p.Name As Parent, tvnode.Name
                FROM tvnode p
                LEFT JOIN tvnode 
                ON tvnode.ParentId = p.Id
                WHERE p.ParentId Is null  
                ORDER BY p.Name;
          </sql>.Value

Using dbcon As New MySqlConnection(MySQLConnStr)
    Using cmd As New MySqlCommand(SQL, dbcon)

        dbcon.Open()
        Using rdr As MySqlDataReader = cmd.ExecuteReader
            Dim parentName As String = ""
            While rdr.Read
                Dim nodeName = rdr.GetString("Parent")
                ' add new parent
                If nodeName <> parentName Then
                    tv1.Nodes.Add(nodeName, nodeName)
                    parentName = nodeName
                End If
                ' add non null child names
                If rdr.IsDBNull(rdr.GetOrdinal("Name")) = False Then
                    Dim thisname = rdr.GetString("Name")
                    tv1.Nodes(parentName).Nodes.Add(thisname, thisname)
                End If
            End While
        End Using

    End Using
End Using

I used an XML literal for the SQL just to avoid scroll, dont let that throw you. The SQL joins tvnodes to itself so it can provide Parent name (Animal, Color) using the p alias; and provide the child names for each of those in one query.

The SQL orders the results by ParentName so that whenever that value changes, you know you need to add a new root/parent node. So, there is no need to search, check or run a new query; no need for a DataSet, DataAdapter or DataGridView. By putting the data in order, you can just loop thru the reader.

This particular JOIN will provide parent names even when there are no children ("Snack"), but this requires that the code check that each child name is not DbNull. The code doesn't need the Ids so they are left out.

+-----------------------
| Parent | Name
| Animal | Cat  
| Animal | Dog  
| ...
| Color  | Red  
| ...  
| Color  | Purple  
| Fish   | Perch  
| Fish   | Bass  
|...  
| Snack  | NULL

Results:

To learn more about SQL this SQL Tutorial is very good.

edited Jun 4, 2016 at 14:42

answered Jun 3, 2016 at 16:21

Ňɏssa Pøngjǣrdenlarp

39k12 gold badges66 silver badges193 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Max Over a year ago

Hi thanks for the time to review the code and in the SQL Select the 'tvnode' is the name of the column or the table? I'm not really good in mysql select and the 'p' is the alias of my table?

Ňɏssa Pøngjǣrdenlarp Over a year ago

I used a table tvNode with columns {id, Name, ParentID}, as explained it uses p as the alias for one copy of the table.

Max Over a year ago

Hi plutonix, I would like to say thank you :) I try your code and it works like a charm, By the way let me ask can i get the 'Id' from the table if i selected the node from the treeview.

Ňɏssa Pøngjǣrdenlarp Over a year ago

Good to hear - just add tvnode.Id (or whatever it is) to the SQL, then figure out where to store it. The Tag property maybe? That will give you the Child Ids.

Robin Mackenzie · Accepted Answer · 2016-06-03 16:16:31Z

Create a form and drop a treeview onto it and try this code below. I've faked up your data and put it into a DataTable - you will need to change this code to work with your MySql database. I think that part of your code is OK and you're asking how to populate the treeivew - so here it is:

Public Class Form1

    Public Data As New DataTable

    Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles Me.Shown
        Data.Columns.Add("Id")
        Data.Columns.Add("Name")
        Data.Columns.Add("Node_Id")
        Data.Rows.Add(1, "Color", 0)
        Data.Rows.Add(2, "Black", 1)
        Data.Rows.Add(3, "Red", 1)
        Data.Rows.Add(4, "White", 1)
        Data.Rows.Add(5, "Animal", 0)
        Data.Rows.Add(6, "Dog", 5)
        Data.Rows.Add(7, "Cat", 5)

        Dim Node As TreeNode
        For Each Row As DataRow In Data.Rows
            Node = New TreeNode(Row("Name"))
            Node.Name = Row("Id")
            If Row("Node_Id") <> 0 Then
                TreeView1.Nodes.Find(Row("Node_Id"), True)(0).Nodes.Add(Node)
            Else
                TreeView1.Nodes.Add(Node)
            End If
        Next

        TreeView1.ExpandAll()

    End Sub


End Class

Belloumi · Accepted Answer · 2020-03-31 12:50:34Z

all these methods do not allow managing more than two levels of the tree structure (Parent, son) but not Grandfather, father, son. I use this technic with a dictionary and starting from a single table (as mentioned by Max)

Code :

'Declare the dictionary
Public DicoChildParent As Dictionary(Of Object, Object)

Sub AlimenterTreeview()
        Dim Tlst(14, 2)
        Dim i As Long
        Dim MyNeud As TreeNode

        Tlst(0, 0) = "Moteur"
        Tlst(1, 0) = "Cullase"
        Tlst(2, 0) = "Cylindre"
        Tlst(3, 0) = "Pompe a eau"
        Tlst(4, 0) = "Pompe a huile"
        Tlst(5, 0) = "Echappement"
        Tlst(6, 0) = "Circuit refroidissement"
        Tlst(7, 0) = "Radiateur"
        Tlst(8, 0) = "Tube radiateur"
        Tlst(9, 0) = "Purge"
        Tlst(10, 0) = "Join de cullase"
        Tlst(11, 0) = "Soupape"
        Tlst(12, 0) = "Carroserie"
        Tlst(13, 0) = "Fond de roulement"

        Tlst(0, 1) = ""
        Tlst(1, 1) = "Moteur"
        Tlst(2, 1) = "Moteur"
        Tlst(3, 1) = "Moteur"
        Tlst(4, 1) = "Moteur"
        Tlst(5, 1) = ""
        Tlst(6, 1) = ""
        Tlst(7, 1) = "Circuit refroidissement"
        Tlst(8, 1) = "Radiateur"
        Tlst(9, 1) = "Radiateur"
        Tlst(10, 1) = "Cullase"
        Tlst(11, 1) = "Moteur"
        Tlst(12, 1) = ""
        Tlst(13, 1) = "Join de cullase"


        DicoChildParent = New Dictionary(Of Object, Object)
        For i = 0 To UBound(Tlst) - 1
            With Form7.TreeView1
                If Len(Tlst(i, 1)) = 0 Then
                    If Not .Nodes.ContainsKey(Tlst(i, 0)) Then
                        .Nodes.Add(Tlst(i, 0), Tlst(i, 0))
                        DicoChildParent.Add(Tlst(i, 0), "x")
                    End If
                Else
                    DicoChildParent.Add(Tlst(i, 0), DicoChildParent(Tlst(i, 1)) & "###" & Tlst(i, 1))
                    MyNeud = GetParentNeud(DicoChildParent(Tlst(i, 0)).ToString, Form7.TreeView1)

                    If Not MyNeud Is Nothing Then
                        MyNeud.Nodes.Add(Tlst(i, 0), Tlst(i, 0))
                    End If
                End If
            End With
        Next

        Form7.ShowDialog()


    End Sub
``` vb.net
Public Function GetParentNeud(ByVal StrParent As String, ByVal Tree As TreeView)
        Dim Vect
        Dim i
        Dim Tmp As TreeNode

        On Error GoTo Erreur

        GetParentNeud = Nothing
        Vect = Split(StrParent, "###")
        Tmp = Tree.Nodes(Vect(1))
        If UBound(Vect) - 1 > 0 Then
            For i = 2 To UBound(Vect)
                Tmp = Tmp.Nodes(Vect(i))
            Next
        End If

        GetParentNeud = Tmp

Erreur: Exit Function

    End Function

Result :

[enter image description here][1]


  [1]: https://i.sstatic.net/ncG5p.png

Collectives™ on Stack Overflow

How to create a tree view from MySQL as database using VB.NET treeview

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related