For our final example in this chapter, we'll look at binding data to a drop-down list, and using the value selected from the list to filter and provide different views of that data. This quite large sample will bring together a number of the things that we've been looking at over the last two chapters, so don't let it's length put you off. For the most part, it's things you've seen before, working in slightly different contexts. Try It Out - Binding to a Drop-down List
We're going to implement a page that displays the products available from Northwind, and allows them to be filtered by their category (beverages, condiments, etc.), and by price range.
How It WorksThe main points of the code in this page are:
Let's look first at the HTML for the page, which is a little more elaborate than usual:
<form runat="server"> <asp:Literal runat="server" Text="Category:" /> <asp:DropDownList runat="server" AutoPostBack="True" OnSelectedIndexChanged="FilterChange" /> <asp:Literal runat="server" Text="Price Range:" /> <asp:DropDownList runat="server" AutoPostBack="True" OnSelectedIndexChanged="FilterChange"> We have a server-side < form> element, within which we have two labels that display the legends Category and Price Range in order to identify the two drop-down lists to the visitor. There are two things to note about those list controls here:
Continuing with the HTML, we have the items for the price filter drop-down list:
<asp:ListItem Value="0" Selected="True">Any Price</asp:ListItem> <asp:ListItem Value="1">Cheap</asp:ListItem> <asp:ListItem Value="2">Moderate</asp:ListItem> <asp:ListItem Value="3">Expensive</asp:ListItem> <asp:ListItem Value="4">Absurdly Expensive</asp:ListItem> </asp:DropDownList> Next, we have our DataGrid control. Notice that we use the BackColor attribute of the HeaderStyle element to set the background color of the grid's header row, that the background color of the rows of the grid is set through BackColor attribute of ItemStyle, and that the background color of every other row is set with the BackColor attribute of AlternatingItemStyle. This gives our DataGrid control a more pleasing appearance.
<asp:DataGrid runat="server"> <HeaderStyle BackColor="#C0C0FF" /> <ItemStyle BackColor="#F1F1F1" /> <AlternatingItemStyle BackColor="#E8E6E6" /> </asp:DataGrid> </form> In our VB.NET code, the first thing we do is to define two public strings that will hold default values for our first filter.
Public strCategoryFilter As String = "CategoryID=1" Public strPriceFilter As String = "UnitPrice>0" In Page_Load(), we use Page. IsPostBack to determine whether this is the first time the page has been loaded. If so, we populate our drop-down list with FillDropDownList(), and call DataFiller() to display our data.
Sub Page_Load(Source As Object, E As EventArgs) If Not Page.IsPostBack Then FillDropDownList() DataFiller() End If End Sub If it is not the first time the page has been loaded, DataFiller() will be called by FilterChange(), which itself will be called when the visitor selects an entry from one of the drop-down lists. In FillDropDownList(), we use a data reader object to bind the categories from the Categories table to the ddlCategoryFilter drop-down list. We only have to populate the drop-down list once, when the page is first loaded - the values will persist between postbacks, so there's no need to refill it when they occur. FillDropDownList() begins with the usual connection and command objects, opens a connection, and uses ExecuteReader() to begin reading the data. This should all look quite familiar.
Sub FillDropDownList() Dim strConnection As String = ConfigurationSettings.AppSettings("NWind") Dim objConnection As New SqlConnection (strConnection) Dim strSqlCategories As String = "SELECT CategoryName,CategoryID " & _ "FROM Categories" Dim objCommand As New SqlCommand(strSqlCategories, objConnection) Dim objReader As SqlDataReader = Nothing objConnection.Open() objReader = objCommand.ExecuteReader() Next, we bind the incoming data from the Categories table of Northwind to ddlCategoryFilter. You'll recall that the DataTextField property sets the field that will actually be displayed in the list, while DataValueField sets the field that will be used to provide the values that will be returned.
ddlCategoryFilter.DataSource = objReader ddlCategoryFilter.DataTextField = "CategoryName" ddlCategoryFilter.DataValueField = "CategoryID" ddlCategoryFilter.DataBind() Finally (and very importantly!), we close the connection:
objConnection.Close() End Sub Next up for investigation is DataFiller(). The first part of this is pretty standard: we create the relevant objects, and fill a DataTable with data from the Products table of Northwind.
Sub DataFiller() Dim strConnection As String = ConfigurationSettings.AppSettings("NWind") Dim objConnection As New SqlConnection(strConnection) Dim strSqlProducts As String = "SELECT ProductID, ProductName, " & _ "CategoryID, UnitPrice FROM Products" Dim objAdapter As New SqlDataAdapter(strSqlProducts, objConnection) Dim objDataSet As New DataSet() objAdapter.Fill(objDataSet, "dtProducts") Then we create our DataView object and set its RowFilter property to filter with category information and price information, using strCategoryFilter and strPriceFilter.
' Create our DataView and filter by category and price Dim dvUK As New DataView(objDataSet.Tables("dtProducts")) dvUK.RowFilter = strCategoryFilter & " AND (" & strPriceFilter & Finally, we bind the DataView to our DataGrid.
dgProducts.DataSource = dvUK dgProducts.DataBind() End Sub In FilterChange(), we call both FilterByPrice() and FilterByCategory() .To the former, we pass the string that has been selected from ddlPriceFilter; to the latter, we pass a string that has been chosen from ddlCategoryFilter. These two procedures will set up the correct strings for filtering our data:
Sub FilterChange(Source As Object, E As EventArgs) FilterByPrice(ddlPriceFilter.SelectedItem.Text.ToString()) FilterByCategory(ddlCategoryFilter.SelectedItem.Value.ToString()) Finally in FilterChange(), we call DataFiller() to redisplay our data.
DataFiller() End Sub In the last two procedures, we actually set up the strings for filtering the displayed data. In FilterByPrice(), we use a Select...Case statement to set up the correct value for strPriceFilter:
Sub FilterByPrice(strChoice As String) Select strChoice Case "Any Price" strPriceFilter = "UnitPrice>0" Case "Cheap" strPriceFilter = "UnitPrice<20" Case "Moderate" strPriceFilter = "UnitPrice>19 AND UnitPrice<50" Case "Expensive" strPriceFilter = "UnitPrice>=50" Case "Absurdly Expensive" strPriceFilter = "UnitPrice>100" End Select End Sub In FilterByCategory(), we simply set strCategoryFilter to the string passed to it:
Sub FilterByCategory(strChoice As String) strCategoryFilter = "CategoryID = " & strChoice End Sub And that's all there is to it! With ASP.NET, as you can surely begin to see, you don't need to do a lot of work in order to get useful, well-presented results. |