How to bind or populate data into a Dropdown List based on first or another Dropdown List in Asp.net C#

Please visit my new Web Site https://coderstechzone.com

In many forums i found this problem that's why i decide to write a post on populating a Dropdown List based on another Dropdown List. Here i will try to show how we can bind sql server data with a Dropdown List as well as after selection populate other Dropdown List. To describe this situation here i will use a very common scenario like country & its regions. Lets we have a country table of ID, Name column & a region table containig ID, Name, CountryID. So its a one to many relationship in database. It will be more appropriate if we first populate Coutry Dropdown List. When user select a country then we will populate selected country regions only in the second Dropdown List. To do that we have to handle Country combo box
SelectedIndexChanged Event. Don't forget to set the AutoPostBack property of Country Dropdown List to True & to import using System.Data.SqlClient; namespace.

Look at sample sql server tables & values:

Add a new aspx page in your project & place below two Drodown List:

<asp:DropDownList ID="cboCountry" runat="server" AutoPostBack="True" OnSelectedIndexChanged="cboCountry_SelectedIndexChanged">
</asp:DropDownList>

<asp:DropDownList ID="cboRegion" runat="server"></asp:DropDownList>

Now we need to populate Country Dropdown List first. So for ease i think Page_Load event is the best. Now put the below code under your Page_Load method:

if (!IsPostBack)
{
// Read sql server connection string from web.config file
string sConstr = ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString;
SqlConnection Conn = new SqlConnection(sConstr);
DataTable dt = new DataTable("tbl");

using (Conn)
{
Conn.Open();
SqlCommand comm = new SqlCommand("SELECT ID,Name FROM Country", Conn);
SqlDataAdapter da = new SqlDataAdapter(comm);
da.Fill(dt);
}

cboCountry.DataSource = dt;
cboCountry.DataTextField = "Name";
cboCountry.DataValueField = "ID";
cboCountry.DataBind();
}

Now open the page in design view. Right click on Country Dropdown List to open proprties then from property click on event to select SelectedIndexChanged Event & put the below code segment under SelectedIndexChanged Event Handler:

protected void cboCountry_SelectedIndexChanged(object sender, EventArgs e)
{
string sConstr = ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString;
SqlConnection Conn = new SqlConnection(sConstr);
DataTable dt = new DataTable("tbl");

using (Conn)
{
Conn.Open();
SqlCommand comm = new SqlCommand("SELECT ID,Name FROM Region WHERE CountryID=" + cboCountry.SelectedValue, Conn);
SqlDataAdapter da = new SqlDataAdapter(comm);
da.Fill(dt);
}

cboRegion.DataSource = dt;
cboRegion.DataTextField = "Name";
cboRegion.DataValueField = "ID";
// Bind sql server data into the Dropdown List
cboRegion.DataBind();
}

Now run the page, hope our goal is achieved.

If you look into above code segment you can realize that there is a lot of scope to reduce codes. Ok now go one step ahead. To do that add a static class in your project & named it clsDbUtility. In this static class we write two static method. One is to retrieve data from our sql server database & another one is for populate Dropdown List. If we can do it then definitely it will ensure code reusability. Write code in your static class by the following way:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public class clsDbUtility
{
public clsDbUtility(){}

public static DataTable ExecuteQuery(string SQLstring)
{
string sConstr = ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString;
SqlConnection Conn = new SqlConnection(sConstr);
DataTable dt = new DataTable("tbl");

using (Conn)
{
Conn.Open();
SqlCommand comm = new SqlCommand(SQLstring, Conn);
comm.CommandTimeout = 0;
SqlDataAdapter da = new SqlDataAdapter(comm);
da.Fill(dt);
}

return dt;
}

public static void FillCombo(DropDownList dropDownList, string dataValueField, string dataTextField, DataTable dataTbl, bool bHasBlank)
{
dropDownList.DataTextField = dataTextField;
dropDownList.DataValueField = dataValueField;
dropDownList.DataSource = dataTbl;
dropDownList.DataBind();

if (bHasBlank)
dropDownList.Items.Insert(0, new ListItem());
}
}

Ok now our class is ready. One question may arise how we can use it from our aspx page. Its simple. Have a look at the modifed code segment:

protected void cboCountry_SelectedIndexChanged(object sender, EventArgs e)
{
clsDbUtility.FillCombo(cboRegion, "ID", "Name", clsDbUtility.ExecuteQuery("SELECT ID,Name FROM Region WHERE CountryID=" + cboCountry.SelectedValue), true);
}

Modify your Page_Load Event according to the above example. Here i want to notify one interesting point is that if you look at the parameter bHasBlank then if you pass true the required field validator will also works for the first parameter reference.

Keep experimenting to reduce your code. It will be best if you use two class one is for DAL & another one is for UI. Hope you got my point. You can also use above technique for Gridview, Repeater, Detailsview control to bind or populate data from sql server database or from other datasources that support ADO.net.