GridView Filter Expression With DropDownList ASP.NET

This example explains how to Filter GridView With DropDownList In ASP.NET Using FilterExpression And Filter Paramaters Or GridView Filtering with Sql Server And SqlDataSource.

I m using northwind database and customers table to show data and filter gridview with dropdownlist.

First of all open aspx page in design view and place 2 dropdownlist, 1 gridview and 3 SqlDatasource on the page.

Configure all 3 sqldatasources as according to code mentioned below. and use them for datasource to populate city dropdown, country dropdown and gridview.

You can also read ModalPopUp extender in Gridview to know how to configure SqlDataSource.

HTML Markup to Populate Dropdowns

<asp:DropDownList ID="ddlCity" runat="server" 
                  AppendDataBoundItems="True" 
                  AutoPostBack="True" 
                  DataSourceID="sqlDataSourceCity" 
                  DataTextField="City" 
                  DataValueField="City" Width="100px">
 <asp:ListItem Value="%">All</asp:ListItem>
 </asp:DropDownList>

<asp:SqlDataSource ID="sqlDataSourceCity" runat="server" 
ConnectionString="<%$ ConnectionStrings:northWindConnectionString %>" 
SelectCommand="SELECT DISTINCT City FROM Customers">
</asp:SqlDataSource>


<asp:DropDownList ID="ddlCountry" runat="server" 
                  AppendDataBoundItems="True" 
                  AutoPostBack="True" 
                  DataSourceID="sqlDataSourceCountry" 
                  DataTextField="Country" 
                  DataValueField="Country" Width="100px">
<asp:ListItem Value="%">All</asp:ListItem>
</asp:DropDownList>


<asp:SqlDataSource ID="sqlDataSourceCountry" runat="server" 
ConnectionString="<%$ ConnectionStrings:northWindConnectionString %>" 
SelectCommand="SELECT DISTINCT [Country] FROM [Customers]">
</asp:SqlDataSource>

Now Configure third sqldatasource to populate gridview based on filter expression as mentioned below

HTML markup of gridview and sqldatasource with filter expression

<asp:GridView ID="GridView1" runat="server" 
              AllowPaging="True" 
              DataSourceID="sqlDataSourceGridView" 
              AutoGenerateColumns="False"
              CssClass="GridViewStyle" 
              GridLines="None" Width="650px" 
              ShowHeader="false">
<Columns>
<asp:BoundField DataField="CustomerID" HeaderText="Customer ID"/>
<asp:BoundField DataField="CompanyName" HeaderText="Company"/>
<asp:BoundField DataField="ContactName" HeaderText="Name"/>
<asp:BoundField DataField="City" HeaderText="city"/>
<asp:BoundField DataField="Country" HeaderText="Country"/>
</Columns>
</asp:GridView>


<asp:SqlDataSource ID="sqlDataSourceGridView" 
                   runat="server" 
ConnectionString="<%$ ConnectionStrings:northWindConnectionString %>" 
SelectCommand="SELECT [CustomerID], [CompanyName], [ContactName], 
[City], [Country] FROM [Customers]" 
FilterExpression="[City] like '{0}%' and [Country] like '{1}%'">
<FilterParameters>
<asp:ControlParameter ControlID="ddlCity" Name="City" 
                      PropertyName="SelectedValue" 
                      Type="String" />
<asp:ControlParameter ControlID="ddlCountry" Name="Country" 
                      PropertyName="SelectedValue" 
                      Type="String" />
</FilterParameters>
</asp:SqlDataSource>

Build and run the application.

Download Sample Code

GridView Filter Expression With DropDownList ASP.NET

This example explains how to Filter GridView With DropDownList In ASP.NET Using FilterExpression And Filter Paramaters Or GridView Filtering with Sql Server And SqlDataSource.

I m using northwind database and customers table to show data and filter gridview with dropdownlist.

First of all open aspx page in design view and place 2 dropdownlist, 1 gridview and 3 SqlDatasource on the page.

Configure all 3 sqldatasources as according to code mentioned below. and use them for datasource to populate city dropdown, country dropdown and gridview.

You can also read ModalPopUp extender in Gridview to know how to configure SqlDataSource.

HTML Markup to Populate Dropdowns

<asp:DropDownList ID="ddlCity" runat="server" 
                  AppendDataBoundItems="True" 
                  AutoPostBack="True" 
                  DataSourceID="sqlDataSourceCity" 
                  DataTextField="City" 
                  DataValueField="City" Width="100px">
 <asp:ListItem Value="%">All</asp:ListItem>
 </asp:DropDownList>

<asp:SqlDataSource ID="sqlDataSourceCity" runat="server" 
ConnectionString="<%$ ConnectionStrings:northWindConnectionString %>" 
SelectCommand="SELECT DISTINCT City FROM Customers">
</asp:SqlDataSource>


<asp:DropDownList ID="ddlCountry" runat="server" 
                  AppendDataBoundItems="True" 
                  AutoPostBack="True" 
                  DataSourceID="sqlDataSourceCountry" 
                  DataTextField="Country" 
                  DataValueField="Country" Width="100px">
<asp:ListItem Value="%">All</asp:ListItem>
</asp:DropDownList>


<asp:SqlDataSource ID="sqlDataSourceCountry" runat="server" 
ConnectionString="<%$ ConnectionStrings:northWindConnectionString %>" 
SelectCommand="SELECT DISTINCT [Country] FROM [Customers]">
</asp:SqlDataSource>

Now Configure third sqldatasource to populate gridview based on filter expression as mentioned below

HTML markup of gridview and sqldatasource with filter expression

<asp:GridView ID="GridView1" runat="server" 
              AllowPaging="True" 
              DataSourceID="sqlDataSourceGridView" 
              AutoGenerateColumns="False"
              CssClass="GridViewStyle" 
              GridLines="None" Width="650px" 
              ShowHeader="false">
<Columns>
<asp:BoundField DataField="CustomerID" HeaderText="Customer ID"/>
<asp:BoundField DataField="CompanyName" HeaderText="Company"/>
<asp:BoundField DataField="ContactName" HeaderText="Name"/>
<asp:BoundField DataField="City" HeaderText="city"/>
<asp:BoundField DataField="Country" HeaderText="Country"/>
</Columns>
</asp:GridView>


<asp:SqlDataSource ID="sqlDataSourceGridView" 
                   runat="server" 
ConnectionString="<%$ ConnectionStrings:northWindConnectionString %>" 
SelectCommand="SELECT [CustomerID], [CompanyName], [ContactName], 
[City], [Country] FROM [Customers]" 
FilterExpression="[City] like '{0}%' and [Country] like '{1}%'">
<FilterParameters>
<asp:ControlParameter ControlID="ddlCity" Name="City" 
                      PropertyName="SelectedValue" 
                      Type="String" />
<asp:ControlParameter ControlID="ddlCountry" Name="Country" 
                      PropertyName="SelectedValue" 
                      Type="String" />
</FilterParameters>
</asp:SqlDataSource>

Build and run the application.

Download Sample Code

Display Images In GridView From DataBase Asp.Net

In this example i am explaining how to Show Display Images In GridView From DataBase In ASP.NET Using C# And VB.NET or showing images stored or saved in SQL Server database in gridview,For this i've already stored images in Database.

Table schema is shown below, ID column is primary key with Identity increment,datatype of Image column is Binary.

To know how to save or store Images in DataBase visit link below

Upload/Save Images in Database using FileUpload Control in ASP.NET C# VB.NET

For displaying images in gridview we need to create a Handler to read binary data from database.
Right click on solution explorer and Add new item, Pick Generic Handler and name it Handler.ashx.
Write this code in ProcessRequest method

C# code behind

<%@ WebHandler Language="C#" Class="Handler" %>

using System;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;

public class Handler : IHttpHandler {
    
public void ProcessRequest (HttpContext context) 
{
SqlConnection con = new SqlConnection();
con.ConnectionString = ConfigurationManager.ConnectionStrings
                      ["ConnectionString"].ConnectionString;

// Create SQL Command 
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "Select ImageName,Image from Images" + 
                  " where ID =@ID";
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = con;

SqlParameter ImageID = new SqlParameter
                    ("@ID", System.Data.SqlDbType.Int);
ImageID.Value = context.Request.QueryString["ID"];
cmd.Parameters.Add(ImageID);
con.Open();
SqlDataReader dReader = cmd.ExecuteReader();
dReader.Read();
context.Response.BinaryWrite((byte[])dReader["Image"]);
dReader.Close();
con.Close();
}

VB.NET Code

Public Class Handler
    Implements IHttpHandler
    
Public Sub ProcessRequest(ByVal context As HttpContext)
Dim con As New SqlConnection()
con.ConnectionString = ConfigurationManager.ConnectionStrings
                        ("ConnectionString").ConnectionString
        
        ' Create SQL Command 
        
        Dim cmd As New SqlCommand()
        cmd.CommandText = "Select ImageName,Image from Images" +
                          " where ID =@IID"
        cmd.CommandType = System.Data.CommandType.Text
        cmd.Connection = con
        
        Dim ImageID As New SqlParameter
                             ("@IID", System.Data.SqlDbType.Int)
        ImageID.Value = context.Request.QueryString("ID")
        cmd.Parameters.Add(ImageID)
        con.Open()
        Dim dReader As SqlDataReader = cmd.ExecuteReader()
        dReader.Read()
        context.Response.BinaryWrite
                    (DirectCast(dReader("Image"), Byte()))
        dReader.Close()
        con.Close()
    End Sub
End Class

Now drag a GridView control on the aspx page and add SQLDataSource to it.

For configuring GridVIew with SqlDataSource read
Insert Delete Update records in GridView using SqlDataSource ItemTemplate and EditItemTemplate

Now go to html markup of GridView and add a TemplateField and in ItemTemplate add a Image control to display Images.
Html Source of GridView should look like this

<asp:GridView ID="GridView1" runat="server" 
              AutoGenerateColumns="False" DataKeyNames="ID"
              DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="ID" HeaderText="ID" 
                InsertVisible="False" ReadOnly="True"
                               SortExpression="ID" />
<asp:BoundField DataField="ImageName" HeaderText="ImageName" 
                               SortExpression="ImageName" />
<asp:TemplateField HeaderText="Image">
<ItemTemplate>
<asp:Image ID="Image1" runat="server" 
           ImageUrl='<%# "Handler.ashx?ID=" + Eval("ID")%>'/>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [ID], [ImageName], [Image] 
              FROM [Images]"></asp:SqlDataSource>

For VB.NET there is slight change in html markup of page
change below mentioned line

ImageUrl='<%# "Handler.ashx?ID=" + Eval("ID")%>'/>

to

ImageUrl='<%# Eval("ID", "Handler.ashx?ID={0}")%>'/>

THis is how it will look

If you want to display Images in multiple columns or in more than one clumns of GridView then u need to make changes in the code as mentioned below.

1. Add a new column in database (i've named it Image2)
2. Add one mote Template Field in html source of gridview and change source as below

<asp:TemplateField HeaderText="Image">
<ItemTemplate>
<asp:Image ID="Image1" runat="server" 
ImageUrl='<%# Eval("ID", "Handler.ashx?ID={0}")+"&img=1"%>'/>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Image2">
<ItemTemplate>
<asp:Image ID="Image2" runat="server" 
ImageUrl='<%# Eval("ID", "Handler.ashx?ID={0}")+"&img=2"%>'/>
</ItemTemplate>
</asp:TemplateField>

And make these changes in code behind of handler.ashx

//Add this line of code in handler.ashx
int intImg = Convert.ToInt32(context.Request.QueryString["img"]);
//Now change earlier code to this one 
SqlDataReader dReader = cmd.ExecuteReader();
        dReader.Read();
        if (intImg == 1)
        {
            context.Response.BinaryWrite((byte[])dReader["Image"]);
        }
        else if (intImg == 2)
        {
            context.Response.BinaryWrite((byte[])dReader["Image2"]);
        }
        dReader.Close();
        con.Close();

Now it will look like this

hope this helps.

Download sample code attached

Sample code for displaying Images in Multiple columns




Other GridView articles:
ASP.NET Display Images in Gridview/DataList using objectdatasource

ASP.NET Insert Edit Update GridView with ObjectDataSource

Add AutoNumber Column in GridView or DataList

Creating Shopping cart in ASP.NET C# VB.NET Example using DataList GridView

Ajax autocomplete extender textbox in EditItemTemaplate of GridView

Running Total In Gridview Footer In ASP.NET C# VB.NET

In this example i am going to demonstrate how to Display Running Total In GridView Footer Row In ASP.NET using C# and VB.NET. This method works with paging enabled gridview as well.

For demo purpose gridview is populated using sqldatasource having table with columns ID ,Name,Amount

I m showing total of amount column is gridview footer. for this we need to sum the the column in RowDataBound Even of Gridiew

Html source of gridview is

<asp:GridView ID="GridView1" runat="server" 
              AutoGenerateColumns="False"
              DataKeyNames="ID" DataSourceID="SqlDataSource1" 
              OnRowDataBound="GridView1_RowDataBound" 
              ShowFooter="True" AllowPaging="True" PageSize="5" 
              BackColor="#ffffff" BorderColor="AliceBlue" 
              BorderStyle="None" BorderWidth="1px" 
              CellPadding="3" 
              CellSpacing="2" FooterStyle-BackColor="#da821e" 
              FooterStyle-ForeColor="#ffffff" 
              RowStyle-BackColor="#003366" 
              RowStyle-ForeColor="#ffffff" 
              AlternatingRowStyle-BackColor="#da821e">
<Columns>
     <asp:BoundField DataField="ID" HeaderText="ID" 
                     InsertVisible="False" ReadOnly="True"
                     SortExpression="ID" />
     <asp:BoundField DataField="Name" HeaderText="Name" 
                     InsertVisible="False" ReadOnly="True"
                     SortExpression="Name" FooterText="Total"/>
     <asp:TemplateField HeaderText="Amount">
     <ItemTemplate>
     <asp:Label ID="lblAmount" runat="server" 
                Text='<%# "$"+Eval("Amount").ToString()%>'>
     </asp:Label>
     </ItemTemplate>
     <FooterTemplate>
     <asp:Label ID="lblTotal" runat="server"></asp:Label>
     </FooterTemplate>
     </asp:TemplateField>
     </Columns>
     <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
     <HeaderStyle BackColor="#da821e" Font-Bold="True" 
                  ForeColor="White" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [ID], [Name], [Amount] FROM [Expenses]">
</asp:SqlDataSource>

Now we need to write code for summing the column in RowdataBound Even of GridView

C# code behind

public partial class _Default : System.Web.UI.Page 
{
    decimal grdTotal = 0;
    protected void Page_Load(object sender, EventArgs e)
    {

    }
protected void GridView1_RowDataBound
                   (object sender, GridViewRowEventArgs e)
{
 if (e.Row.RowType == DataControlRowType.DataRow)
 {
  decimal rowTotal = Convert.ToDecimal
              (DataBinder.Eval(e.Row.DataItem, "Amount"));
  grdTotal = grdTotal + rowTotal;
 }
 if (e.Row.RowType == DataControlRowType.Footer)
 {
  Label lbl = (Label)e.Row.FindControl("lblTotal");
  lbl.Text = grdTotal.ToString("c");
 }
}
}

VB.NET code behind

Public Partial Class _Default
    Inherits System.Web.UI.Page
    Private grdTotal As Decimal = 0
    Protected Sub Page_Load
    (ByVal sender As Object, ByVal e As EventArgs)
        
End Sub

Protected Sub GridView1_RowDataBound
(ByVal sender As Object, ByVal e As GridViewRowEventArgs)

If e.Row.RowType = DataControlRowType.DataRow Then
Dim rowTotal As Decimal = 
Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "Amount"))
grdTotal = grdTotal + rowTotal
End If

If e.Row.RowType = DataControlRowType.Footer Then
Dim lbl As Label = DirectCast(e.Row.FindControl
                           ("lblTotal"), Label)
lbl.Text = grdTotal.ToString("c")
End If
End Sub
End Class

Hope this helps

Other gridview articles:
Populating dropdown based on the selection of first drop down in DetailsView using FindControl and ItemTemplate

Pouplating Multiple DetailsView based on single GridView using DataKeyNames

Merging GridView Headers to have multiple Headers in GridView

Search Records In GridView And Highlight Results Using AJAX ASP.NET

Insert update Delete record in GridView using SqlDataSource ItemTemplate and EditItemTemplate

AutoNumber Column In GridView DataList ASP.NET

This example explains how to Add AutoNumber Column In GridView Or DataList In ASP.NET 2.0,3.5,4.0 Using C# And VB.NET. Several times we need to display Auto Number or serial number for Rows records in gridview or other similar controls in ASP.NET.

We can add AutoNumber column by using Container.DataItemIndex property in html markup of the Gridview.



Here's the sample html markup for the page

<title>Auto Number Cloumn in GridView </title>
</head>
<body>
<form id="form1" runat="server">
<asp:GridView ID="GridView1" runat="server" 
              AllowPaging="True" AutoGenerateColumns="False"
              DataSourceID="SqlDataSource1" PageSize="6" 
              AlternatingRowStyle-BackColor="#006699" 
              AlternatingRowStyle-ForeColor="#FFFFFF" >
    <Columns>
    <asp:TemplateField HeaderText="Serial Number">
    <ItemTemplate>
        <%# Container.DataItemIndex + 1 %>
    </ItemTemplate>
    </asp:TemplateField>
    <asp:BoundField DataField="Name" HeaderText="Name" 
                    SortExpression="Name" />
    <asp:BoundField DataField="Location" HeaderText="Location" 
                    SortExpression="Location" />
    </Columns>
    </asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [Name], [Location] FROM [Details]">
</asp:SqlDataSource>

Hope this helps

Search Records In GridView And Highlight Results Asp.Net Ajax

In this example i am Explaining how to Search Records In GridView And Highlight Results Using Ajax In Asp.Net 2.0,3.5,4.0 based on text entered in textbox.



Add following CSS style in head section of page.

HTML SOURCE

---

<asp:ScriptManager ID="ScriptManager1" runat="server"/>

Enter first name to search:

<asp:TextBox ID="txtSearch" runat="server" AutoPostBack="True"

             OnTextChanged="txtSearch_TextChanged"/>

<asp:UpdatePanel ID="UpdatePanel1" runat="server">

<ContentTemplate>

<asp:GridView ID="grdSearch" runat="server"

              AutoGenerateColumns="false">

<Columns>

<asp:TemplateField HeaderText="FirstName">

<ItemTemplate>

<asp:Label ID="lblFirstName" runat="server" 

           Text='<%# Highlight(Eval("FirstName").ToString()) %>'/>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="LastName">

<ItemTemplate>

<asp:Label ID="lblLastName" runat="server" Text='<%#(Eval("LastName")) %>'/>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Location">

<ItemTemplate>

<asp:Label ID="lblLocation" runat="server" Text='<%#(Eval("Location")) %>'/>

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

</ContentTemplate>

<Triggers>

<asp:AsyncPostBackTrigger ControlID="txtSearch" EventName="TextChanged" />

</Triggers>

</asp:UpdatePanel>

Write following code in code behind
C# CODE

---

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindGrid();
        }
    }
    private DataTable GetRecords()
    {
        SqlConnection conn = new SqlConnection(strConnection);
        conn.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "Select * from Employees";
        SqlDataAdapter dAdapter = new SqlDataAdapter();
        dAdapter.SelectCommand = cmd;
        DataSet objDs = new DataSet();
        dAdapter.Fill(objDs);
        return objDs.Tables[0];

    }
    private void BindGrid()
    {
        DataTable dt = GetRecords();
        if (dt.Rows.Count > 0)
        {
            grdSearch.DataSource = dt;
            grdSearch.DataBind();
        }
    }
    private void SearchText()
    {
        DataTable dt = GetRecords();
        DataView dv = new DataView(dt);
        string SearchExpression = null;
        if (!String.IsNullOrEmpty(txtSearch.Text))
        {
            SearchExpression = string.Format("{0} '%{1}%'",
            grdSearch.SortExpression, txtSearch.Text);

        }
        dv.RowFilter = "FirstName like" + SearchExpression;
        grdSearch.DataSource = dv;
        grdSearch.DataBind();

    }
    public string Highlight(string InputTxt)
    {
        string Search_Str = txtSearch.Text.ToString();
        // Setup the regular expression and add the Or operator.
        Regex RegExp = new Regex(Search_Str.Replace(" ", "|").Trim(),
        RegexOptions.IgnoreCase);

        // Highlight keywords by calling the 
        //delegate each time a keyword is found.
        return RegExp.Replace(InputTxt,
        new MatchEvaluator(ReplaceKeyWords));

        // Set the RegExp to null.
        RegExp = null;

    }

    public string ReplaceKeyWords(Match m)
    {

       // return "" + m.Value + "";

    }

    protected void txtSearch_TextChanged(object sender, EventArgs e)
    {
        SearchText();
    }

VB.NET

---

Protected Sub Page_Load(sender As Object, e As EventArgs)
 If Not IsPostBack Then
  BindGrid()
 End If
End Sub
Private Function GetRecords() As DataTable
 Dim conn As New SqlConnection(strConnection)
 conn.Open()
 Dim cmd As New SqlCommand()
 cmd.Connection = conn
 cmd.CommandType = CommandType.Text
 cmd.CommandText = "Select * from Employees"
 Dim dAdapter As New SqlDataAdapter()
 dAdapter.SelectCommand = cmd
 Dim objDs As New DataSet()
 dAdapter.Fill(objDs)
 Return objDs.Tables(0)

End Function
Private Sub BindGrid()
 Dim dt As DataTable = GetRecords()
 If dt.Rows.Count > 0 Then
  grdSearch.DataSource = dt
  grdSearch.DataBind()
 End If
End Sub
Private Sub SearchText()
 Dim dt As DataTable = GetRecords()
 Dim dv As New DataView(dt)
 Dim SearchExpression As String = Nothing
 If Not [String].IsNullOrEmpty(txtSearch.Text) Then

  SearchExpression = String.Format("{0} '%{1}%'", grdSearch.SortExpression, txtSearch.Text)
 End If
 dv.RowFilter = "FirstName like" & SearchExpression
 grdSearch.DataSource = dv
 grdSearch.DataBind()

End Sub
Public Function Highlight(InputTxt As String) As String
 Dim Search_Str As String = txtSearch.Text.ToString()
 ' Setup the regular expression and add the Or operator.
 Dim RegExp As New Regex(Search_Str.Replace(" ", "|").Trim(), RegexOptions.IgnoreCase)

 ' Highlight keywords by calling the 
 'delegate each time a keyword is found.
 Return RegExp.Replace(InputTxt, New MatchEvaluator(AddressOf ReplaceKeyWords))

 ' Set the RegExp to null.
 RegExp = Nothing

End Function

Public Function ReplaceKeyWords(m As Match) As String

 Return "" & Convert.ToString(m.Value) & ""

End Function

Protected Sub txtSearch_TextChanged(sender As Object, e As EventArgs)
 SearchText()
End Sub