In this article, we shall learn how to bind DropDownList or ListBox to the records coming in from the database.
This article has been written as an response to the Forums thread posted at
http://www.dotnetfunda.com/forums/thread5275-how-to-bind-dropdownlist-with-database-value.aspx.
In this thread, the author want to know how to populate the DropDownList from the records of the database. To demonstrate this, I have create a sample page and my sample page code looks like below
ASPX Page
<
div>
<asp:DropDownList Id="dropDownList1" runat="server" DataTextField="FullName" DataValueField="AutoId" /><asp:ListBox Id="listBox1" runat="server" DataTextField="FullName" DataValueField="AutoId" /> </div>
In the above code snippet, we have a DropDownList and a ListBox control. Both are different in terms of the their UI and ListBox can allows multiple item selection but DropDownList doesn't. Apart from that the way we work with DropDownList, we can work with ListBox control as well.
Here we have specified DataTextField as FullName (coming from the database table column) and DataValueField as AutoId (field name of the database table).
Code behind
Namespace to use
using
System.Data;using
System.Data.SqlClient;using
System.Configuration;
Code behind
string _connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;protected void Page_Load(object sender, EventArgs e){
if (!IsPostBack){
GetData();
}
}
private void GetData(){
DataTable table = new DataTable();// get the connectionusing (SqlConnection conn = new SqlConnection(_connStr)){
// write the sql statement to executestring sql = "SELECT AutoId, FirstName + ' ' + LastName as FullName, Age, Active FROM PersonalDetail ORDER By AutoId";// instantiate the command object to fireusing (SqlCommand cmd = new SqlCommand(sql, conn)){
// get the adapter object and attach the command object to itusing (SqlDataAdapter ad = new SqlDataAdapter(cmd)){
// fire Fill method to fetch the data and fill into DataTablead.Fill(table);
}
}
}
// specify the data source for the DropDownListdropDownList1.DataSource = table;
// bind the data nowdropDownList1.DataBind();
// specify the data source for the ListBoxlistBox1.DataSource = table;
// bind the data nowlistBox1.DataBind();
}
In the code behind page under the Page_Load event we are checking if the page is not post back (it means that page is being requested for the first time) then calling the GetData() method. It is mandatory to call this method inside the IsPostBack condition otherwise every time the page loads the user selection from the dropdown or ListBox will get overridden.
GetData() method
Pretty simple, if you are aware about ADO.NET, you should be able to understand it in blink of eyes. We are instantiating the DataTable, SqlConnection by passing the database connection string and then instantiating the SqlCommand object by passing the SQL statement (here I have merged the FirstName and LastName to FullName and that is why I had specified FullName as DataTextField into the DropDownList or ListBox control). Then we are instantiating the SqlDataAdapter by passing the SqlCommand object and calling the Fill method.
Fill method fetch the records from the database and hold into the DataTable object. Then we are specifying the DataSource of the DropDownList control to this DataTable object and then calling the DataBind() method that ultimately binds the data to the DropDownList. Same applies to the ListBox as well.
My records from the PersonalDetail table looks like below
and when the above records is bounded to the DropDownList and ListBox, it looks like below (The HTML source view of the DropDownList and ListBox controls)
<
select name="dropDownList1" id="dropDownList1"><
option value="3">Joe Elan</option><
option value="5">Michael Sumakar</option><
option value="10">Smith Modified Paul Modified</option><
option value="22">Jesn Anil</option><
option value="35">Jeff Janson</option><
option value="36">Sheo Narayan</option><
option value="37">Ammar Naqi</option></
select>
<
select size="4" name="listBox1" id="listBox1"><
option value="3">Joe Elan</option><
option value="5">Michael Sumakar</option><
option value="10">Smith Modified Paul Modified</option><
option value="22">Jesn Anil</option><
option value="35">Jeff Janson</option><
option value="36">Sheo Narayan</option><
option value="37">Ammar Naqi</option></
select>
You can see that the value of the item is the AutoId value coming from the database and the item text is the combination of FirstName and LastName.
Hope this article will be useful for the author of above thread and others who are looking for this solutions. In case you are looking for more Tips and Tricks of DropDownList or ListBox control,
click here.
Thanks for reading, keep reading and sharing your knowledge !