Here, in this article I’ll show you, with examples, how to use Asp.Net DropDownList control on a web page. In addition, we’ll see how to bind a DropDownList to an SQL Server database table using various methods, such as, an SqlDataSource and a Code behind procedure. Finally, I’ll conclude with an example of Cascading DropDownList.
What is a Cascading DropDownList?
A cascading dropdown list is a bunch of dropdown lists that are dependent on a parent dropdown list (or each other). The parent dropdown list will have master data or items. When a user select an item from the parent dropdown list, the second (or other) dropdown list is populated with items (extracted from a database table or other source) based on the parent item.
The Asp.Net DropDownList control has similarities to an HTML <select> element. I am sure you might have used the <select> element in your projects before. Here is an example, which I believe would help you understand the basic usage of a <select> element.
In the first example here, I’ll show to how to assign values to a DropDownList, manually.
DrowDownList Example
I have a list of color with Hexadecimal codes, which I want to add to a DropDownList. When a user selects a color from the list, it would display the color as background of a DIV element. To color the background, I have written a small script in JavaScript and jQuery as well. You have two methods now.
<div style="width:300px;">
<label style="margin-right:20px;">Pick a Color</label>
<asp:DropDownList ID="ddlColor" Font-Names="Verdana" onchange="SetColor(this);"
runat="server">
<asp:ListItem></asp:ListItem>
<asp:ListItem Value="#A4C639">Android Green</asp:ListItem>
<asp:ListItem Value="#9966CC">Amethyst</asp:ListItem>
<asp:ListItem Value="#4F86F7">Blue Berry</asp:ListItem>
<asp:ListItem Value="#FFA700">Chrome Yellow</asp:ListItem>
<asp:ListItem Value="#C23B22">Dark Pastel Red</asp:ListItem>
</asp:DropDownList>
<div id="color" style="width:100px; height:100px;margin:20px 0;" runat="server"> </div>
</div>I have added the ListItem element inside the DropDownList tag. Each ListItem element will hold the Hex codes as values and display the colors (in text).
Now let’s see our scripts. First example is in JavaScript, followed by an example in jQuery
Read DropDownList Values using JavaScript
<script>
function SetColor(ctrl) {
var div = document.getElementById("color");
div.style.backgroundColor = ctrl.value;
}
</script>Output
The onchange event of the DropDownList will call the function SetColor(). The function takes the control reference as a parameter. With the reference, I got the value to color the DIV’s background.
onchange="SetColor(this);"
Read DropDownList Values using jQuery
The jQuery process too is very simple, except that you will have to remove the onchange event that I have added to the DropDownList control. Add the jQuery CDN in the <head> section of your page and add the script.
<head>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.10.1/jquery.min.js"></script>
</head>
<script>
$('#ddlColor').change(function () {
$('#color').css("background-color", $(this).val());
});
</script>Bind the DropDownList Control to a Database Table using SqlDataSource
When you have a big and an unknown list of data to display, you might need to hook the DropDownList to a database table. The SqlDataSource control is a simple tool, which will help you to bind the DropDownList to a database table. Here is an article I have written explaining how to use SqlDataSource control bind and populate data to a control.
I have a database of books with various categories and prices stored in it. I am using a dummy “books” table for this example.
However, in the drop down list I wish to display distinct categories only. Therefore, first I’ll add the SqlDataSource to the web page and configure (add) a data source to it. I am assuming you have checked the above link where I have explained how to use SqlDataSource control
To get distinct categories, you need to set a condition using SQL Server “Distinct” function. You can do this using the SqlDataSource wizard. Open the wizard and go to the page Configure the Select Statement. You will have use the Next button at the bottom of the wizard window.
Find the option Specify a custom SQL statement in the wizard, select the option and click the Next button. In the “Define Custom Statements”, choose the Select tag and write your SQL query using Distinct.
SELECT Distinct [Category] FROM [Books]
Finally, you need to bind the SqlDataSource to the DropDownList. Again, go to the design mode and click the drop down list control. You will see a small arrow pointing to right. Click it. It will open a dialog box showing you option to Choose Data Source… for the control. Click it. See the image.
In the Choose Data Source window, select SqlDataSource from the first drop down list, followed by the data field and data value. Now, see the markup.
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:DNA_CLASSIFIEDConnectionString %>"
SelectCommand="SELECT '-- Select Category --' [Category]
UNION SELECT Distinct [Category] FROM [Books]"></asp:SqlDataSource>
<asp:DropDownList ID="ddlCategory" runat="server"
DataSourceID="SqlDataSource1"
DataTextField="Category"
DataValueField="Category"
Font-Names="Verdana">
</asp:DropDownList>Run the application and you will see the drop down list showing a distinct list of categories. It was simple, right.
Now, let’s take this example to the next level and see how we can bind and populate data to an Asp.Net DropDownList using Code behind procedures. You will often use this method in your web application, since it is more flexible and you can perform many more activities.
Cascading a DropDownList – Populate Data to a DropDownList based on Data from another DropDownList
The term Cascading a DropDownList refers to a process where a single of multiple DropDropList gets data based on the value picked from another DropDownList. In the previous example, I had a single drop down list control showing a list of “distinct” categories from the books table.
Now, I wish to display the name of books available in the database for a selected category. Therefore, I’ll add another DropDownList control along with the first one, on the web page and set the id of the this control as ddlBooks.
The first drop down list remains connected with the database table books. However, I’ll add a property and event to the control and set its values. The property is AutoPostBack and value I have set is True. Next, is an event called the OnTextChanged and has value ShowBooks. I’ll explain the property and the event in the final part of this article.
<label style="margin-right:20px;">Choose a Category</label>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:DNA_CLASSIFIEDConnectionString %>"
SelectCommand="SELECT '-- Select Category --' [Category]
UNION SELECT Distinct [Category] FROM [Books]">
</asp:SqlDataSource>
<%--THE FIRST DROPDOWN LIST.--%>
<asp:DropDownList ID="ddlCategory" runat="server"
DataSourceID="SqlDataSource1"
DataTextField="Category"
DataValueField="Category"
Font-Names="Verdana"
AutoPostBack="True"
OnTextChanged="ShowBooks">
</asp:DropDownList>
<%--THE SECOND DROPDOWN LIST. GETS ITS DATA BASED ON THE VALUE SELECTED
IN THE FIRST DROPDOWNLIST.--%>
<asp:DropDownList ID="ddlBooks" runat="server"></asp:DropDownList>
using System;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
public partial class SiteMaster : System.Web.UI.MasterPage
{
protected void ShowBooks(object sender, EventArgs args)
{
if (!string.IsNullOrEmpty(ddlCategory.SelectedValue))
{
DataTable dt = new DataTable();
// SET CONNECTION.
using (SqlConnection con = new SqlConnection
("Data Source=DNA;Persist Security Info=False;" +
"Initial Catalog=DNA_CLASSIFIED;User Id=sa;Password=;Connect Timeout=30;"))
{
string sQuery = "SELECT *FROM dbo.Books " + "WHERE Category = '" +
ddlCategory.SelectedValue + "'";
using (SqlCommand cmd = new SqlCommand(sQuery))
{
SqlDataAdapter sda = new SqlDataAdapter();
cmd.Connection = con;
con.Open();
sda.SelectCommand = cmd;
sda.Fill(dt);
ddlBooks.DataSource = dt;
ddlBooks.DataTextField = "BookName";
ddlBooks.DataValueField = "BookName";
ddlBooks.DataBind();
// OPTIONAL. SET THE FIRST VALUE.
ddlBooks.Items.Insert(0, new ListItem("--Select a Book--", "0"));
}
}
}
}
}Output
Option Explicit On
Imports System.Data
Imports System.Data.SqlClient
Partial Class Site
Inherits System.Web.UI.MasterPage
Protected Sub ShowBooks(ByVal sender As Object, ByVal args As EventArgs)
If ddlCategory.SelectedValue <> "" Then
Dim dt As DataTable = New DataTable
' SET CONNECTION.
Using con As SqlConnection = & _
New SqlConnection("Data Source=DNA;Persist Security Info=False;" & _
"Initial Catalog=DNA_CLASSIFIED;User Id=sa;Password=;Connect Timeout=30;")
Dim sQuery As String = "SELECT *FROM dbo.Books " & _
"WHERE Category = '" & ddlCategory.SelectedValue & "'"
Using cmd As SqlCommand = New SqlCommand(sQuery)
Dim sda As SqlDataAdapter = New SqlDataAdapter
cmd.Connection = con : con.Open()
sda.SelectCommand = cmd
sda.Fill(dt)
ddlBooks.DataSource = dt
ddlBooks.DataTextField = "BookName"
ddlBooks.DataValueField = "BookName"
ddlBooks.DataBind()
' OPTIONAL. SET THE FIRST VALUE.
ddlBooks.Items.Insert(0, New ListItem("--Select a Book--", "0"))
End Using
End Using
End If
End Sub
End ClassDropDownList AutoPostBack Property
A Boolean property, the AutoPostBack takes a value as True or False. If set as True, it will automatically do a postback or call a code behind function, whenever the user selects a value from the drop down list. The postback will trigger the OnTextChanged event, which will in turn call the function ShowBooks written in the code behind section of the application.
Some very interesting points I have mentioned in this article, explaining about the Asp.Net DropDownList control. We have seen how to populate data to the drop down list manually and use JavaScript and jQuery to fetch (or read) data from the list.
Next, we have seen how to bind a DropDownList control to a database table using SqlDataSource. This is one simple way to assign or populate data to a data driven Asp.Net control.
Finally, we have seen how we can use data from one DropDownList as source to fill data to another DropDownList control. This process in commonly known as “Cascading DropDownList” and we have done this by binding a DropDownList to a data source using code behind procedure.
Thanks for reading. ☺