ASP.NET Populate The DropDownList Control With The Northwind Categories Table

The Categories table is a perfect example of how sometimes you have to populate the DropDownList control to data from the database. In this example we will populate the DropDownList control to the Categories table in the Northwind database.

 1. Drag the DropDownList control into a .aspx page.




Make sure you check "Enable AutoPostBack" 2.  In your C# code file you need the namespaces

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

2. Then get the Northwind connection string value from the Web.config file

string connectString = WebConfigurationManager.ConnectionStrings["NorthwindConnectionString"].
ConnectionString;

3. Type in the following code in the Page_Load method

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                using (SqlConnection conn = new SqlConnection(connectString))
                {
                    SqlCommand cmd = new SqlCommand("SELECT CategoryID, CategoryName 
FROM Categories ORDER BY CategoryName", conn);
                    conn.Open();
                    SqlDataReader reader = cmd.ExecuteReader();

                    DropDownList1.DataValueField = "CategoryID";
                    DropDownList1.DataTextField = "CategoryName";
                    DropDownList1.DataSource = reader;
                    DropDownList1.DataBind();

                    reader.Close();

                }
            }
            else
            {
                Response.Write("Selected item is " + DropDownList1.SelectedItem.Text + "<br>");
                Response.Write("Selected id is " + DropDownList1.SelectedItem.Value + "<br>");
            }
        }
    }

The line "if (!Page.IsPostBack)" is very important because if you don't have it you will never get the selected value. When you "Enable AutoPostBack" on the Designer view you tell ASP.NET to post back the page every time there is a change in the DropDownList1 control. So the block of code only executes once to populate the drop down list, after that only the "else" block is executed.