Normalization help with Treeview control

[DimMeAsLost]

I have an Access 2007 app that I created that we use to make our material purchase requests from. Basically there is a materials table that holds our material information, costs, labor units, and 3 levels of catagories. I use 3 cascading combos on a pop up form with a list box that the user drills down then adds to the request form via an append query. We have been using this for about 4 years now.

I am in the middle of converting another app to VB.NET 2008 and SQL 2005 Full (database with be on server/ client will be on local machine). Anyway, I am being asked to add this material request into this application and add a treeview so it will be easier for the user to drill down. Our material database has over 5000 records. I have never used a treeview control and all examples I have seen do not seem to completely understand.

I have 2 questions:

1. Do I need to create a separate table that lists my catagories and and have the material database relationship to that table? I need help in rationalizing the normalization process so that my second question will be properly be setup (treeview control) but doesnt belong in this thread, but you do need to understand what I am trying to accomplish.

2. How can I set up the treeview control to properly show the items in the Root>Child>GChild of the database structure? Do I do a recursive method (which I am not sure how to do, or should I create the nodes manually and use TAGS as a parameter for a stored procedure. (Note: Server will be located in the same building as the clients, so no remote desktop or bandwith issues to concern with)

I want to thank everyone in advance for any help and guidance you can provide me.

 

[ss7thirty]

recursive method, which I am not sure how to do

A recursive method is one that calls itself. Below is an example of recursion to go through all folders in a folder and all files, etc, etc, all the way down.

Public Sub DrillBabyDrill(dir as IO.DirectoryInfo)
   For each fi as IO.FileInfo in Dir.GetFiles
      'perform processing on each file
   Next fi
   For each d as IO.DirectoryInfo in dir.GetDirectories
     DrillBabyDrill(d)
   Next d
End Sub

I would suggest using recursion if possible because it would allow your application to be dynamic and this is pretty much always a good thing. Also, normalization of your data is a good idea. However, it may not always be necessary it is just good programming practice.

 

[DimMeAsLost]

Well I cant believe it has been over a year since I started this. But finally getting back to working on this.

I have came up with the code below but it is only giving me 2 levels past the root. I see the Root, Level01, and Level02 but nothing past that. I need help with adding the 3rd and 4th Child to the For Each..Next section.

This is a form 'frmMatTreeSel' that I have with a splitter control, and on the left side a treeview control named 'Treeview1'. On the second side I have a listbox control 'lbxMatDesc'. The plan would be that when I drill down thru the treeview, once the user see the item they want, then I can trigger another event to populate the listbox showing the related items. The code I have is all inside the form's class, see below.

Can someone help me with this please? Thanks to all in advance.

Imports System.Data.SqlClient


Public Class frmMatTreeSel
    Const Con As String = _
        "Data Source=XXXX My connection string not shown on purpose"

    Const SQLExpression As String = _
    "SELECT DISTINCT Level01, Level02, Level03, MaterialID " & _
    "FROM tblMaterialsTree "


    Const Database As String = "MATERIALS"




    Private Sub frmMatTreeSel_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        BindData()

    End Sub

    Sub BindData()

        Dim Rootnode As TreeNode = Nothing
        Dim Mainnode As TreeNode = Nothing
        Dim Childnode As TreeNode = Nothing
        Dim MainName As String = String.Empty
        Dim cn As New SqlConnection(Con)
        Dim adp As New SqlDataAdapter(SQLExpression, cn)
        Dim ds As New DataSet

        adp.Fill(ds, "SystemData")

        TreeView1.Nodes.Clear()

        Rootnode = TreeView1.Nodes.Add(key:="Root", text:=Database, _
        imageIndex:=0, selectedImageIndex:=0)

        For Each row As DataRow In ds.Tables("SystemData").Rows

            If MainName <> row(0).ToString Then
                Mainnode = Rootnode.Nodes.Add(key:="Table", text:=row(0).ToString, _
                imageIndex:=1, selectedImageIndex:=1)
                MainName = row(0).ToString
            End If

            Childnode = Mainnode.Nodes.Add(key:="Column", text:=row(1).ToString, _
            imageIndex:=2, selectedImageIndex:=2)
        Next

        TreeView1.Nodes(0).EnsureVisible()

        ds.Dispose()
        ds = Nothing
        adp.Dispose()
        adp = Nothing
        cn.Dispose()
        cn = Nothing

    End Sub

End Class