asp:Q&A
LANGUAGES: VB.NET
ASP.NET VERSIONS: 1.x
Dynamic Dropdowns
Get the Help You Need
By Josef Finsel
Welcome to this month s Q&A column. Before I get started, please remember that this is your column; it runs on the questions you ask, so feel free to send them my way so I can help you.
Q: I implemented the example for dynamic dropdowns demonstrated in your January column and was impressed with what I saw but I have a problem. My project for work uses the dropdowns to filter employees by branch. I have a branch dropdown that displays only employees in that branch. That works fine, but when I choose a different employee than the one at the top of the list, I want something to happen, so I tried creating a DropDownSelectedIndexChanged event. It catches the event, then reloads the code in the Page_Load method so my branch stays the same. But no matter which employee I pick when the page reloads, the first employee is selected. How can I modify the code to retain the correct employee?
A: This is an easy thing to fix; all you need to do is change when the data is loaded into the combobox. In the January sample I created, I made it simple and left everything in the Page_Load event (see Got Questions? ).
Although changing when the data is loaded is fairly simple, I ran into some difficulty modifying the code to show it because I wanted to show something realistic, along the lines of the question being asked. To that end, I switched from the pubs database I used for the January article to the Northwind database where I could pull data linked across several tables: Region, Territories, EmployeeTerritories, and Employees (see Figure 1). The goal was to build a simple, three-dropdown Web page that would allow you to select a region, drill down to see the employees with territories in that region, and, by selecting an employee, see their individual territories. A fairly straightforward task or so it seemed before I began.
Figure 1: Using the Northwind
database I can pull data linked across several tables.
I needed to create four DataTables, one for each database table. Then I could filter them based on the selection of the table above them in the hierarchy. The first problem I encountered is that this isn t a one-way street. I would need to take several steps to get the data the way I wanted it:
- Filter Territories based on the selected RegionID to get the list of the territories in the region.
- Filter EmployeeTerritories by TerritoryID based on the filtered Territories table.
- Filter Employees based on the EmployeeID in the EmployeeTerritories table.
- Refilter the EmployeeTerritories table to filter by the employees with territories in the region.
- Refilter the Territories table based on the TerritoryID now available from EmployeeTerritories as filtered in step 4.
To make matters worse, although ADO.NET has many great tools for ensuring relational integrity between tables, there is no easy way to use those DataRelations to get data from the tables for display in the dropdowns. But, with a little perseverance, I found a way to do this. For simplicity s sake, I did make one change to the data and eliminated the EmployeeTerritories table, joining it to the Territories table to add the EmployeeID. I could do this because this was a one-to-many connection with no territory being assigned to more than one employee. This combined steps 2 and 3 into one step and steps 4 and 5 into another. If it were a many-to-many connection I could have expanded the methods used here to follow all five steps.
I m not going to go into the same details covered in my January column, but the Page_Load checks to see if the page is being posted back and, if it isn t, loads three DataTables and saves those tables to session variables so that there is no further need to hit the database server. Then it calls FilterFromRegions, a subroutine that first filters the Territories DataTable based on the selected Region information. Then it creates a new filter for the Employees DataTable.
If I were doing this through calls to the database, I could easily requery the Employees table using a subquery to define a filter based on another table. That would look something like this:
SELECT EmployeeID, LastName + ',' + FirstName as EmpName
FROM Northwind..Employees WHERE EmployeeID
IN (SELECT EmployeeID FROM Territory WHERE RegionID=1)
Unfortunately, even with the DataTables shared in a DataSet, there is no way to actually reference them in this manner. This means that we need to build a filter that contains all the rows we need. This is easy enough to do by looping through the rows of the filtered table, but you can t use the same RowFilter property that we use before DataBinding. I don t know why, but once you ve filtered a DataTable, you can t get the filtered rows using the DefaultView. Instead, we ll use the Select method. This takes a filter and returns an array of DataRows that we can loop through to create the filter for the next table.
The filter we are going to create is going to be an IN filter. This type of filter uses a comma-delimited list that defines a set of data. An example would be EmployeeID IN (1,2,4,5). We re going to keep adding the values from our array of DataRows to a string that we ll use as a filter. The code to do this is shown in Listing One. Here we define rwTerritory as a DataRow and use a For..Each loop to cycle through the array of DataRows returned by the Select method. When I m done looping through, I ll have a comma at the end of the string that will need to be removed. Then I can use the assembled filter to filter the next DataSet.
I ve split the filtering into two sections: FilterFromRegions, which filters the territories based on the region; and FilterFromEmployee, which filters the territories based on EmployeeID. After loading the Regional dropdown in page load, I call FilterFromRegions, which in turn calls FilterFromEmployee. These are also called as appropriate when the dropdown changes for Region or Employees.
There are many things that could be done to improve this. Pulling the code that creates the comma-separated list from an array of DataRows into a separate function would be nice. That could also allow you to eliminate duplicates, since the four employees in the Northern region have 19 territories and the filter that is first being applied to the Employees DataTable has a lot of duplication in it. But I ll leave that as an exercise for you to try as you explore the code when you download it.
Well, that wraps up this month s column. Send your ASP.NET questions to [email protected] so I can help you get the answers you need.
The sample code accompanying this article is available for download.
Josef Finsel is a software consultant. He has published a number of VB and SQL Server-related articles and is currently working on the syntax for FizzBin.NET, a programming language that works the way programmers have always suspected. He s also author of The Handbook for Reluctant Database Administrators (Apress, 2001).
Begin Listing One Displaying Data Using a DataGrid and Templates
Private Sub listOfRegions_SelectedIndexChanged(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles _
listOfRegions.SelectedIndexChanged
FilterFromRegions()
End Sub
Private Sub listOfEmployees_SelectedIndexChanged(ByVal sender As _
System.Object, ByVal e As System.EventArgs) Handles _
listOfEmployees.SelectedIndexChanged
FilterFromEmployee()
End Sub
Private Sub FilterFromRegions()
Dim filterOn As String
Dim dtTerritories As DataTable = Session("Territories")
Dim dtRegions As DataTable = Session("Regions")
Dim dtEmployees As DataTable = Session("Employees")
'Fill the Regions
filterOn = "RegionID = " & listOfRegions.SelectedItem.Value
Dim rwTerritory As DataRow
Dim tfilterOn = "EmployeeID in ("
For Each rwTerritory In dtTerritories.Select(filterOn)
tfilterOn = tfilterOn & rwTerritory("EmployeeID") & ","
Next
If tfilterOn.EndsWith(",") Then
tfilterOn = tfilterOn.Substring(0, tfilterOn.Length - 2)
End If
tfilterOn = tfilterOn & ")"
dtEmployees.DefaultView.RowFilter = tfilterOn
listOfEmployees.ClearSelection()
listOfEmployees.DataSource = dtEmployees.DefaultView
listOfEmployees.DataTextField = "EmpName"
listOfEmployees.DataValueField = "EmployeeID"
listOfEmployees.DataBind()
FilterFromEmployee()
End Sub
End Listing One
