Aim

We want to Select the Name of the Country from the Dropdownlist and On Selecting a Particular Country, A list of State is populated in the second Dropdownlist control from SQL Database.

Step 1: Creating the Database Structure.

  1. Create Database DDLDemo
  2. Use DDLDemo
  3. Create Table Country(ID int Identity Primary Key, Name nvarchar(50))
  4. Create Table [State](ID int Identity Primary Key, Name nvarchar(50), CountryID int FOREIGN KEY References Country(ID))
  6. Insert Into Country Values ('India')
  7. Insert Into Country Values ('USA')
  8. Insert Into Country Values ('China')
  9. Insert Into Country Values ('Australia')
  10. Insert Into Country Values ('United Kingdom')
  12. Select * from Country
  13. Select * from State
  15. Insert Into State Values ('Delhi',1)
  16. Insert Into State Values ('UP',1)
  17. Insert Into State Values ('Kanpur',1)
  18. Insert Into State Values ('Lucknow',1)
  19. Insert Into State Values ('New York',2)
  20. Insert Into State Values ('Boston',2)
  21. Insert Into State Values ('Chile',2)
  22. Insert Into State Values ('Japan',3)
  23. Insert Into State Values ('Tokyo',3)
  24. Insert Into State Values ('Shinghai',3)
  25. Insert Into State Values ('Vietnam',3)
  26. Insert Into State Values ('Delhi',4)
  27. Insert Into State Values ('Sydney',4)
  28. Insert Into State Values ('Malbourne',4)
  29. Insert Into State Values ('England',5)
  30. Insert Into State Values ('London',5)
  31. Insert Into State Values ('Singapore',5)
  33. --This is a query using Inner join which shows all the city for their respective countries.
  35. Select c.Name as Country, s.Name as State from Country c INNER JOIN State s ON c.ID = s.CountryID
Step 2: Creating the Web Application:

  1. Add a new Webform to the Web Application.
  2. Type the following code in the HTML File.

    1. <!DOCTYPE html>
    3. <html xmlns="http://www.w3.org/1999/xhtml">
    4. <head runat="server">
    5. <title></title>
    6. <link href="Content/bootstrap.min.css" rel="stylesheet" />
    7. </head>
    8. <body>
    9. <form id="form1" runat="server">
    10. <div>
    11. <table class="table text-capitalize text-center text-danger" style="font-size: larger">
    12. <thead>
    13. <tr>
    14. <td>Country</td>
    15. <td>State</td>
    16. </tr>
    17. </thead>
    18. <tbody>
    19. <tr>
    20. <td>
    21. <%--Please put Autopostback Property true--%>
    22. <asp:DropDownList ID="ddl_Country" runat="server" AutoPostBack="True"
    24. OnSelectedIndexChanged="ddl_Country_SelectedIndexChanged"></asp:DropDownList>
    25. </td>
    26. <td>
    27. <asp:DropDownList ID="ddl_State" runat="server"></asp:DropDownList>
    28. </td>
    29. </tr>
    30. </tbody>
    31. </table>
    32. </div>
    33. </form>
    34. </body>
    35. </html>

  3. Type the following code in the Code behind file by Pressing F7 Key.
    1. using System;
    2. using System.Data;
    3. using System.Data.SqlClient;
    4. namespace DDLPopulateDemo
    5. {
    6. public partial class WebForm1 : System.Web.UI.Page
    7. {
    8. string CS = "Data Source=(local);Initial Catalog=DDLDemo;Integrated Security=True"; //Creating a String to Connect to Database
    9. protected void Page_Load(object sender, EventArgs e)
    10. {
    11. if (!IsPostBack) //Used to Check that Whether the page loads first time or Second Time
    12. {
    13. using (SqlConnection con = new SqlConnection(CS)) //Created Connection to Connect to Database
    14. {
    15. con.Open(); //Opened the Connection
    16. SqlCommand cmd = new SqlCommand("Select * from Country", con); //Fetching all the Data using SQL Query through Country
    18. Table
    19. SqlDataAdapter da = new SqlDataAdapter(cmd); // Initializing a Data Adapter to Store the data
    20. DataSet ds = new DataSet(); //Created a Dataset
    21. da.Fill(ds); //Filled the Dataset with Values contained in DataAdapter
    22. ddl_Country.DataSource = ds; //Setting the Datasource of DDL to Dataset
    23. ddl_Country.DataTextField = "Name"; //This is the name of the column of Database through which we want to Show the
    25. Values
    26. ddl_Country.DataValueField = "ID"; //This is the Primary key on which the Data is fetched
    27. ddl_Country.DataBind(); //Binding the Fetched Data to the DDL
    28. }
    29. }
    30. }
    31. protected void ddl_Country_SelectedIndexChanged(object sender, EventArgs e) //When the Selected index is changed of the DDL, It
    33. postbacks the Data and This event is fired
    34. {
    35. using (SqlConnection con = new SqlConnection(CS))
    36. {
    37. con.Open();
    38. //Created a custom Query which Selects data from the State Table on the Basis of the Foreign key which was Country ID
    39. SqlCommand cmd = new SqlCommand("Select * from State where CountryID = '" + (Convert.ToInt32(ddl_Country.SelectedValue)) +
    41. "'", con);
    42. //Rest of the Code Remains same as Page_Load method
    43. SqlDataAdapter da = new SqlDataAdapter(cmd);
    44. DataSet ds = new DataSet();
    45. da.Fill(ds);
    46. ddl_State.DataSource = ds;
    47. ddl_State.DataTextField = "Name";
    48. ddl_State.DataValueField = "ID";
    49. ddl_State.DataBind();
    50. }
    51. }
    52. }
    53. }