I decided to write this sample demo because I always encounter this kind of problem in the ASPNET Forum . So here's a sample (one way) solution on how to populate the second DropDownList based on the value selected on the first DropDownList using the ADO.NET way.
Assuming that DropDownList1 contains the list of countries and we need to populate the list of States in a particular country based on the first DropDownList selection.
protected void PopulateDropDownList1(){
string queryString = "SELECT * FROM Table1";
SqlClient.SqlConnection connection = new SqlClient.SqlConnection("Your Connection String here");
SqlClient.SqlCommand command = new SqlClient.SqlCommand(queryString, connection);
connection.Open();
DataTable dt = new DataTable();
SqlDataAdapter ad = new SqlDataAdapter(command);
ad.Fill(dt);
if (dt.Rows.Count > 0) {
DropDownList1.DataSource = dt;
DropDownList1.DataTextField = "CountryName";
DropDownList1.DataValueField = "CountryName";
DropDownList1.DataBind();
}
connection.Close();
}
protected void PopulateDropDownList2(string country)
{
string queryString = "SELECT States FROM FROM Table2 WHERE Country = @country";
SqlClient.SqlConnection connection = new SqlClient.SqlConnection("YOUR CONNECTION STRING HERE");
SqlClient.SqlCommand command = new SqlClient.SqlCommand(queryString, connection);
command.Parameters.AddWithValue("@country", country);
connection.Open();
DataTable dt = new DataTable();
SqlDataAdapter ad = new SqlDataAdapter(command);
ad.Fill(dt);
DropDownList1.Items.Clear();
if (dt.Rows.Count > 0) {
DropDownList2.DataSource = dt;
DropDownList2.DataTextField = "States";
DropDownList2.DataValueField = "States";
DropDownList2.DataBind();
}
connection.Close();
}
protected void DropDownList1_SelectedIndexChanged(object sender, System.EventArgs e)
{
PopulateDropDownList2(DropDownList1.SelectedItem.Value.ToString());
}
protected void Page_Load(object sender, System.EventArgs e)
{
if (!Page.IsPostBack) {
PopulateDropDownList1();
}
}
NOTE: Don't forget to add the following Namespaces below for you to make it work
Using System.Data;
Using System.Data.SqlClient;
Also don't forget to set AutoPostBack to TRUE in your first DropDownList to fire up the SelectedIndexChanged event
Another Solution:
You can also use Cascading DropDownList .. see below for demo
Cascading DropDownList Sample Demo
That simple!