[RESOLVED] Mysql to Treeview-VBForums
Results 1 to 5 of 5

Thread: [RESOLVED] Mysql to Treeview

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jul 2002
    Posts
    88

    Resolved [RESOLVED] Mysql to Treeview

    I need to have Treeview Regions/Town/contact any advice how it is best to do this

    anyone who can help me to get list to treeview like this
    Regions
    --Town
    --contact
    I have Table one for Regions and one for Town and the Table contact


    to day the database/treeview is like this
    company
    --contact


    Code:
     Public Sub getcompany()
    
    
            Dim conn As Common.DbConnection
    
            cnString = String.Format("Server=SERVER;Database=DATABASE;Uid=USER;Pwd=PASSWORD;SslMode=none;")
    
            Dim sqlQRY As String = "SELECT * FROM  company ORDER BY company Asc"
    
    
            conn = New MySqlConnection(cnString)
    
            Try
                conn.Open()
    
                'create command 
                Dim cmd As Common.DbCommand = New MySqlCommand(sqlQRY, conn)
    
                'create data reader
                Dim rdr As MySqlDataReader = cmd.ExecuteReader
    
                While (rdr.Read)
    
                    TreeView1.Nodes.Add(rdr("ID").ToString, rdr("company").ToString, 1, 2)
                    AddOrders(rdr("ID").ToString)
    
                End While
    
            Catch ex As Common.DbException
                MsgBox(ex.ToString)
            Finally
                ' Close connection
                conn.Close()
            End Try
    
        End Sub
    	
    	
    	
    	    Private Sub AddOrders(ByVal companyID As String)
    
            Dim conn2 As Common.DbConnection
    
            'Create connection
            conn2 = New MySqlConnection(cnString)
    
            Dim sqlQRY As String = "SELECT * FROM contact WHERE companyID = '" & companyID & "'  ORDER BY contact Asc"
    
            ' Open connection
            conn2.Open()
    
            'create command 
            Dim cmd As Common.DbCommand = New MySqlCommand(sqlQRY, conn2)
    
            'create data reader
            Dim rdrs As MySqlDataReader = cmd.ExecuteReader
    
            While (rdrs.Read)
    
                TreeView1.Nodes(companyID).Nodes.Add("", rdrs("contact").ToString, 5, 3)
    
            End While
        End Sub

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    97,097

    Re: Mysql to Treeview

    I'd suggest populating a DataSet with three DataTables and appropriate DataRelations between then. You can then use the appropriate DataRelation to get the child rows for each parent to add child nodes. E.g.
    vb.net Code:
    1. Dim connectionString = "connection string here"
    2. Dim data As New DataSet
    3. Dim grandparentAdapter As New SqlDataAdapter("SELECT * FROM Grandparent", connectionString) With {.MissingSchemaAction = MissingSchemaAction.AddWithKey}
    4. Dim parentAdapter As New SqlDataAdapter("SELECT * FROM Parent", connectionString) With {.MissingSchemaAction = MissingSchemaAction.AddWithKey}
    5. Dim childAdapter As New SqlDataAdapter("SELECT * FROM Child", connectionString) With {.MissingSchemaAction = MissingSchemaAction.AddWithKey}
    6.  
    7. grandparentAdapter.Fill(data, "Grandparent")
    8. parentAdapter.Fill(data, "Parent")
    9. childAdapter.Fill(data, "Child")
    10.  
    11. data.Relations.Add("GrandparentParent",
    12.                    data.Tables("Grandparent").Columns("GrandparentId"),
    13.                    data.Tables("Parent").Columns("GrandparentId"))
    14. data.Relations.Add("tParentChild",
    15.                    data.Tables("Parent").Columns("ParentId"),
    16.                    data.Tables("Child").Columns("ParentId"))
    17.  
    18. Dim nodes As New List(Of TreeNode)
    19.  
    20. For Each grandparentRow As DataRow In data.Tables("Grandparent").Rows
    21.     Dim grandparentNode As New TreeNode(CStr(grandparentRow("GrandparentName")))
    22.  
    23.     For Each parentRow As DataRow In grandparentRow.GetChildRows("GrandparentParent")
    24.         Dim parentNode As New TreeNode(CStr(parentRow("ParentName")))
    25.  
    26.         For Each childRow As DataRow In parentRow.GetChildRows("ParentChild")
    27.             parentNode.Nodes.Add(New TreeNode(CStr(childRow("ChildName"))))
    28.         Next
    29.  
    30.         grandparentNode.Nodes.Add(parentNode)
    31.     Next
    32.  
    33.     nodes.Add(grandparentNode)
    34. Next
    35.  
    36. TreeView1.Nodes.AddRange(nodes.ToArray())
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Jul 2002
    Posts
    88

    Re: Mysql to Treeview

    Thanks jmcilhinney this work well


    when I connect Parent(Town) to Child(contact) Each Parent(Town) can have many Child(contact)
    same with Child(contact) it can have many Parent(Town)

    it it best to get contact information and write to new Table which stores Child(contact_Town)

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    97,097

    Re: Mysql to Treeview

    That does complicate things slightly and is something that you really should have mentioned to begin with. You could do much as I showed with the child table being the join table between Town and Contact. When you get a TownContact row, you can get the ContactId from that and then get the corresponding Contact row from the Contact table by calling Find on its Rows collection.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Jul 2002
    Posts
    88

    Re: Mysql to Treeview

    Thanks jmcilhinney

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.