In this article I will explain with an example, how to use the ASP.Net DropDownList control and populate it from database using SqlDataSource in ASP.Net.
Here I am making use of Microsoft’s Northwind Database. The download and install instructions are provided in the following article.
DropDownList control and its Properties
The DropDownList control makes use of the following properties when it is being populated using the SqlDataSource control.
DataSourceID – The ID of the SqlDataSource control is set as value to this property, this way the DropDownList control determines its source of data.
DataTextField – The name of the column to be set as Text in DropDownList. These values will be visible to end user.
DataValueField – The name of the column to be set as Value in DropDownList. These values will not be visible to end user.
AppendDataBoundItems – This property if set True then it will not clear the existing DropDownList values instead it will append the values from Database.
This property must be set to True when the DropDownList needs to display a default item such as Please select, etc.
The following HTML Markup consists of an ASP.Net DropDownList control and ASP.Net SqlDataSource control.
The SqlDataSource control is set with the following properties.
1. ConnectionString – Name of the Connection String setting in the Web.Config file.
2. SelectCommand – The Select statement to fetch the records from the Employees table of the Northwind database.
The ID of the SqlDataSource control is set as DataSourceID of the DropDownList control.
The default item is added to the DropDownList by placing a ListItem and setting the AppendDataBoundItems property to True.
<asp:DropDownList ID="ddlEmployees" runat="server" DataSourceID="SqlDataSource1"
DataTextField="EmployeeName" DataValueField="EmployeeID" AppendDataBoundItems="true">
<asp:ListItem Text="Please select" Value="" />
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:constr %>"
SelectCommand="SELECT (FirstName + ' ' + LastName) AS EmployeeName, EmployeeID FROM Employees">