DropDownList Part 1: Bind a DataTable To A DropDownList

One of the most commonly used server controls in ASP.NET is the DropDownList control.  In this blog I will show you how to bind a DataTable to a DropDownList control.

1. Create a .aspx page
2. Click on the "Toolbox" panel

3.  Drag the DropDownList control into a design surface

4. Create a connection string in the Web.Config file

  <connectionStrings>
    <add name="NorthwindConnectionString" connectionString="Data Source=(local);
Initial Catalog=Northwind;Integrated Security=True" 
providerName="System.Data.SqlClient"/>
  </connectionStrings>

5.  In the code behind page(.cs) page of the .aspx page, type in the following lines at the top to use the following libraries

using System.Web.Configuration;
using System.Data.SqlClient;
using System.Data;

6. Create a new method call BindCategoriesList() that returns void

protected void BindCategoriesList()
{
    DataTable dtCategories = new DataTable();
    string connectString = WebConfigurationManager.ConnectionStrings["NorthwindConnectionString"].
ConnectionString;

    using (SqlConnection conn = new SqlConnection(connectString))
    {
      SqlCommand cmd = new SqlCommand("SELECT CategoryID,CategoryName FROM Categories", conn);
      conn.Open();
      SqlDataAdapter adapter = new SqlDataAdapter(cmd);
      adapter.Fill(dtCategories);

      DropDownList1.DataSource = dtCategories;
      DropDownList1.DataTextField = dtCategories.Columns["CategoryName"].ToString();
      DropDownList1.DataValueField = dtCategories.Columns["CategoryID"].ToString();
      DropDownList1.DataBind();

   }
            

        }

The code above creates new DataTable call dtCategories, then it connects to the database using the "NorthwindConnectionString", and queries the CategoryID, and CategoryName in the Categories table in the Northwind database. The query results are then stored in the DataTable. The next step is to use the data in the dtCategories DataTable to populate the DropDownList1 control on our .aspx page. The following lines are used to bind the dtCategories to the DropDowndList1 control. Read the code comment to get the explanation

                
 //used to set the DropDownList1.DataSource to dtCategories
DropDownList1.DataSource = dtCategories;

//set the text field of the dropdownlist to the "CategoryName" value in the database
DropDownList1.DataTextField = dtCategories.Columns["CategoryName"].ToString();

//set the value field of the dropdownlist to the "CategoryID" value in the database
DropDownList1.DataValueField = dtCategories.Columns["CategoryID"].ToString();

7. Call the BindCategoriesList() method in the Page_Load method

        protected void Page_Load(object sender, EventArgs e)
        {
            BindCategoriesList();
            
        }

8. Run the application and you will see the DropDownList1 control populated with the Category Names in the Northwind database

9. If you look at the HTML source code you will see the following

<select name="DropDownList1" id="DropDownList1">
 <option value="1">Beverages</option>
 <option value="2">Condiments</option>
 <option value="3">Confections</option>
 <option value="4">Dairy Products</option>
 <option value="5">Grains/Cereals</option>
 <option value="6">Meat/Poultry</option>
 <option value="7">Produce</option>
 <option value="8">Seafood</option>
</select>

Related Blogs:

1. Bind a DataTable To A DropDownList
2. (DropDownList) Setting Default Value on First Page Load
3. Use The DropDownList Control To Populate GridView
4. Bind DropDownList To A List Of Objects