How to fill DropDownList from Sql server database in asp.net

How to fill DropDownList from Sql server database in asp.net

Introduction: In previous article i explained how to Fill CheckBoxList based on DropDownList selection in asp.net(C#, VB) and How to open Pop up window on Drop down selection in Asp.net  and How to fill dropdownlist with days, month and year in asp.net(C#, VB) .One of the common requirement while working on asp.net application is to dynamically Fill/Bind DropDownList from Sql server table. Suppose we want to fillDropDownList on page load from department table then here is the way:

• Create a table in the Sql server database as shown in Figure and name it DEPARTMENT_TABLE

• Place a DropDownList control on your design page (.aspx) of asp.net website.

<asp:DropDownList ID="ddlDept" runat="server">

 </asp:DropDownList>

• Create function in the code behind file to Fill/Bind DropDownList  and call it on  Page load event:

C#.NET Code

    protected void Page_Load(object sender, EventArgs e)

    {

if (!Page.IsPostBack)

        {

            FillDeptDropdownList();           

        }   

}

protected void FillDeptDropdownList()

    {

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDbCon"].ConnectionString);

        SqlCommand cmd = new SqlCommand("Select * from DEPARTMENT_TABLE", con);

        SqlDataAdapter adp=new SqlDataAdapter(cmd);

        DataTable dt=new DataTable();

        adp.Fill(dt);

        ddlDept.DataSource=dt;

        ddlDept.DataTextField="DEPT_NAME";

        ddlDept.DataValueField="DEPT_ID";

        ddlDept.DataBind();

        ddlDept.Items.Insert(0,"Select Department");

       //OR    ddlDept.Items.Insert(0, new ListItem("Select Department", "-1"));       

    }