How to display correct subcategories after selecting a category from a combo

How to display correct subcategories after selecting a category from a combo

• Kymagic

  Hello

   

  After you find the row with the category you can do

   

  dr.GetChildRows("SubCat")

   

  This will return you a collection of Datarows that are subcategories of the row you selected. This list can be bound to a control as a datasource or used in a For each loop. I usually display those relations inside a treeview, and store the datarow inside the TAG element of the treenode. Once a user selects a treenode, i can call the getChildrows of the TAG of the treeview and for example display the products into a datagrid.

  To fill the treeview you can use code along this:

   

  For Each oRow As DataRow In oDS.Tables(2).Rows

  If oRow.IsNull("ParentGroup_ID") Then

  Dim oNode As TreeNode

  oNode.Text = dr.Item("groupName")

  oNode.Tag = dr

  FillSubNodes (oNode.nodes, dr)

  tvCat.nodes.add (oNode)

  End If

  Next

   

  Inside the FillSubNodes you can use code like this:

   

  For Each oRow As DataRow In dr.GetChildRows("Gruppen")

  Dim oNode As TreeNode

  oNode = New treenode

  oNode.Text = dr.Item("groupName")

  oNode.Tag = dr

  FillSubNodes (oNode.nodes, oRow) 'Recursive Call to self to get futher SubCategories

  oTarget.add (oNode) ' This is the Node collection passed in as a parameter

  Next

   

  This Code will fill your complete Treeview with the data.

  
  
  

• airin

  Thanks for the info. This should help with my objectives as I am trying to create an IT Asset tracking system.
  

• Anthony Borton

  Hello..

   

  I would use a slightly different aproach. The nice thing about Datasets is that they can also store and use relations inside them... Also I would only save the subcategory to which an item belongs. The category is a direct result from the choosen subcategory, and does not need to be saved because of this. Also Subcategories tend to "move" around over time, and this eliminates the need to update the table in which you save them. Example : CD-Rom-RW  drives might have been "CD-Rom Burners" ages ago, but now belong under "Optical Burners" with DVD-Burners grouped together.

   

  Tabledesign:

  Category

  =============

  category_id

  subcategory_name

  parent_ID

   

  I would only use one category table and fetch the whole table into the client since most likely it will be used a lot of times. I would store the Table in a dataset and add a datarelation to the Dataset.

  Example

  oDS.Relations.Add("SubCat", oDS.Tables(0).Columns("category_id"), oDS.Tables(0).Columns("Parent_ID"), False)

   

  Now once you display the initial list (By using a Filter or iterating through the Dataset and displaying the parents only) you can fetch the content for the next combobox quite easy like this :

  (dr is a Datarow from the above Dataset)

  dr.GetChildRows("SubCat")

  Works like a charm and you can nest as many Categories as you want.

   

   

  Hope this helps

   

  
  
  

• InfiniZac

  Thanks for the details. I will be using combo lists to achieve this for the time being. Is the method the same

  
  

• Jamie Thomson

  you can re-populate the subcategory combo when you change the category in the category combo. and you can use the SelectedValueChanged event of category combo to achive this.

  do you need this or do you want any thing else

  
  
  

• A kid

  sorry for previous post you chage the procedure as below!!

  let your table are

  category

  ===========

  category_id

  category_name

  subcategory

  =============

  subcategory_id

  subcategory_name

  category_id

  private sub CategoryChanged(ByVal sender As Object, ByVal e As System.EventArgs)

  Dim dsData As New DataSet

  Dim adapter As New SqlDataAdapter("select subcategory_name from subcategory, category where subcategory.category_id = category.category_id and category_name = '" & combobox1.selectedtext & "'", New SqlConnection(strDBConnection))

  adapter.SelectCommand.Connection.Open()

  adapter.Fill(dsData)

  adapter.selectcommand.connection.close()

  adapter.dispose()

  ComboBox2.DataSource = dsData.Tables(0)

  ComboBox2.DisplayMember = "subcategory_name"

  dsData.dispose()

  end sub

  
  
  

• Jan Kučera

  Prasant,

  Thanks very much for this. This will help me a lot. My only issue will be when I come to save the changes to the database so that it can write the correct categoryid and subcategoryID into another table.

  Regards

  Dipendra

  
  

• Praveena Karuturi

  Thanks for the details. This looks useful. I am slightly new to VB 2005. Is there any example code you can provide to achieve this or can you go into any detail.

  Regards

  Dipendra

  
  

• Fistandantilus282303

  You can also use like this

  ComboBox1.DisplayMember = "Category_Name"

  ComboBox1.ValueMember = "Category_ID"

  and you can get the value as ComboBox1.SelectedValue. in this way you will directly get the selected category and subcategories id.

  
  
  

• Leebert

  Yes... basically it is

  Once you have the Datarow that was selected in the Combobox you can the call the dr.GetChildRows("Gruppen") In order to get all Subcategories from that row. But really only try to save the final category an item is in. If you will save the top category also you will have redundant date inside the item...

  
  
  

• John1010

  populate category table :

  ========================

  Dim dsData As New DataSet

  Dim adapter As New SqlDataAdapter("select category_name from category", New SqlConnection(strDBConnection))

  adapter.SelectCommand.Connection.Open()

  adapter.Fill(dsData)

  adapter.selectcommand.connection.close()

  adapter.dispose()

  ComboBox1.DataSource = dsData.Tables(0)

  ComboBox1.DisplayMember = "category_name"

  dsData.dispose()

  AddHandler ComboBox1.SelectedIndexChanged, AddressOf CategoryChanged

  private sub CategoryChanged(ByVal sender As Object, ByVal e As System.EventArgs)

  Dim dsData As New DataSet

  Dim adapter As New SqlDataAdapter("select subcategory_name from subcategory where category_name = '" & combobox1.selectedtext & "'", New SqlConnection(strDBConnection))

  adapter.SelectCommand.Connection.Open()

  adapter.Fill(dsData)

  adapter.selectcommand.connection.close()

  adapter.dispose()

  ComboBox2.DataSource = dsData.Tables(0)

  ComboBox2.DisplayMember = "subcategory_name"

  dsData.dispose()

  end sub

  
  
  

• tom_7

  Thanks for the details. Since I am fairly new to VB 2005, are you able to expand in more detail what you have described such as the table design alongwith which fields are primary or foreign keys.

  How do I iterate through the dataset to get the correct subcategories.

  Regards

  Dipendra

  
  
• How to display correct subcategories after selecting a category from a combo