Dependent DropDownList in FormView

Question

I am trying to create two drop down lists in a form view where the values that are displayed in the second drop down are dependent upon the first drop down.The first list contains the class numbers contained in a sql table "Classes" and the second drop down list contains the class sections in the same sql table "Classes". I want to be able to select a class number and only have the sections that correspond to that class number pop up.

Example Classes Table:

Number:   Section:   SLN:  
210       1          A-1  
210       2          A-2  
210       3          A-3  
340       1          B-1  
340       7          B-7 

I currently have the first list for Number set up properly using

   <asp:DropDownList ID="ddlNumber" runat="server" 
    DataSourceID="SqlDSClasses"
     AutoPostBack="True" DataTextField="Number" DataValueField="Number">
    </asp:DropDownList> 
for the drop down and

<asp:SqlDataSource ID="SqlDSClasses" runat="server"       
ConnectionString="<%$ ConnectionStrings:ReinstatementCS %>" 
SelectCommand="SELECT [Prefix], [Number], [Location], [SLN],                              
[StartTime], [EndTime], [ClassDay], [ClassCredit], [ClassSection] FROM [Classes]">   
</asp:SqlDataSource>
for the corresponding SqlDataSource

So far I have tried to use

SELECT [ClassSection] FROM [Classes] WHERE [Number] = NumberDropDownList.DataValueField
for the Section list:

<asp:DropDownList ID="ClassSectionDropDownList" runat="server" DataSourceID="SqlDSNumSec" 
                                            DataTextField="ClassSection" DataValueField="ClassSection"  AutoPostBack="True">
                                        </asp:DropDownList>

The Form View being used is set up as follows:

<asp:FormView ID="FVStudentClass" runat="server" DataSourceID="SqlDSStudentClass"
                        DataSourceID2="SqlDSAccess" EmptyDataText="Student Class Not Completed">
                        <EditItemTemplate>
                            <table style="width:100%;">
                                <tr>
                                    <td>
                                        Prefix:
                                    </td>
                                    <td>
                                        UCOLL</td>
                                    <td>
                                        Number:
                                    </td>
                                    <td>
                                        <asp:DropDownList ID="ddlNumber" runat="server" DataSourceID="SqlDSClasses"  AutoPostBack="True"
                                            DataTextField="Number" DataValueField="Number">
                                        </asp:DropDownList>
                                    </td>

                                    <td>

                                        Section:
                                    </td>
                                    <td>
                                        <asp:DropDownList ID="SectionDropDownList" runat="server" DataSourceID="SqlDSNumSec" 
                                            DataTextField="Section" DataValueField="Section"  AutoPostBack="True">
                                        </asp:DropDownList>
                                    </td>
                                </tr>
                            </table>
                            <asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" CommandName="Update"
                                Text="Update" />
                            &nbsp;<asp:LinkButton ID="UpdateCancelButton" runat="server" CausesValidation="False"
                                CommandName="Cancel" Text="Cancel" />
                        </EditItemTemplate>
                        <InsertItemTemplate>
                            IsTransfer:
                            <asp:CheckBox ID="IsTransferCheckBox" runat="server" 
                                Checked='<%# Bind("IsTransfer") %>' />
                            <br />
                            Prefix:
                            <asp:TextBox ID="PrefixTextBox" runat="server" Text='<%# Bind("Prefix") %>' />
                            <br />
                            Number:
                            <asp:TextBox ID="NumberTextBox" runat="server" Text='<%# Bind("Number") %>' />
                            <br />
                            Section:
                            <asp:TextBox ID="SectionTextBox" runat="server" 
                                Text='<%# Bind("Section") %>' />
                            <br />
                            ID:
                            <asp:TextBox ID="IDTextBox" runat="server" Text='<%# Bind("ID") %>' />
                            <br />
                            <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert"
                                Text="Insert" />
                            &nbsp;<asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False"
                                CommandName="Cancel" Text="Cancel" />
                        </InsertItemTemplate>
                        <ItemTemplate>
                            <table style="width:100%;">
                                <tr>
                                    <td>
                                        Prefix:</td>
                                    <td>
                                        <asp:Label ID="PrefixLabel" runat="server" Text='<%# Bind("Prefix") %>' />
                                    </td>
                                    <td>
                                        Number:</td>
                                    <td>
                                        <asp:Label ID="NumberLabel" runat="server" Text='<%# Bind("Number") %>' />
                                    </td>
                                    <td>
                                        ClassSection:</td>
                                    <td>
                                        <asp:Label ID="ClassSectionLabel" runat="server" 
                                            Text='<%# Bind("ClassSection") %>' />
                                    </td>
                                </tr>
                                <tr>
                                    <td>
                                        IsTransfer:</td>
                                    <td>
                                        <asp:CheckBox ID="IsTransferCheckBox" runat="server" 
                                            Checked='<%# Bind("IsTransfer") %>' Enabled="false" />
                                    </td>
                                </tr>
                            </table>
                            <asp:LinkButton ID="EditButton" runat="server" CausesValidation="False" CommandName="Edit"
                                Text="Edit" />
                        </ItemTemplate>
                    </asp:FormView>

with a SqlDataSource:

<asp:SqlDataSource ID="SqlDSStudentClass" runat="server" ConnectionString="<%$ ConnectionStrings:ReinstatementCS %>"

                        DeleteCommand="DELETE FROM [StudentClass] WHERE [SLN] = @SLN" 
                        InsertCommand="INSERT INTO [StudentClass] ([SLN],[ID]) VALUES (@SLN, @ID)"
                        SelectCommand="SELECT DISTINCT Classes.Number, Classes.Section, StudentClass.ID
FROM 
    StudentClass 
    LEFT JOIN AccessList ON AccessList.ALID = StudentClass.ID
    JOIN Classes ON StudentClass.SLN = Classes.SLN
WHERE ([SLN] = @SLN)" 
    <!--AccessList just gives extra information to the user-->                    
                        UpdateCommand="UPDATE [StudentClass] SET [SLN] = @SLN, [ID] = @ID WHERE [SLN] = @SLN">

                        <DeleteParameters>
                            <asp:Parameter Name="SLN" Type="Int32" />
                        </DeleteParameters>
                        <InsertParameters>
                            <asp:Parameter Name="SLN" Type="String" />
                            <asp:Parameter Name="ID" Type="String" />
                        </InsertParameters>
                        <SelectParameters>
                            <asp:QueryStringParameter Name="ID" QueryStringField="ALID" 
                                Type="String" />
                        </SelectParameters>
                        <UpdateParameters>
                            <asp:Parameter Name="ID" Type="String" />
                            <asp:Parameter Name="SLN" Type="Int32" />
                        </UpdateParameters>
                    </asp:SqlDataSource>

Example of the form in action using the example tables above:

Number Drop Down:
210                ->   select 210     
340  

Section Drop Down:
1
2                  -> select 3
3

Answer 1

I figured out how to do it: My C# code behind now looks like this:

protected void ddlNumber_SelectedIndexChanged(object sender, EventArgs e)
{
    DropDownList ddlNumber = FVStudentClass.FindControl("ddlNumber") as DropDownList;

    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ReinstatementCS"].ConnectionString);
    SqlCommand myCommand = new SqlCommand("SELECT DISTINCT ClassSection FROM Classes WHERE Number = " + ddlNumber.Text);
    myCommand.Connection = con;
    SqlDataAdapter da = new SqlDataAdapter(myCommand);
    DataTable dt = new DataTable();
    da.Fill(dt);

    DropDownList ddlSection = FVStudentClass.FindControl("ddlSection") as DropDownList;
    ddlSection.DataSource = dt;
    ddlSection.DataTextField = "ClassSection";
    ddlSection.DataValueField = "ClassSection";
    ddlSection.DataBind();
    ddlSection.Items.Insert(0, "--Select--");
}

and my .aspx dropdowns: ddlNumber:

<asp:DropDownList ID="ddlNumber" runat="server" DataSourceID="SqlDSClasses"  AutoPostBack="True" DataTextField="Number" DataValueField="Number"   onselectedindexchanged="ddlNumber_SelectedIndexChanged">
</asp:DropDownList>

ddlSection:

<asp:DropDownList ID="ddlSection" runat="server"  AutoPostBack="True"                                                     onselectedindexchanged="ddlSection_SelectedIndexChanged">
</asp:DropDownList>

I found this out combining the link from Nikita http://csharpdotnetfreak.blogspot.com/2009/03/populate-dropdown-based-selection-other.html

and this other page: http://forums.asp.net/t/1617449.aspx (ignore all the div's in the first answer)

Answer 2

Take a look at this article:

http://csharpdotnetfreak.blogspot.com/2009/03/populate-dropdown-based-selection-other.html

It explains how to achive what you want.

Also, as an alternative, you may use AJAX: http://www.asp.net/ajaxLibrary/AjaxControlToolkitSampleSite/CascadingDropDown/CascadingDropDown.aspx