• How to Populate Dropdown based on another Dropdown

    Posted on September 19, 2012 by in ASP.NET, C#, Dotnet

    In this article, I am going to demonstrate an example on how to populate dropdown based on another dropdown.

    Database Connection:
    I am going to use AdventureWorks for this example. Add the following connectionstring element to the web.config.

    <connectionStrings>
    add name="Sql" connectionString="Data Source=(local);Initial Catalog=AdventureWorks;User=testuser;Password=testuser;" providerName="System.Data.SqlClient"/>    
    </connectionStrings>
    

    Page Design:
    I have created a simple page with couple of dropdowns ddlCountry and ddlState. Onselectedindexchanged event handler property of ddlCountry is set to ddlCountry_SelectedIndexChanged. Also the autopostback property of the ddlCountry is set to true, so that when user selects a country from the dropdown or changes his selection, page is posted back to the server and ddlCountry_SelectedIndexChanged is invoked.

    <div>  
        <table>
            <tr>
                <td>Country: </td>
                <td><asp:DropDownList ID="ddlCountry" runat="server" 
                    AutoPostBack="true" onselectedindexchanged="ddlCountry_SelectedIndexChanged"></asp:DropDownList></td>
            </tr>
            <tr>
                <td>State: </td>
                <td><asp:DropDownList ID="ddlState" runat="server"></asp:DropDownList></td>
            </tr>
        </table>                          
    </div>
    

    Source Code:
    When page is initally loaded, we bind both dropdownlists in the page_load function.

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            LoadCountryDropDown();
            LoadStatesDropDown();
        }
    }
    

    In the LoadCountryDropdown  function, we fetch the list of countries from the Person.CountryRegion table using ADO.NET API and the resultset is bound to the country dropdown.

    private void LoadCountryDropDown()
    {
        ddlCountry.DataSource = RetrieveCountries();
        ddlCountry.DataValueField = "CountryRegionCode";
        ddlCountry.DataTextField = "Name";
        ddlCountry.DataBind();
    }
    public static DataSet RetrieveCountries()
    {
        //fetch the connection string from web.config
        string connString = ConfigurationManager.ConnectionStrings["Sql"].ConnectionString;
    
        //SQL statement to fetch countries
        string sql = @"SELECT CountryRegionCode, Name FROM Person.CountryRegion";
    
        DataSet dsCountries = new DataSet();
        //Open SQL Connection
        using (SqlConnection conn = new SqlConnection(connString))
        {
            conn.Open();
            //Initialize command object
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                //Fill the result set
                adapter.Fill(dsCountries);
            }
        }
        return dsCountries;
    }
    

    When user selects a country from the dropdown, ddlCountry_SelectedIndexChanged event handler is invoked. In this function, we call LoadStatesDropDown. In LoadStatesDropDown function, we fetch states belonging to selected country and bound to the states dropdown.

    private void LoadStatesDropDown()
    {
        ddlState.DataSource =RetrieveStatesByCountry(ddlCountry.SelectedValue);
        ddlState.DataValueField = "StateProvinceID";
        ddlState.DataTextField = "Name";
        ddlState.DataBind();
    }
    public static DataSet RetrieveStatesByCountry(string countryCode)
    {
        //fetch the connection string from web.config
        string connString = ConfigurationManager.ConnectionStrings["Sql"].ConnectionString;
    
        //SQL statement to fetch states            
        string sql = @"SELECT StateProvinceID, Name FROM Person.StateProvince WHERE CountryRegionCode = '" + countryCode + "'";
    
        DataSet dsState = new DataSet();
        //Open SQL Connection
        using (SqlConnection conn = new SqlConnection(connString))
        {
            conn.Open();
            //Initialize command object
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                //Fill the result set
                adapter.Fill(dsState);
            }
        }
        return dsState;
    }
    

    Output:
    Run the application and countries are populated into countries dropdown and states belonging to the selected country are also populated in states dropdown.
    Populate Dropdown based on another Dropdown 1

    Select “United States” from the country dropdown and page is posted back to the server and states dropdown will be populated states belonging to united states.
    Populate Dropdown based on another Dropdown 2

    Be Sociable, Share!
      Post Tagged with , ,

    Written by

    Software architect with over 10 years of proven experience in designing & developing n-tier and web based software applications, for Finance, Telecommunication, Manufacturing, Internet and other Commercial industries. He believes that success depends on one's ability to integrate multiple technologies to solve a simple as well as complicated problem.

    View all articles by

    Email : [email protected]

    Leave a Reply