In this article I will explain how we can filter the items in ASP.Net DataList control based on the value selected in ASP.Net DropDownList control.
Database and Connection string
For this sample to work you will need to download the Microsoft Northwind database using the following link.
Below is the connection string from the Web.Config file.
<connectionStrings>
<add name="conString" connectionString="Data Source=.\SQLExpress;
database=Northwind;Integrated Security=true"/>
</connectionStrings>
HTML Markup
Below is the HTML Markup where I have a DropDownList containing some countries from the Customers table of the Northwind Database and the DataList control which will display the Customer Details.
Country: <asp:DropDownList ID="ddlCountry" AutoPostBack="true" runat="server">
<asp:ListItem Text="All" Value="" />
<asp:ListItem Text="Argentina" Value="Argentina" />
<asp:ListItem Text="Belgium" Value="Belgium" />
<asp:ListItem Text="Brazil" Value="Brazil" />
<asp:ListItem Text="Canada" Value="Canada" />
</asp:DropDownList><br />
<asp:DataList ID="dlCustomers" runat="server" RepeatLayout="Table" RepeatColumns="3"
CellPadding="2" CellSpacing="2">
<ItemTemplate>
<table cellpadding="2" cellspacing="0" border="1" style="width: 200px; height: 100px;
border: dashed 2px #04AFEF; background-color: #B0E2F5">
<tr>
<td>
<b><u><span class="name">
<%# Eval("ContactName") %></span></u></b>
</td>
</tr>
<tr>
<td>
<b>City: </b><span class="city">
<%# Eval("City") %></span><br />
<b>Postal Code: </b><span class="postal">
<%# Eval("PostalCode") %></span><br />
<b>Country: </b><span class="country">
<%# Eval("Country")%></span><br />
<b>Phone: </b><span class="phone">
<%# Eval("Phone")%></span><br />
<b>Fax: </b><span class="fax">
<%# Eval("Fax")%></span><br />
</td>
</tr>
</table>
</ItemTemplate>
</asp:DataList>
Namespaces
You will have to import the following namespaces.
C#
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
VB.Net
Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
Binding and Filtering the Items of the ASP.Net DataList Control
In the HTML Markup you will notice that the value of the first (default) item in the DropDownList is blank string. And in my query I have added a condition that if the Selected Value of the DropDownList is Blank it should return all records and if the Selected Value of the DropDownList is a valid Country then it should filter the records based on country.
C#
protected void Page_Load(object sender, EventArgs e)
{
string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
string query = "SELECT top 10 * FROM Customers where Country=@Country or @Country=''";
SqlCommand cmd = new SqlCommand(query);
cmd.Parameters.AddWithValue("@Country", ddlCountry.SelectedItem.Value);
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataSet ds = new DataSet())
{
sda.Fill(ds);
dlCustomers.DataSource = ds;
dlCustomers.DataBind();
}
}
}
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Dim query As String = "SELECT top 10 * FROM Customers where Country=@Country or @Country=''"
Dim cmd As SqlCommand = New SqlCommand(query)
cmd.Parameters.AddWithValue("@Country", ddlCountry.SelectedItem.Value)
Dim con As SqlConnection = New SqlConnection(conString)
Dim sda As SqlDataAdapter = New SqlDataAdapter
cmd.Connection = con
sda.SelectCommand = cmd
Dim ds As DataSet = New DataSet
sda.Fill(ds)
dlCustomers.DataSource = ds
dlCustomers.DataBind()
End Sub
Demo
Downloads
