Figure 1
Figure 2
Introduction
In this article, I will show how to bind the menu control with the database. Suppose you have a situation to display the different Category and its Sub-Categories. When we click the Category/Sub Category (See Figure 1) to view the detail information about Category/Sub Category (See Figure 2), then this bit of code will be useful. This code prevents a post back to the server while selecting Sub Category.
Using the Code
Database Detail
Table Name - Category
Table Name - SubCategory
There are two tables (Category
, SubCategory
) which are related to each other.
Here’s the method called BindMenu
which binds the menu control during the page load.
- Create the connection string to establish connection with the database:
Dim connectionString As String =
WebConfigurationManager.ConnectionStrings_
("DatabaseConnectionString1").ConnectionString
Dim con As New SqlConnection(connectionString)
- Create two
DataAdapter
s for both the tables:
Dim dadCategories As New SqlDataAdapter("SELECT CatId,CatName FROM Category
order by CatName", con)
Dim dadSubCat As New SqlDataAdapter("SELECT SubCatId,CatId,SubCatName FROM
SubCategory order by SubCatName", con)
- Create a
DataSet
and fill the dataset
with both the tables:
Dim dsCat As New DataSet()
Using con
con.Open()
dadCategories.Fill(dsCat, "Category")
dadSubCat.Fill(dsCat, "SubCategory")
End Using
- Relate both the tables and name the relation as
Children
:
dsCat.Relations.Add("Children", dsCat.Tables("Category").Columns("CatId"),
dsCat.Tables("SubCategory").Columns("CatId"))
- Loop through each category of data and its related Sub category of data. At each loop, we create menu items and associate with the menu control.
For Each categoryRow As DataRow In dsCat.Tables("Category").Rows
Dim mNode As New MenuItem(CType(categoryRow("CatName"), String), "", "",
"~/DetailView.aspx?CatID=" + CType(categoryRow("CatId"), String), "_parent")
Menu1.Items.Add(mNode)
Dim subCatRows() As DataRow = categoryRow.GetChildRows("Children")
For Each row As DataRow In subCatRows
Dim subCatName As String = CType(row("SubCatName"), String)
Dim subCatItems As New MenuItem(subCatName, "", "",
"~/DetailView.aspx?CatID=" + CType(row("CatId"), String) + "&SubCatID=" +
CType(row("SubCatId"), String), "_parent")
Menu1.Items(count).ChildItems.Add(subCatItems)
Next
count = count + 1
Next
History
- 28th June, 2009: Initial post