ASP.NET

Populating ASP.NET DropDownList Control using SQL

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))    
  5.     
  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')    
  11.     
  12. Select * from Country    
  13. Select * from State    
  14.     
  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)    
  32.     
  33. --This is a query using Inner join which shows all the city for their respective countries.    
  34.     
  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>    
    2.     
    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"     
    23.     
    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     
    17.     
    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     
    24.     
    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     
    32.     
    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)) +     
    40.     
    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. }    
Ebook Download
View all
Learn
View all
Get Started

You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

Become a sharper developer and jumpstart your career.

Basic

$0

$

. 00

monthly

For Basic members:

  • Standard Profile
  • Access learning courses
  • Access free e-books
  • 1x point
Premium

$20

$

. 00

monthly

For Premium members:

  • Earn 2x reward points
  • Premium profile with Verified check
  • Premium Avatar with status update
  • Learning courses
  • Unlimited Certifications and badges on profile
  • Unlimited Book downloads
  • Access to Premium content
  • Online Training
  • Unlimited PDF conversions and downloads
  • Professional Resume Writing
  • Interview Preparations Guide
  • 25 messages per month
  • 1 "Kodzu" avatar, basic
Elite

$45

$

. 00

monthly

For Elite members:

  • Bundle E-books
  • Access to all Learn Series
  • Unlimited Apply Jobs