Playing With Dropdown List in an ASP.Net Web Application

The requirement is for three Dropdown List Controls, the contents of which are populated from a database table created in SQL Server. Also until we click a value in the first DDL, the second should not be activated and the same case is with the third DDL.

Solution

Step 1

Create a Web Application Project in ASP.NET and create 3 Dropdown Lists in it. The form should look such as shown below.

Dropdown List

OR

Paste the following code into the HTML file of the ASPX Page.

  1. <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="index.aspx.cs" Inherits="CascadingDDLDemo.index" %>  
  2.   
  3. <!DOCTYPE html>  
  4.   
  5. <html>  
  6. <head runat="server">  
  7.     <title></title>  
  8. </head>  
  9. <body>  
  10.     <form id="form1" runat="server">  
  11.         <div>  
  12.             <asp:DropDownList ID="DropDownList1" runat="server" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged" AutoPostBack="True" DataTextField="CountryName" DataValueField="CountryID"></asp:DropDownList>  
  13.             <br />  
  14.             <br />  
  15.             <br />  
  16.             <asp:DropDownList ID="DropDownList2" runat="server" OnSelectedIndexChanged="DropDownList2_SelectedIndexChanged" AutoPostBack="True" DataTextField="StateName" DataValueField="StateID"></asp:DropDownList>  
  17.             <br />  
  18.             <br />  
  19.             <br />  
  20.             <asp:DropDownList ID="DropDownList3" runat="server" DataTextField="CityName" DataValueField="CityID"></asp:DropDownList>  
  21.         </div>  
  22.     </form>  
  23. </body>  
  24. </html>  

Step 2

Open SQL Server Management Studio and execute the following SQL Queries.

Create Database CascadingDDLDemo.

  1. Create Database CascadingDDLDemo  
  2.   
  3. Use CascadingDDLDemo  
  4.   
  5. Create Table tblCountries(CountryID int Primary Key, CountryName nvarchar(50))  
  6. Create Table tblStates(StateID int Primary Key, StateName nvarchar(50), CountryID int FOREIGN KEY REFERENCES tblCountries(CountryID))  
  7. Create Table tblCities(CityID int Primary Key, CityName nvarchar(50), StateID int FOREIGN KEY REFERENCES tblStates(StateID))  
  8.   
  9. Insert Into tblCountries Values (1,'USA')  
  10. Insert Into tblCountries Values (2,'Europe')  
  11. Insert Into tblCountries Values (3,'India')  
  12.   
  13. Insert Into tblStates Values (1,'New York',1)  
  14. Insert Into tblStates Values (2,'California',1)  
  15. Insert Into tblStates Values (3,'Albania',2)  
  16. Insert Into tblStates Values (4,'Austria',2)  
  17. Insert Into tblStates Values (5,'Uttar Pradesh',3)  
  18. Insert Into tblStates Values (6,'Maharashtra',3)  
  19.   
  20. Insert Into tblCities Values (1,'Bufflo',1)  
  21. Insert Into tblCities Values (2,'Yonkers',1)  
  22. Insert Into tblCities Values (3,'Richmond',2)  
  23. Insert Into tblCities Values (4,'Norwalk',2)  
  24. Insert Into tblCities Values (5,'Tirana',3)  
  25. Insert Into tblCities Values (6,'Fier',3)  
  26. Insert Into tblCities Values (7,'Hard',4)  
  27. Insert Into tblCities Values (8,'Enns',4)  
  28. Insert Into tblCities Values (9,'Allahbad',5)  
  29. Insert Into tblCities Values (10,'Lucknow',5)  
  30. Insert Into tblCities Values (11,'Aurangabad',6)  
  31. Insert Into tblCities Values (12,'Akola',6)  
  32.   
  33. Create Procedure spGetCountries  
  34. As  
  35. Begin  
  36.     Select CountryName,CountryID from tblCountries  
  37. End  
  38.   
  39. Create Procedure spGetStates @CountryID int  
  40. As  
  41. Begin  
  42.     Select StateName,StateID from tblStates where CountryID = @CountryID  
  43. End  
  44.   
  45. Create Procedure spGetCities @StateID int  
  46. As  
  47. Begin  
  48.     Select CityName,CityID from tblCities where StateID = @StateID  
  49. End  

Step 3

Return to Visual Studio and press the F7 key and paste in the following code.

  1. using System;  
  2. using System.Configuration;  
  3. using System.Data;  
  4. using System.Data.SqlClient;  
  5. using System.Web.UI.WebControls;  
  6.   
  7. namespace CascadingDDLDemo  
  8. {  
  9.     public partial class index : System.Web.UI.Page  
  10.     {  
  11.         protected void Page_Load(object sender, EventArgs e)  
  12.         {  
  13.             if (!IsPostBack)  
  14.             {  
  15.                 DropDownList2.Enabled = false;  
  16.                 DropDownList3.Enabled = false;  
  17.                 DropDownList1.DataSource = getData("spGetCountries"null);  
  18.                 DropDownList1.DataBind();  
  19.   
  20.                 ListItem LICountry = new ListItem("----Select----""-1");  
  21.                 DropDownList1.Items.Insert(0, LICountry);  
  22.                 ListItem LIState = new ListItem("----Select----""-1");  
  23.                 DropDownList2.Items.Insert(0, LIState);  
  24.                 ListItem LICity = new ListItem("----Select----""-1");  
  25.                 DropDownList3.Items.Insert(0, LICity);  
  26.   
  27.             }  
  28.         }  
  29.         protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)  
  30.         {  
  31.             if (DropDownList1.SelectedValue == "-1")  
  32.             {  
  33.                 DropDownList2.SelectedIndex = 0;  
  34.                 DropDownList3.SelectedIndex = 0;  
  35.                 DropDownList2.Enabled = false;  
  36.                 DropDownList3.Enabled = false;  
  37.             }  
  38.             else  
  39.             {  
  40.                 DropDownList2.Enabled = true;  
  41.                 SqlParameter Parameter = new SqlParameter("@CountryID", DropDownList1.SelectedValue);  
  42.                 DropDownList2.DataSource = getData("spGetStates", Parameter);  
  43.                 DropDownList2.DataBind();  
  44.   
  45.                 ListItem LIState = new ListItem("----Select----""-1");  
  46.                 DropDownList2.Items.Insert(0, LIState);  
  47.   
  48.                 DropDownList3.SelectedIndex = 0;  
  49.                 DropDownList3.Enabled = false;  
  50.             }  
  51.         }  
  52.   
  53.         protected void DropDownList2_SelectedIndexChanged(object sender, EventArgs e)  
  54.         {  
  55.             if (DropDownList2.SelectedValue == "-1")  
  56.             {  
  57.                 DropDownList3.SelectedIndex = 0;  
  58.                 DropDownList3.Enabled = false;  
  59.             }  
  60.             else  
  61.             {  
  62.                 DropDownList3.Enabled = true;  
  63.                 SqlParameter Parameter = new SqlParameter("@StateID", DropDownList2.SelectedValue);  
  64.                 DropDownList3.DataSource = getData("spGetCities", Parameter);  
  65.                 DropDownList3.DataBind();  
  66.   
  67.                 ListItem LICity = new ListItem("----Select----""-1");  
  68.                 DropDownList3.Items.Insert(0, LICity);  
  69.             }  
  70.         }  
  71.         private DataSet getData(string Proc, SqlParameter Parameter)  
  72.         {  
  73.             string CS = ConfigurationManager.ConnectionStrings["CS"].ConnectionString;  
  74.             using (SqlConnection con = new SqlConnection(CS))  
  75.             {  
  76.                 con.Open();  
  77.                 SqlDataAdapter DA = new SqlDataAdapter(Proc, con);  
  78.                 DA.SelectCommand.CommandType = CommandType.StoredProcedure;  
  79.                 if (Parameter != null)  
  80.                 {  
  81.                     DA.SelectCommand.Parameters.Add(Parameter);  
  82.                 }  
  83.                 DataSet DS = new DataSet();  
  84.                 DA.Fill(DS);  
  85.                 return DS;  
  86.             }  
  87.         }  
  88.     }  
  89. }  

Step 4

Go the Solution Explorer in the project and double-click on the web.config file and paste in the following code.

  1. <?xml version="1.0"?>  
  2. <configuration>  
  3.   <system.web>  
  4.     <compilation debug="true" targetFramework="4.5" />  
  5.     <httpRuntime targetFramework="4.5" />  
  6.   </system.web>  
  7.   <connectionStrings>  
  8.     <add name="CS" providerName="System.Data.SqlClient" connectionString="Data Source=(local);Initial Catalog=CascadingDDLDemo;Integrated Security=True"/>  
  9.   </connectionStrings>  
  10. </configuration>  

Step 5

Press F5 to run the application. Remember to name the project CascadingDDLDemo if you are running exactly the same preceding code. After running the application, it should look as in the following screenshot.

screenshot

Explanation of the Preceding Code

Explanation of Step 1

We added 3 Dropdown List controls from the toolbox. The main focus is on the first and the second Dropdown List because when the data is selected in them, that data should be posted back to the server and on the basis of which the values will be populated in the next Dropdown List control. So we set the AutoPostBack property to “true” of these two controls.

Explanation of Step 2

  • We created the 3 tables Country, State and City. All of them are interrelated with each other. On the basis of the country selection, the states are populated and on the basis of the state selection, the cities are populated. So, we related them using the primary and foreign keys. If you are new to this concept, please learn about database keys in SQL Server.
  • Then, we inserted some sample data into these tables.
  • Then we created 3 Stored Procedures to fetch the data in the ASP.NET web application and on the basis of that data, we will populated our Dropdown lists.

Explanation of Step 3

First, we will create a private method that fetches the data through the database on the basis of the two arguments the Stored Procedure we created in SQL and the parameters they accepts. We have named this method getData().

  • A simple ADO.NET code is used to fetch the data using the Stored Procedures and remember the method is returning the DataSet, in other words after fetching the data, it stores the values in the dataset. This increases the productivity of the application.
  • In the Page_Load method, we are disabling the second and the third DDL and populate the first one using our private method. A ListItem is inserted at 0 index of the DDL to just show a “----Select----”. So, we have set the Data Source to the method that fetches data and then DataBind() to bind the data to the control.
  • The same procedure is done with the other two DDLs. But it is done on the SelectedIndexChanged event of DDLs. So be careful.

Explanation of Step 4

We have just added the connection string to the Web.config file of the application.

Please comment in case of any queries.

I hope you like the preceding article.