Asp.net - Creating 2 linked Drop Down Lists from Database

Question

I have 2 linked database tables (Similar to Country -> State) - how can I dynamically populate 2 drop down lists with these tables such that:

If I pick a Country, only the states for that country show up in the second drop down list?

Update: There seems to be a solution on this page -> http://stackoverflow.com/questions/936499/populate-dropdownlist-based-upon-other-dropdownlist-vb -> the one that uses the SQL Data connection on the aspx page itself, databinding the dropdowns - how do I do this with LINQ? I think I can figure it out from here.

Answer 1

You have your state control hidden, and autopostback = true on your Country dropdownlist.

On the SelectedIndexChanged, you pull that value and query your database with that, and then set your state drop down list to visible.

In the ASPX page:

<asp:DropDownList ID="CountryDD" runat="server" AutoPostBack="true" OnSelectedIndexChanged="CountryDD_IndexChanged" />

<asp:DropDownList ID="StateDD" runat="server" Visible="false"/>

In the Code Behind:

Protected Sub CountryDD_IndexChanged(ByVal sender As Object, ByVal e As EventArgs)
    StateDD.Items.Clear()
    Using conn As SqlConnection = New SqlConnection("Connection String")
        conn.Open()
        Using cmd As SqlCommand = New SqlCommand("get_states_by_country", conn)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@country_id", CountryDD.SelectedValue)
            Using dr As SqlDataReader = cmd.ExecuteReader()
                StateDD.Items.Add(New ListItem(dr("state_name").ToString(), dr("state_id")))
            End Using
        End Using
    End Using
End Sub

Answer 2

This will require one of two things:

1. Non-trivial JavaScript. (Make it easier with JQuery. Provide one set of data associating states with countries, and use JavaScript to create/populate the list each time the user selects a new country.
2. Lots of communication back and forth from the server. (Each time the user picks a country, you ask the server for a new list of states, and use AJAX to update the state drop-down.)

Answer 3

If you're not opposed to using the Microsoft Ajax Control Toolkit, they have a control called the Cascading Dropdown that would do what you want.

Answer 4

I ended up using one of the answers on http://stackoverflow.com/questions/936499/populate-dropdownlist-based-upon-other-dropdownlist-vb - I put the 2 dropdowns in an AJAX Update Panel. This is the same as databyss's answer, except it uses data binding in the page definition itself, no back-end code required.

Code:

<asp:UpdatePanel ID="UpdatePanel1" runat="server">
    <ContentTemplate>

        <asp:DropDownList ID="DropDownList1" runat="server" 
            DataSourceID="Countries" DataTextField="Name" DataValueField="Id"
            AutoPostBack="true"
        />
        <asp:LinqDataSource ID="Countries" runat="server" 
            ContextTypeName="YourDataContext" 
            Select="new (Id, Name)" TableName="AssessmentTypes">
        </asp:LinqDataSource>

        <asp:DropDownList ID="DropDownList2" runat="server" 
            DataSourceID="States" DataTextField="Name" DataValueField="Id"
        />
        <asp:LinqDataSource ID="States" runat="server" 
            ContextTypeName="YourDataContext" 
            Select="new (Id, Name, CountryId)" TableName="Modules" 
            Where="CountryId == @CountryId">
            <WhereParameters>
                <asp:ControlParameter ControlID="DropDownList1" DbType="Guid" 
                    Name="CountryId" PropertyName="SelectedValue" />
            </WhereParameters>
        </asp:LinqDataSource>    

    </ContentTemplate>
</asp:UpdatePanel>