Creating Cascading DropDownLists in ASP.Net (연속적인 셀렉트박스...

연속적인 선택

대륙 : 아시아 --- 한국 ------------------------------ 서울

일본 부산

중국 대전

광주

이런식으루... 선택된 데이터의 하위 데이터 리스트를 불러올때

asp.net 이 아닐 경우에는 ajax 로 호출 해서 뿌려주면 되겠다.

하지만 asp.net 은 간단하게 처리할 수 있으니 나는 아래의 방법으로 처리함.

DB에서 안불러오고 그냥 뿌려줄 경우에는

CCategory2.Items.Clear(); // CCategory2 selectbox 초기화

if (Category1 == "new") {

CCategory2.Items.Add(new ListItem("없음", "")); // 없음

} else if (Category1 == "Set-Top Box Remote"){

CCategory2.Items.Add(new ListItem("Smart Remote", "Smart Remote"));

CCategory2.Items.Add(new ListItem("Cable/IPTV/SAT", "Cable/IPTV/SAT"));

}

Cascading DropDownList means a series of dependent DropDownLists where one DropDownList is dependent on the parent or previous DropDownList and is populated based on the item selected by the user. On many occasions we need to make use of Cascading DropDownLists as I have here

Continent - Country - City

City is dependent on Country and in turn Country is dependent on the Continent thus we need a series of Cascading DropDownList here.

Database Design

For this tutorial I have created three tables whose designs are given below

Continents Table

Countries Table

Cities Table

Connection String

Front End Design

I have used 3 DropDownLists one for each entity the mapping is shown below

1. ddlContinents - List of Continents

2. ddlCountry- List of Countries

3. ddlCity - List of Cities

Select Continent : Select Country : Select City :

I have added OnSelectedIndexChanged Event to all DropDownLists and also AutoPostBack set property to true.

The User Interface looks as below

Populating the Continents DropDownList

On the Page_Load Event of the Page I am populating the Continents DropDownList

C#

protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { ddlContinents.AppendDataBoundItems = true; String strConnString = ConfigurationManager .ConnectionStrings["conString"].ConnectionString; String strQuery = "select ID, ContinentName from Continents"; SqlConnection con = new SqlConnection(strConnString); SqlCommand cmd = new SqlCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = strQuery; cmd.Connection = con; try { con.Open(); ddlContinents.DataSource = cmd.ExecuteReader(); ddlContinents.DataTextField = "ContinentName"; ddlContinents.DataValueField = "ID"; ddlContinents.DataBind(); } catch (Exception ex) { throw ex; } finally { con.Close(); con.Dispose(); } } }

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load If Not IsPostBack Then ddlContinents.AppendDataBoundItems = True Dim strConnString As [String] = ConfigurationManager _ .ConnectionStrings("conString").ConnectionString Dim strQuery As [String] = "select ID, ContinentName from Continents" Dim con As New SqlConnection(strConnString) Dim cmd As New SqlCommand() cmd.CommandType = CommandType.Text cmd.CommandText = strQuery cmd.Connection = con Try con.Open() ddlContinents.DataSource = cmd.ExecuteReader() ddlContinents.DataTextField = "ContinentName" ddlContinents.DataValueField = "ID" ddlContinents.DataBind() Catch ex As Exception Throw ex Finally con.Close() con.Dispose() End Try End If End Sub

Populating the Country DropDownList

Next on the SelectedIndexChanged Event of the parent Continent DropDownList I am populating the Countries DropDownList based on the ID of the Continent Selected by the user

C#

protected void ddlContinents_SelectedIndexChanged(object sender, EventArgs e) { ddlCountry.Items.Clear(); ddlCountry.Items.Add(new ListItem("--Select Country--", "")); ddlCity.Items.Clear(); ddlCity.Items.Add(new ListItem("--Select City--", "")); ddlCountry.AppendDataBoundItems = true; String strConnString = ConfigurationManager .ConnectionStrings["conString"].ConnectionString; String strQuery = "select ID, CountryName from Countries " + "where ContinentID=@ContinentID"; SqlConnection con = new SqlConnection(strConnString); SqlCommand cmd = new SqlCommand(); cmd.Parameters.AddWithValue("@ContinentID", ddlContinents.SelectedItem.Value); cmd.CommandType = CommandType.Text; cmd.CommandText = strQuery; cmd.Connection = con; try { con.Open(); ddlCountry.DataSource = cmd.ExecuteReader(); ddlCountry.DataTextField = "CountryName"; ddlCountry.DataValueField = "ID"; ddlCountry.DataBind(); if (ddlCountry.Items.Count > 1) { ddlCountry.Enabled = true; } else { ddlCountry.Enabled = false; ddlCity.Enabled = false; } } catch (Exception ex) { throw ex; } finally { con.Close(); con.Dispose(); } }

VB.Net

Protected Sub ddlContinents_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) ddlCountry.Items.Clear() ddlCountry.Items.Add(New ListItem("--Select Country--", "")) ddlCity.Items.Clear() ddlCity.Items.Add(New ListItem("--Select City--", "")) ddlCountry.AppendDataBoundItems = True Dim strConnString As [String] = ConfigurationManager _ .ConnectionStrings("conString").ConnectionString Dim strQuery As [String] = "select ID, CountryName from Countries " _ & "where ContinentID=@ContinentID" Dim con As New SqlConnection(strConnString) Dim cmd As New SqlCommand() cmd.Parameters.AddWithValue("@ContinentID", _ ddlContinents.SelectedItem.Value) cmd.CommandType = CommandType.Text cmd.CommandText = strQuery cmd.Connection = con Try con.Open() ddlCountry.DataSource = cmd.ExecuteReader() ddlCountry.DataTextField = "CountryName" ddlCountry.DataValueField = "ID" ddlCountry.DataBind() If ddlCountry.Items.Count > 1 Then ddlCountry.Enabled = True Else ddlCountry.Enabled = False ddlCity.Enabled = False End If Catch ex As Exception Throw ex Finally con.Close() con.Dispose() End Try End Sub

You will notice I am passing the ID of the continent as parameter to the Query using SelectedItemValue property of the DropDownList thus the query returns the records (Countries) for that Continent ID which are then bind to the Country DropDownList

Populating the City DropDownList

Now on the selection of the Country I am filling the Cities for that country into the City DropDownList.

C#

protected void ddlCountry_SelectedIndexChanged(object sender, EventArgs e) { ddlCity.Items.Clear(); ddlCity.Items.Add(new ListItem("--Select City--", "")); ddlCity.AppendDataBoundItems = true; String strConnString = ConfigurationManager .ConnectionStrings["conString"].ConnectionString; String strQuery = "select ID, CityName from Cities " + "where CountryID=@CountryID"; SqlConnection con = new SqlConnection(strConnString); SqlCommand cmd = new SqlCommand(); cmd.Parameters.AddWithValue("@CountryID", ddlCountry.SelectedItem.Value); cmd.CommandType = CommandType.Text; cmd.CommandText = strQuery; cmd.Connection = con; try { con.Open(); ddlCity.DataSource = cmd.ExecuteReader(); ddlCity.DataTextField = "CityName"; ddlCity.DataValueField = "ID"; ddlCity.DataBind(); if (ddlCity.Items.Count > 1) { ddlCity.Enabled = true; } else { ddlCity.Enabled = false; } } catch (Exception ex) { throw ex; } finally { con.Close(); con.Dispose(); } }

VB.Net

Protected Sub ddlCountry_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) ddlCity.Items.Clear() ddlCity.Items.Add(New ListItem("--Select City--", "")) ddlCity.AppendDataBoundItems = True Dim strConnString As [String] = ConfigurationManager _ .ConnectionStrings("conString").ConnectionString Dim strQuery As [String] = "select ID, CityName from Cities " _ & "where CountryID=@CountryID" Dim con As New SqlConnection(strConnString) Dim cmd As New SqlCommand() cmd.Parameters.AddWithValue("@CountryID", _ ddlCountry.SelectedItem.Value) cmd.CommandType = CommandType.Text cmd.CommandText = strQuery cmd.Connection = con Try con.Open() ddlCity.DataSource = cmd.ExecuteReader() ddlCity.DataTextField = "CityName" ddlCity.DataValueField = "ID" ddlCity.DataBind() If ddlCity.Items.Count > 1 Then ddlCity.Enabled = True Else ddlCity.Enabled = False End If Catch ex As Exception Throw ex Finally con.Close() con.Dispose() End Try End Sub

Above I am firing a query on the Cities Table and getting all the cities that belong to that country which was selected by the user.

Displaying the Results

Finally on the SelectedIndexChanged event of the City DropDownList I am displaying the complete selections done by the user.

C#

protected void ddlCity_SelectedIndexChanged(object sender, EventArgs e) { lblResults.Text = "You Selected " + ddlContinents.SelectedItem.Text + " -----> " + ddlCountry.SelectedItem.Text + " -----> " + ddlCity.SelectedItem.Text; }

VB.Net

Protected Sub ddlCity_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) lblResults.Text = "You Selected " & _ ddlContinents.SelectedItem.Text & " -----> " & _ ddlCountry.SelectedItem.Text & " -----> " & _ ddlCity.SelectedItem.Text End Sub

The output is shown in the screenshot below

Thus we come to the end of this article. Download the sample source code in VB.Net and C# using the link below

from http://otep.tistory.com/234 by ccl(A) rewrite - 2020-03-07 07:56:25