Adding a Second Data-Dependent DropDownList to the DataGrid's Footer

This example shows adding a second data-dependent DropDownList Web control to the footer of the DataGrid. The second DropDownList is dependent upon the first. That is, when the first DropDownList changes, the second one's contents change altogether.

Please note that this is a rather contrived example. Since the ASPFAQs.com database does not have any sort of "two-levels" of hierarchy (like a category and subcateogry table), I sort of forced two dependent DropDownLists. The first DropDownList lists the various FAQ categories. The second DropDownList lists the FAQs that belong to the category selected in the first DropDownList.

 FAQ IDCategoryFAQ Question
Edit 2 Strings How do you convert a string to all uppercase or all lowercase?
Edit 12 Email How can I display line breaks in an email message?
Edit 14 Email Can I send emails without using CDONTS?
Edit 15 Strings How can I determine the length of a string (how many characters are in a string)?
Edit 17 Strings How can I find the position of a certain character (or substring) within a string?
Edit 19 Dates and Times How can I return the current date/time?
Edit 20 Functions and Subroutines What is recursion?
Edit 23 FileSystemObject What, exactly, is the FileSystemObject?
Edit 25 Email How can I send an email using CDONTS?
Edit 8 Databases, General How do you create a database table through a SQL statement (via an ASP page, perhaps)?
Edit 16 Dates and Times Using VBScript, how can I determine if a variable is a valid date or not?
Edit 18 Dates and Times How can I easily display a nicely formatted date variable?
Edit 24 FileSystemObject How can I read the contents of a text file through an ASP page?
Edit 7 Arrays How do I dynamically resize an array?
Edit 11 Email How can I have my ASP page send HTML-formatted email?
Edit 10 Databases, General How can I tell what version of ADO I am using?
Edit 13 Email How do you send email attachments through an ASP page?
Edit 21 Arrays How can I determine the upper or lower bounds of an array?
Edit 22 Email How can I validate an email adress using Jscript?
 

Source Code 
<% @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()
    '2. Create the command object, passing in the SQL string
    Const strSQL as String = "SELECT FAQID, F.FAQCategoryID, F.Description, FC.Name AS CategoryName FROM tblFAQ F INNER JOIN tblFAQCategory FC ON F.FAQCategoryID = FC.FAQCategoryID WHERE FAQID <= 25"

    'Set the datagrid's datasource to the datareader and databind
    Dim resultsDataSet as New DataSet()
    Dim myDataAdapter as SqlDataAdapter = New SqlDataAdapter(strSQL, myConnection) 
    myDataAdapter.Fill(resultsDataSet)   

    dgPopularFAQs.DataSource = resultsDataSet
    dgPopularFAQs.DataBind()
  End Sub


  Function GetCategories() as DataSet
    'Populate the ddlDataSet
    Const strSQLDDL as String = _
         "SELECT FAQCategoryID, Name FROM tblFAQCategory ORDER BY Name"    
    
    Dim myDataAdapter as SqlDataAdapter = New _
                          SqlDataAdapter(strSQLDDL, myConnection)    
    
    myDataAdapter.Fill(ddlDataSet, "Categories")

    Return ddlDataSet
  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_Edit(sender As Object, e As DataGridCommandEventArgs)
    dgPopularFAQs.EditItemIndex = e.Item.ItemIndex
    BindData()
    
    dgPopularFAQs.ShowFooter = False
  End Sub

  Sub dgPopFAQs_Cancel(sender As Object, e As DataGridCommandEventArgs)
    dgPopularFAQs.EditItemIndex = -1
    BindData()
    
    dgPopularFAQs.ShowFooter = True
  End Sub

  Sub dgPopFAQs_Update(sender As Object, e As DataGridCommandEventArgs)
    Response.Write("<b>The Update Command has no effect on this live demo...</b>")
    
    dgPopularFAQs.EditItemIndex = -1
    BindData()
  End Sub
  
  Sub dgPopFAQs_Insert(sender as Object, e As DataGridCommandEventArgs)
    If e.CommandName = "Insert" then
      Dim txtNewDescription as TextBox = e.Item.FindControl("txtNewDescription")
      Dim lstCategoriesInsert 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>" & lstCategoriesInsert.SelectedItem.Text & "</li>")
      Response.Write("<li>" & txtNewDescription.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"
		HeaderStyle-HorizontalAlign="Center"
		HeaderStyle-BackColor="Red"
		HeaderStyle-ForeColor="White"
		HeaderStyle-Font-Bold="True"
		HeaderStyle-Font-Name="Verdana"
		AlternatingItemStyle-BackColor="#dddddd"
		ItemStyle-Font-Name="Verdana"

		OnEditCommand="dgPopFAQs_Edit"
		OnCancelCommand="dgPopFAQs_Cancel"
		OnUpdateCommand="dgPopFAQs_Update"
		OnItemCommand="dgPopFAQs_Insert"
		
		ShowFooter="True">
	
	  <Columns>
	    <asp:EditCommandColumn EditText="Edit" CancelText="Cancel" UpdateText="OK" />
	    <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>
            <%# DataBinder.Eval(Container.DataItem, "CategoryName") %>
	      </ItemTemplate>
	      
	      <EditItemTemplate>
	        <asp:DropDownList runat="server" id="lstCategories"
	              DataValueField="FAQCategoryID"  DataTextField="Name"
	              DataSource='<%# GetCategories() %>'
	              SelectedIndex='<%# GetSelectedIndex(Container.DataItem("FAQCategoryID")) %>' />
	      </EditItemTemplate>
	      
   	      <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>
	      
	      <EditItemTemplate>
	        <asp:TextBox runat="server" Text='<%# Container.DataItem("Description") %>' 
	               Columns="80" />
	      </EditItemTemplate>
	      
	      <FooterTemplate>
	        <asp:DropDownList runat="server" id="lstFAQsByCategory"
	              DataValueField="FAQID"  DataTextField="Description"
	              DataSource='<%# GetFAQsByCategory(-1) %>' />
	      </FooterTemplate>
	    </asp:TemplateColumn>
	  </Columns>	
	</asp:datagrid>
  </form>

[Return to the article]