Data from one table to populate a VB TreeView

[KodeKrazy]

I have a table with the following columns, ID (A unique value) Style#, Color, Size (There a more columns than that, but those are the ones I will need for this exercise.)

There is a row for each Style#, in every Color, and every Size. (e.g.:
Rec1) Style A, White, Small
Rec2) Style A, White, Med
Rec3) Style A, White, Large
Rec4) Style A, Black, Small
Rec5) Style A, Black, Med
Rec6) Style B, White, Small
Rec7) Style B, Black, Large

etc...)

I want to populate the TreeView by Style, then Color, then Size. As evidenced by the sample data above, there can be sizes, colors available in one style, that are not available in another, etc.

I have thus far been able to extract data to populate the first two nodes on the control, but breaking out the sizes by color, is proving to be slightly beyond the reach of my present abilities.

I know I will probably need a Group By clause, but whenever I try to nest SELECT statements with Group By, I get an error!

Anyone have a suggestion? (I also posted this question in the SQL Server, MSSQL forum.)

[QVeen72]

Hi,

No, you dont need a "Group By".
Its easy if done in 2 steps.. Follow this Logic:

First Query Distinct Styles From Database:
Select Distinct Style From MyTable.
Loop through recordset and Populate TreeView StyleName (as Text and Key) as Parent Nodes/Root Nodes

Now Query Rest of the data
Select Distinct Style, Colour, Size From MyTable Order By Style, Colour,Size.
Loop through this recordset and Populate TreeView Colour and Size (as Text and Key) as Child for the Parent = Style

Regards
Veena

[KodeKrazy]

Hi,

No, you dont need a "Group By".
Its easy if done in 2 steps.. Follow this Logic:

First Query Distinct Styles From Database:
Select Distinct Style From MyTable.
Loop through recordset and Populate TreeView StyleName (as Text and Key) as Parent Nodes/Root Nodes

Now Query Rest of the data
Select Distinct Style, Colour, Size From MyTable Order By Style, Colour,Size.
Loop through this recordset and Populate TreeView Colour and Size (as Text and Key) as Child for the Parent = Style

Regards
Veena

This is what the Treeview control looks like so far:

+ Style A
- Black
- White
+ Style B
- Black
- Red

But there are no records being returned for Size (childrow2 = Nothing)

Here is my code so far:

 
Dim DSProducts As DataSet
        Dim CNproducts As New SqlClient.SqlConnection("DATA SOURCE=.\SQLDBNAME; INITIAL CATALOG=dbTableName; Integrated Security = True") 
        Dim DAItems As New SqlClient.SqlDataAdapter("SELECT DISTINCT Style, Description FROM Items WHERE Catalog = 4", CNproducts)
        Dim DAColors As New SqlClient.SqlDataAdapter("SELECT DISTINCT Color, Style FROM Items WHERE Catalog = 4", CNproducts)
        Dim DASizes As New SqlClient.SqlDataAdapter("SELECT DISTINCT Size, SizeOrder, Style, Color from Items WHERE Catalog = 4", CNproducts)
 
        DSProducts = New DataSet()
        CNproducts.Open()
        DAItems.Fill(DSProducts, "dtItems")
        DAColors.Fill(DSProducts, "dtColors")
        DASizes.Fill(DSProducts, "dtSize")
 
        'Create a data relation object to facilitate the relationship between the Colors and Sizes data.
        DSProducts.Relations.Add("ItemToColor",DSProducts.Tables("dtItems").Columns("Style"),DSProducts.Tables("dtColors").Columns("Style"))
 
' Tried both of these.Neither relation returns any records.
DSProducts.Relations.Add("ItemToSize",DSProducts.Tables("dtItems").Columns("Style"),DSProducts.Tables("dtSize").Columns("Style"))       
'DSProducts.Relations.Add("ColorToSize",DSProducts.Tables("dtColors").Columns("Color"),DSProducts.Tables("dtSize").Columns("Color"))
 
        TreeView1.Nodes.Clear()
        Dim parentrow As DataRow
        Dim ParentTable As DataTable
        ParentTable = DSProducts.Tables("dtItems")
 
        For Each parentrow In ParentTable.Rows
 
            Dim parentnode As TreeNode
            parentnode = New TreeNode(parentrow.Item(0) & " " & parentrow.Item(1))
            TreeView1.Nodes.Add(parentnode)
            '   populate child
 
            Dim childrow As DataRow
            Dim childnode As TreeNode
            childnode = New TreeNode()
            For Each childrow In parentrow.GetChildRows("ItemToColor")
                childnode = parentnode.Nodes.Add(childrow(0))
                childnode.Tag = childrow("Color")
 
                '    populate child2
 
                Dim childrow2 As DataRow
 
                Dim childnode2 As TreeNode
                childnode2 = New TreeNode()
                For Each childrow2 In childrow.GetChildRows("ItemToSize")
                    childnode2 = childnode.Nodes.Add(childrow2(0))
                    childnode2.Tag = childrow("Size")
 
                Next childrow2
 
            Next childrow
 
        Next parentrow
 
 

I need to know what I am doing wrong that the last group (Size) is not relating back to the Item & Color.

Thanks for your help!

KK

[QVeen72]

Hi,

Why you have commented 18th line...?
remove the comment, and 45th line change it to :

For Each childrow2 In childrow.GetChildRows("ColorToSize")

Regards
Veena

[KodeKrazy]

The reason it is commented out relates to the comment on line 16. I had tried both of those relations. (lines 17 & 18) However, when I uncomment line 18 (and change line 45, per your instructions), I get the error "These columns don't currently have unique values." and execution stops on line 18 (in the above example). As to why they don't have unique values I believe lies in the SQL that derives the data.

Thanks again for your help!

KK

[KodeKrazy]

Well, I finally figured it out! It's a rather clever solution, if I do say so myself. I ended up creating a unique value by concatenating the Style & Color columns in my query, then relating those values to each other in the resulting record sets.

(e.g.: Dim DASizes As New SqlClient.SqlDataAdapter("SELECT DISTINCT Size, Color + Style AS csVal FROM tblName", cnConnection)
and then add a

DSName.Relations.Add("ColorToSize", DSName.Tables("dtColors").Columns("scVal"), DSName.Tables("dtSize").Columns("scVal")))

Then just iterate through the recordset to populate the third branch of the treeview.

It works perfectly!

Again, Thank you for all of your assistance. I look forward to participating in these forums as there is some very useful information here, and I am confident that I will be able to contribute.

Cheers!

KK