What you want to see on DotNetFunda.com ?
Go to DotNetFunda.com
Twitter TwitterLinkedIn
YouTubeGoogle
 Online : 5415 |  Welcome, Guest!   Register  Login
Home > Articles > ASP.NET > How to bind dropdownlist with database value

How to bind dropdownlist with database value

1 vote(s)
Rating: 5 out of 5
Article posted by SheoNarayan on 7/4/2011 | Views: 13452 | Category: ASP.NET | Level: Intermediate | Points: 250 red flag


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 !

If you like this article, subscribe to our RSS Feed. You can also subscribe via email to our Interview Questions, Codes and Forums section.

Page copy protected against web site content infringement by Copyscape
Found interesting? Add this to:



Please Sign In to vote for this post.

About Sheo Narayan

Experience:8 year(s)
Home page:http://www.snarayan.com
Member since:Tuesday, July 08, 2008
Level:HonoraryPlatinum
Status: [Microsoft_MVP] [Administrator]
Biography:Microsoft MVP, Author, Writer, Mentor & architecting applications since year 2001.

Connect me on Facebook | Twitter | LinkedIn | Blog

>> Write Response - Respond to this post and get points
Related Posts

In this article I provided some trick for not allowing user to aceess your site when javascript is disabled in browser.

Security is one of the key concerns in web applications. To maintain state we use many mechanisms and Query String is also one. If Query string holds any sensitive data it may lead to SQL Injection or accessing some other user’s data by just updating the Query String

In this article write the code to merge column & cells in same data in cells. I am described how to merge cells in Gridview in ASP.NET . In this article I am add OnRowDataBound Event of gridview and use RowSpan of cells.

This article describe basic overview of WebFarm and WebGarden for ASP.NET Site Deployment on IIS

Read ConnectionString Information from Application Configuration File.

More ...
About Us | Contact Us | The Team | Advertise | Software Development | Write for us | Testimonials | Privacy Policy | Terms of Use | Link Exchange | Members | Go Top
General Notice: If you find plagiarised (copied) contents on this page, please let us know the original source along with your correct email id (to communicate) for further action.
Copyright © DotNetFunda.Com. All Rights Reserved. Copying or mimicking the site design and layout is prohibited. Logos, company names used here if any are only for reference purposes and they may be respective owner's right or trademarks. | 5/25/2013 9:28:56 AM