How to Bind or Populate Data into a DropDownList Based on first or another DropDownList in Asp.Net C# VB.Net

In many forums I found this problem that’s why i decide to write a post on populating a DropDownList based on another DropDownList. Here i will try to show how we can bind SQL Server data with a DropDownList as well as after selection populate other DropDownList. To describe this situation here i will use a very common scenario like country and its regions. Lets we have a country table of ID, Name column and 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 DropDownList. When user select a country then we will populate selected country regions only in the second DropDownList. To do that we have to handle Country combo box SelectedIndexChanged Event. Don’t forget to set the AutoPostBack property of Country DropDownList to True.

To do the Example First Create Below Tables into DataBase:

CREATE TABLE [dbo].[Country](
	[ID] [int] NOT NULL,
	[Name] [varchar](max) NULL,
	[Sort] [int] NULL,
 CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Region](
	[ID] [int] NOT NULL,
	[Name] [varchar](max) NULL,
	[CountryID] [int] NULL,
	[Sort] [int] NULL,
 CONSTRAINT [PK_Region] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Now Insert some Data:

INSERT INTO Country VALUES(1,'United States',1)
INSERT INTO Country VALUES(2,'Australia',2)
INSERT INTO Country VALUES(3,'Bangladesh',3)

INSERT INTO Region VALUES(1,'Newyork',1,1)
INSERT INTO Region VALUES(2,'Texas',1,2)
INSERT INTO Region VALUES(3,'Sydney',2,1)
INSERT INTO Region VALUES(4,'Parth',2,2)
INSERT INTO Region VALUES(5,'Dhaka',3,1)
INSERT INTO Region VALUES(6,'Chittagonj',3,2)

Now modify web.config file to connect to DataBase:

<configuration>
  <connectionStrings>
    <add name="DBConnection" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=TESTDB;Trusted_Connection=yes;" providerName="System.Data.SqlClient"/>
    <!--<add name="BONConnection" connectionString="Data Source=XXX.com;Initial Catalog=DBNAME;User Id=UserName;Password=YourPassword;" providerName="System.Data.SqlClient" />-->
  </connectionStrings>
...................
...................

Note: If you use windows authentication then use the first key. Otherwise use second key line. Update server name, Database name, User name, Password as per your settings.

Now add two DropDownList or ComboBoxes in Default.aspx Page:

<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <b>Country:</b><br />
<asp:DropDownList ID="cboCountry" runat="server" AutoPostBack="True"  onselectedindexchanged="cboCountry_SelectedIndexChanged"></asp:DropDownList>
<br /><br />
<b>Region:</b><br />
<asp:DropDownList ID="cboRegion" runat="server"></asp:DropDownList>
</asp:Content>

Now under Page_Load event first Bind Country DropDownList:
C# Code:

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            // Read sql server connection string from web.config file
            string sConstr = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
            SqlConnection Conn = new SqlConnection(sConstr);
            DataTable dt = new DataTable("tbl");

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

            cboCountry.DataSource = dt;
            cboCountry.DataTextField = "Name";
            cboCountry.DataValueField = "ID";
            cboCountry.DataBind();
            cboCountry.Items.Insert(0,new ListItem("--Select--"));
        }
    }

Note: Don’t forget to add “System.Data”, “System.Data.SqlClient”, “System.Configuration” namespaces.

VB.Net Code:

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            ' Read sql server connection string from web.config file
            Dim sConstr As String = ConfigurationManager.ConnectionStrings("DBConnection").ConnectionString
            Dim Conn As New SqlConnection(sConstr)
            Dim dt As New DataTable("tbl")

            Using Conn
                Conn.Open()
                Dim comm As New SqlCommand("SELECT ID,Name FROM Country ORDER BY Sort", Conn)
                Dim da As New SqlDataAdapter(comm)
                da.Fill(dt)
            End Using

            cboCountry.DataSource = dt
            cboCountry.DataTextField = "Name"
            cboCountry.DataValueField = "ID"
            cboCountry.DataBind()
            cboCountry.Items.Insert(0, New ListItem("--Select--"))
        End If

    End Sub

Note: Don’t forget to add “System.Data”, “System.Data.SqlClient”, “System.Configuration” namespaces.

Now open the Default page in design view. Right click on Country DropDownList to open properties then from property click on event to select SelectedIndexChanged Event and put the below code segment under SelectedIndexChanged Event Handler:

C# Code:

    protected void cboCountry_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (cboCountry.SelectedIndex > 0)
        {
            string sConstr = ConfigurationManager.ConnectionStrings["DBConnection"].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 + " ORDER BY Sort", 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();
        }
        else
            cboRegion.Items.Clear();
    }

VB.Net Code:

    Protected Sub cboCountry_SelectedIndexChanged(sender As Object, e As System.EventArgs) Handles cboCountry.SelectedIndexChanged
        If cboCountry.SelectedIndex > 0 Then
            Dim sConstr As String = ConfigurationManager.ConnectionStrings("DBConnection").ConnectionString
            Dim Conn As New SqlConnection(sConstr)
            Dim dt As New DataTable("tbl")

            Using Conn
                Conn.Open()
                Dim comm As New SqlCommand("SELECT ID,Name FROM Region WHERE CountryID=" + cboCountry.SelectedValue & " ORDER BY Sort", Conn)
                Dim da As New SqlDataAdapter(comm)
                da.Fill(dt)
            End Using

            cboRegion.DataSource = dt
            cboRegion.DataTextField = "Name"
            cboRegion.DataValueField = "ID"
            ' Bind sql server data into the Dropdown List
            cboRegion.DataBind()
        Else
            cboRegion.Items.Clear()
        End If

    End Sub

Now run the page, hope we have achieved our goal:
Bind Dropdown List based on another Dropdown List in Asp.net Csharp VB.Net

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 clsUIUtility. In this static class we will write two static method. One is to retrieve data from our SQL server database & another one is for populating or binding data into a DropDownList. If we can do it then definitely it will ensure code re-usability. Write code in your static class in the following way:

C# Code:

public class clsUIUtility
{
	public clsUIUtility()
	{
	}

    public static DataTable ExecuteQuery(string SQLstring)
    {
        string sConstr = ConfigurationManager.ConnectionStrings["DBConnection"].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());
    }
}

Note: Don’t forget to add “System.Data”, “System.Data.SqlClient”, “System.Configuration”, “System.Web.UI.WebControls” namespaces.

VB.Net Code:

Public Class clsUIUtility
    Public Shared Function ExecuteQuery(SQLstring As String) As DataTable
        Dim sConstr As String = ConfigurationManager.ConnectionStrings("DBConnection").ConnectionString
        Dim Conn As New SqlConnection(sConstr)
        Dim dt As New DataTable("tbl")

        Using Conn
            Conn.Open()
            Dim comm As New SqlCommand(SQLstring, Conn)
            comm.CommandTimeout = 0
            Dim da As New SqlDataAdapter(comm)
            da.Fill(dt)
        End Using

        Return dt
    End Function

    Public Shared Sub FillCombo(dropDownList As DropDownList, dataValueField As String, dataTextField As String, dataTbl As DataTable, bHasBlank As Boolean)
        dropDownList.DataTextField = dataTextField
        dropDownList.DataValueField = dataValueField
        dropDownList.DataSource = dataTbl
        dropDownList.DataBind()

        If bHasBlank Then
            dropDownList.Items.Insert(0, New ListItem())
        End If
    End Sub

End Class

Note: Don’t forget to add “System.Data”, “System.Data.SqlClient”, “System.Configuration”, “System.Web.UI.WebControls” namespaces.

Now Invoke clsUIUtility from Page_Load Event Like Below:
C# Code:

            clsUIUtility.FillCombo(cboCountry, "ID", "Name", clsUIUtility.ExecuteQuery("SELECT ID,Name FROM Country ORDER BY Sort"), false);
            cboCountry.Items.Insert(0, new ListItem("--Select--"));

VB.Net Code:

            clsUIUtility.FillCombo(cboCountry, "ID", "Name", clsUIUtility.ExecuteQuery("SELECT ID,Name FROM Country ORDER BY Sort"), False)
            cboCountry.Items.Insert(0, New ListItem("--Select--"))

Now Invoke clsUIUtility from SelectedIndexChanged Event Like Below:
C# Code:

clsUIUtility.FillCombo(cboRegion, "ID", "Name", clsUIUtility.ExecuteQuery("SELECT ID,Name FROM Region WHERE CountryID=" + cboCountry.SelectedValue+" ORDER BY Sort"), false);

VB.Net Code:

clsUIUtility.FillCombo(cboRegion, "ID", "Name", clsUIUtility.ExecuteQuery("SELECT ID,Name FROM Region WHERE CountryID=" + cboCountry.SelectedValue + " ORDER BY Sort"), False)

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.

Download Code Example C#        Download Code Example VB.Net

Leave a Reply

Your email address will not be published. Required fields are marked *

AlphaOmega Captcha Classica  –  Enter Security Code