Wednesday, May 6, 2009

How to bind or populate data into a Dropdown List based on first or another Dropdown List in Asp.net C#




Please visit my new Web Site WWW.Codedisplay.com



In many forums i found this problem that's why i decide to write a post on populating a Dropdown List based on another Dropdown List. Here i will try to show how we can bind sql server data with a Dropdown List as well as after selection populate other Dropdown List. To describe this situation here i will use a very common scenario like country & its regions. Lets we have a country table of ID, Name column & a region table containig ID, Name, CountryID. So its a one to many relationship in database. It will be more appropriate if we first populate Coutry Dropdown List. When user select a country then we will populate selected country regions only in the second Dropdown List. To do that we have to handle Country combo box
SelectedIndexChanged Event. Don't forget to set the AutoPostBack property of Country Dropdown List to True & to import using System.Data.SqlClient; namespace.

Look at sample sql server tables & values:


Add a new aspx page in your project & place below two Drodown List:

<asp:DropDownList ID="cboCountry" runat="server" AutoPostBack="True" OnSelectedIndexChanged="cboCountry_SelectedIndexChanged">
</asp:DropDownList>

<
asp:DropDownList ID="cboRegion" runat="server"></asp:DropDownList>


Now we need to populate Country Dropdown List first. So for ease i think Page_Load event is the best. Now put the below code under your Page_Load method:

if (!IsPostBack)
{
// Read sql server connection string from web.config file
string sConstr = ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString;
SqlConnection Conn = new SqlConnection(sConstr);
DataTable dt = new DataTable("tbl");

using
(Conn)
{
Conn.Open();
SqlCommand comm = new SqlCommand("SELECT ID,Name FROM Country", Conn);
SqlDataAdapter da = new SqlDataAdapter(comm);
da.Fill(dt);
}

cboCountry.DataSource = dt;
cboCountry.DataTextField =
"Name";
cboCountry.DataValueField =
"ID";
cboCountry.DataBind();
}


Now open the page in design view. Right click on Country Dropdown List to open proprties then from property click on event to select SelectedIndexChanged Event & put the below code segment under SelectedIndexChanged Event Handler:

protected void cboCountry_SelectedIndexChanged(object sender, EventArgs e)
{
string sConstr = ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString;
SqlConnection Conn = new SqlConnection(sConstr);
DataTable dt = new DataTable("tbl");

using
(Conn)
{
Conn.Open();
SqlCommand comm = new SqlCommand("SELECT ID,Name FROM Region WHERE CountryID=" + cboCountry.SelectedValue, Conn);
SqlDataAdapter da = new SqlDataAdapter(comm);
da.Fill(dt);
}

cboRegion.DataSource = dt;
cboRegion.DataTextField = "Name";
cboRegion.DataValueField =
"ID";
// Bind sql server data into the Dropdown List
cboRegion.DataBind();
}


Now run the page, hope our goal is achieved.

If you look into above code segment you can realize that there is a lot of scope to reduce codes. Ok now go one step ahead. To do that add a static class in your project & named it clsDbUtility. In this static class we write two static method. One is to retrieve data from our sql server database & another one is for populate Dropdown List. If we can do it then definitely it will ensure code reusability. Write code in your static class by the following way:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public
class clsDbUtility
{
public clsDbUtility(){}

public
static DataTable ExecuteQuery(string SQLstring)
{
string sConstr = ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString;
SqlConnection Conn = new SqlConnection(sConstr);
DataTable dt = new DataTable("tbl");

using
(Conn)
{
Conn.Open();
SqlCommand comm = new SqlCommand(SQLstring, Conn);
comm.CommandTimeout = 0;
SqlDataAdapter da = new SqlDataAdapter(comm);
da.Fill(dt);
}

return
dt;
}

public
static void FillCombo(DropDownList dropDownList, string dataValueField, string dataTextField, DataTable dataTbl, bool bHasBlank)
{
dropDownList.DataTextField = dataTextField;
dropDownList.DataValueField = dataValueField;
dropDownList.DataSource = dataTbl;
dropDownList.DataBind();

if
(bHasBlank)
dropDownList.Items.Insert(0,
new ListItem());
}
}


Ok now our class is ready. One question may arise how we can use it from our aspx page. Its simple. Have a look at the modifed code segment:

protected void cboCountry_SelectedIndexChanged(object sender, EventArgs e)
{
clsDbUtility.FillCombo(cboRegion, "ID", "Name", clsDbUtility.ExecuteQuery("SELECT ID,Name FROM Region WHERE CountryID=" + cboCountry.SelectedValue), true);
}


Modify your Page_Load Event according to the above example. Here i want to notify one interesting point is that if you look at the parameter bHasBlank then if you pass true the required field validator will also works for the first parameter reference.

Keep experimenting to reduce your code. It will be best if you use two class one is for DAL & another one is for UI. Hope you got my point. You can also use above technique for Gridview, Repeater, Detailsview control to bind or populate data from sql server database or from other datasources that support ADO.net.

12 comments:

Anonymous said...

I was trying to populate data from dropdownlist to gridview through simple binding. First i bound the dropdownlist with simple datasource (a public string array). Then tried to populate dropdownlist data into gridview. But it was not done. I want to know, wheather it is possible or should i use data table object.

Shawpnendu said...

Hi Anonymous,
Yes, you can use DropdownList datasource property as well as datatable to bind data to a GridView. Since your question is to bind data from DropdownList to GridView so the code sample like:

string[] urls =
{
"http://shawpnendu.blogspot.com",
"http://www.xxxxx.com",
"http://www.yyyyyy.com"
};

dd.DataSource = urls;
dd.DataBind();

gvBrand.DataSource = dd.DataSource;
gvBrand.DataBind();

Here dd is a DropdownList.

For more details on DataTable you can visit my Asp.net label, GridView label. Hope you will get a lot of articles on Datatable & GridView.

abhijit said...

hi swapnendu i have followed ur code...its working fine...now i want to populate another dropdown from the 2nd one...how can it be done ...pls help me its urgent!!!
AD

fatta said...

just what i have been looking for.
www.pixjm.com

Shawpnendu said...

Hi Abhijit,
Sorry for my late reply. Hope your problem has been resolved. If not then you can follow the below steps:

1. Add an eventhandler named SelectedIndexChanged for 2nd DropDownList.
2. In the handler method populate the 3rd DropDownList. But make sure that 1st combo selected index >-1 since 3rd combo depends on 2 nd combo & 2nd combo depends on 1st combo.
2. Set the postback property to true for 2nd combo.

Let me know if you can't understand.

Anonymous said...

Hai Mr. Shawpnendu Bikash,

I have work out ur coding, its very nice.

thanks and regards
yours friend

Anonymous said...

hi
i would like to know how to display the database table for selected category using dropdownlist

rizwan said...

hi this is rhizwan here.. i am a beginner i wanted to know that how we can use this two static methods in other class i.e about ddl and sql databinding. can u plz explain me..

Anonymous said...

Thanks for your code I was really help me alot

Anonymous said...

I Think Ajax Cascading Feature is another best option...

Anonymous said...

thnx for the code it is very helpfull.

Anonymous said...

It was very helpful.You made my day.Thanks cheers@!

Want to say something?
I WOULD BE DELIGHTED TO HEAR FROM YOU

Want To Search More?
Google Search on Internet
Subscribe RSS Subscribe RSS
Article Categories
  • Asp.net
  • Gridview
  • Javascript
  • AJAX
  • Sql server
  • XML
  • CSS
  • Free Web Site Templates
  • Free Desktop Wallpapers
  • TopOfBlogs
     
    Free ASP.NET articles,C#.NET,VB.NET tutorials and Examples,Ajax,SQL Server,Javascript,Jquery,XML,GridView Articles and code examples -- by Shawpnendu Bikash