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:

[CODE=vbnet]

Dim DSProducts As DataSet
Dim CNproducts As New SqlClient.SqlCo nnection("DATA SOURCE=.\SQLDBN AME; INITIAL CATALOG=dbTable Name; Integrated Security = True")
Dim DAItems As New SqlClient.SqlDa taAdapter("SELE CT DISTINCT Style, Description FROM Items WHERE Catalog = 4", CNproducts)
Dim DAColors As New SqlClient.SqlDa taAdapter("SELE CT DISTINCT Color, Style FROM Items WHERE Catalog = 4", CNproducts)
Dim DASizes As New SqlClient.SqlDa taAdapter("SELE CT DISTINCT Size, SizeOrder, Style, Color from Items WHERE Catalog = 4", CNproducts)

DSProducts = New DataSet()
CNproducts.Open ()
DAItems.Fill(DS Products, "dtItems")
DAColors.Fill(D SProducts, "dtColors")
DASizes.Fill(DS Products, "dtSize")

'Create a data relation object to facilitate the relationship between the Colors and Sizes data.
DSProducts.Rela tions.Add("Item ToColor",DSProd ucts.Tables("dt Items").Columns ("Style"),DSPro ducts.Tables("d tColors").Colum ns("Style"))

' Tried both of these.Neither relation returns any records.
DSProducts.Rela tions.Add("Item ToSize",DSProdu cts.Tables("dtI tems").Columns( "Style"),DSProd ucts.Tables("dt Size").Columns( "Style"))
'DSProducts.Rel ations.Add("Col orToSize",DSPro ducts.Tables("d tColors").Colum ns("Color"),DSP roducts.Tables( "dtSize").Colum ns("Color"))

TreeView1.Nodes .Clear()
Dim parentrow As DataRow
Dim ParentTable As DataTable
ParentTable = DSProducts.Tabl es("dtItems")

For Each parentrow In ParentTable.Row s

Dim parentnode As TreeNode
parentnode = New TreeNode(parent row.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.GetCh ildRows("ItemTo Color")
childnode = parentnode.Node s.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.GetChi ldRows("ItemToS ize")
childnode2 = childnode.Nodes .Add(childrow2( 0))
childnode2.Tag = childrow("Size" )

Next childrow2

Next childrow

Next parentrow

[/CODE]

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.GetChi ldRows("ColorTo Size")

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.SqlDa taAdapter("SELE CT DISTINCT Size, Color + Style AS csVal FROM tblName", cnConnection)
and then add a

DSName.Relation s.Add("ColorToS ize", DSName.Tables(" dtColors").Colu mns("scVal"), DSName.Tables(" dtSize").Column s("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