Enhancing Inserting Data through the DataGrid Footer, Part 2

In Part 1 we looked at how to add a data-dependent DropDownList to the footer of a DataGrid. In this part we'll see how to add two DropDownLists, with the second DropDownList dependent upon the first.

Adding a Second DropDownList to the DataGrid's Footer
Now that we've seen how to add a single DropDownList to the DataGrid's footer, let's turn our attention at adding a second one that's dependent upon the first. That is, the values displayed in the second DropDownList depend on the value selected in the first DropDownList. We'll be building upon the code from the live demo we examined in Part 1.

There are a couple of challenges in adding a second DropDownList:

First, when the user selects a new option from the first DropDownList, the Web form needs to be posted back so that the second DropDownList can be populated with the appropriate list items for the newly selected item from the first DropDownList.
Second, when the postback occurs, we must be able to determine what option was selected from the first DropDownList, and then bind the second DropDownList to the appropriate data. The main challenge here is in being able to reference the second DropDownList so its DataSource property can be set and its DataBind() method called.

The first challenge can be solved with two additions to the code. First, we must specify that the first DropDownList, whenever it is changed, causes a postback. This is accomplished by setting the first DropDownList's AutoPostBack property to True. Next, we must create an event handler for the first DropDownList's SelectedIndexChanged event. This is the event that fires when the Web form is posted back after the first DropDownList has been changed. In this event's handler, we need to determine what option was selected, query the database for the appropriate data, and then bind this data to the second DropDownList.

This leads us directly into the second challenge. In order to bind the data to the second DropDownList, we must be able to programmtically reference the second DropDownList so that it's DataSource property can be set and its DataBind() method called. While this might seem like it should be an easy task, it is a bit more tricky than it first appears. This task is tricky in part because the DataGrid does not make it easy to programmatically reference the footer. While the DataGrid provides the Items collection, this collection only contains those rows of the DataGrid that are actually bound to data.

Typically, if one needs to programmatically access the footer they do so in the DataGrid's ItemDataBound event, which fires once for every row of the DataGrid, including the footer. However, this event only fires when the DataGrid's DataBind() method is called. Since the DataGrid is not bound after the user chooses a new list item from the first DropDownList, this route was not an option either.

The solution I will present in this article comes from some clever developers at a software consulting company I consult with occassionally, Software Outfitters. The developers there realized that when the DataGrid footer is nothing more than an instance of the DataGridItem class. So, if they could programmatically reference this item instance, they could easily snag the second DropDownList using the FindControl() method. The way they accessed the DataGridItem representing the footer is as follows: in the first DropDownList's SelectedIndexChanged event handler, they could reference the DropDownList causing the event to fire (via the sender parameter to the event handler). With this control, they could reference the TableCell the DropDownList appears in by using the DropDownList's Parent property. (The Parent property returns a control's parent control.) Using the Parent property again, they could get the DataGridItem that contained the TableCell that contained the first DropDownList. With a reference to the DataGridItem they could use the FindControl() method to get the second DropDownList.

The solution to these two challenges can be seen in the following code example and live demo. When examining the code, realize that the ASPFAQs.com data model does not have any sort of natural two-level hierarchy that would make two dependent DropDownLists sensible. However, if the data model was adjusted so that FAQs could contain both a category and subcategory, the two DropDownList example would be more realistic. Instead, the code sample below is a bit contrived. The first DropDownList lists the various FAQ categories (as it did in the first live demo. The second DropDownList displays all of the FAQs for the FAQ category selected from the first DropDownList. That is, if you change the category in the first DropDownList, the page is posted back and the second DropDownList lists the FAQs for the newly selected category.


            <% @Import Namespace="System.Data" %>
            <% @Import Namespace="System.Data.SqlClient" %>
            <script language="vb" runat="server">
            'Create a connection
            Dim myConnection as New _
            SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
            Dim ddlDataSet as DataSet = New DataSet()
            Sub Page_Load(sender as Object, e as EventArgs)
            If Not Page.IsPostBack then
            BindData()
            End If
            End Sub
            Sub BindData()
            'Code removed for brevity
            End Sub
            Function GetCategories() as DataSet
            'Code removed for brevity
            End Function
            Function GetFAQsByCategory(catID as Integer) as DataTable
            'Populate the ddlDataSet
            Dim strSQLDDL as String
            If catID < 0 then
            strSQLDDL = "SELECT FAQID, Description FROM tblFAQ " & _
            "WHERE FAQCategoryID = " & _
            "(SELECT TOP 1 FAQCategoryID FROM tblFAQCategory " & _
            "ORDER BY Name) " & _
            "ORDER BY Description"
            Else
            strSQLDDL = "SELECT FAQID, Description FROM tblFAQ " & _
            "WHERE FAQCategoryID = " & catID
            End If
            Dim myDataAdapter as SqlDataAdapter = New _
            SqlDataAdapter(strSQLDDL, myConnection)
            myDataAdapter.Fill(ddlDataSet, "FAQs")
            Return ddlDataSet.Tables("FAQs")
            End Function
            Function GetSelectedIndex(CID as String) as Integer
            Dim iLoop as Integer
            Dim dt as DataTable = ddlDataSet.Tables("Categories")
            For iLoop = 0 to dt.Rows.Count - 1
            If Int32.Parse(CID) = Int32.Parse(dt.Rows(iLoop)("FAQCategoryID")) then
            Return iLoop
            End If
            Next iLoop
            End Function
            Sub dgPopFAQs_Insert(sender as Object, e As DataGridCommandEventArgs)
            If e.CommandName = "Insert" then
            Dim lstFAQInsert as DropDownList = e.Item.FindControl("lstFAQsByCategory")
            Dim lstCatInsert as DropDownList = e.Item.FindControl("lstCategoriesInsert")
            Response.Write("<b>The Insert Command has no effect on this live demo</b><br />")
            Response.Write("<b>The data you would have inserted, though, was:<ul>")
            Response.Write("<li>" & lstCatInsert.SelectedItem.Text & "</li>")
            Response.Write("<li>" & lstFAQInsert.SelectedItem.Text & "</li></ul></b>")
            End If
            End Sub
            Sub LoadSecondDDL(sender as Object, e as EventArgs)
            'Reference the FAQCategoriesID DropDownList
            Dim categories as DropDownList = sender
            'Get the FAQ DropDownList
            Dim dgFooter as DataGridItem = categories.Parent.Parent
            Dim FAQDDL as DropDownList = dgFooter.FindControl("lstFAQsByCategory")
            'Get the value of FAQCategoryID
            Dim FAQCatID as Integer = categories.SelectedItem.Value
            'Get the list of FAQs for the specific category
            FAQDDL.DataSource = GetFAQsByCategory(FAQCatID)
            FAQDDL.DataBind()
            End Sub
            </script>
            <form runat="server">
            <asp:datagrid id="dgPopularFAQs" runat="server"
            AutoGenerateColumns="False"
            OnItemCommand="dgPopFAQs_Insert"
            ShowFooter="True"
            ...>
            <Columns>
            <asp:TemplateColumn ItemStyle-Width="10%"
            ItemStyle-HorizontalAlign="Center" HeaderText="FAQ ID"
            FooterStyle-HorizontalAlign="Center">
            <ItemTemplate>
            <%# Container.DataItem("FAQID") %>
            </ItemTemplate>
            <FooterTemplate>
            <asp:Button Text="Add" CommandName="Insert" runat="server" />
            </FooterTemplate>
            </asp:TemplateColumn>
            <asp:TemplateColumn HeaderText="Category">
            <ItemTemplate>
            <%# Container.DataItem("CategoryName") %>
            </ItemTemplate>
            <FooterTemplate>
            <asp:DropDownList runat="server" id="lstCategoriesInsert"
            DataValueField="FAQCategoryID"  DataTextField="Name"
            DataSource='<%# GetCategories() %>'
            AutoPostBack="True"
            OnSelectedIndexChanged="LoadSecondDDL" />
            </FooterTemplate>
            </asp:TemplateColumn>
            <asp:TemplateColumn HeaderText="FAQ Question">
            <ItemTemplate>
            <%# Container.DataItem("Description") %>
            </ItemTemplate>
            <FooterTemplate>
            <asp:DropDownList runat="server" id="lstFAQsByCategory"
            DataValueField="FAQID"  DataTextField="Description"
            DataSource='<%# GetFAQsByCategory(-1) %>' />
            </FooterTemplate>
            </asp:TemplateColumn>
            </Columns>
            </asp:datagrid>
            </form>

Conclusion
In this article we examined how to extend John Sanborn's earlier article on inserting new records using the DataGrid's footer. Specifically, we saw how to add a data-dependent DropDownList Web control to one of the column's of the footer, as well as how to add two DropDownLists, where one was dependent upon the selected value in the other.

Happy Programming!