Search
Winners

Win Prizes

Social Presence
Twitter Twitter LinkedIn YouTube Google

Like us on Facebook
Advertisements
Top Articles Author
Fri, 31-Oct-2014 Authors
All Time Authors
Sourav.Kayal
39750
SheoNarayan
38050
Niladri.biswas
33350

Latest members | More ...


(Statistics delayed by 5 minutes)
Ads
 Article

How to bind dropdownlist with database value

SheoNarayan
Posted by under ASP.NET category on for Intermediate level | Points: 250 | Views : 20142 red flag
If you found plagiarised (copied) or inappropriate content,
please let us know the original source along with your correct email id (to communicate) for further action.
Rating: 5 out of 5
1 vote(s)
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 connection

using (SqlConnection conn = new SqlConnection(_connStr))

{

// write the sql statement to execute

string sql = "SELECT AutoId, FirstName + ' ' + LastName as FullName, Age, Active FROM PersonalDetail ORDER By AutoId";

// instantiate the command object to fire

using (SqlCommand cmd = new SqlCommand(sql, conn))

{

// get the adapter object and attach the command object to it

using (SqlDataAdapter ad = new SqlDataAdapter(cmd))

{

// fire Fill method to fetch the data and fill into DataTable

ad.Fill(table);

}

}

}

// specify the data source for the DropDownList

dropDownList1.DataSource = table;

// bind the data now

dropDownList1.DataBind();

// specify the data source for the ListBox

listBox1.DataSource = table;

// bind the data now

listBox1.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 !
Page copy protected against web site content infringement by Copyscape
About the Author

SheoNarayan

Full Name: Sheo Narayan
Member Level: HonoraryPlatinum
Member Status: Microsoft_MVP,Administrator
Member Since: 7/8/2008 6:32:14 PM
Country: India
Regards, Sheo Narayan http://www.dotnetfunda.com
http://www.snarayan.com

Microsoft MVP, Author, Writer, Mentor & architecting applications since year 2001. Connect me on http://www.facebook.com/sheo.narayan | https://twitter.com/sheonarayan | http://www.linkedin.com/in/sheonarayan
Login to vote for this post.
Found interesting? Add this to:


Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)