Creating a great looking Gridview with filtering

31 May
May 31, 2008

Matt Berseth (http://www.mattberseth.com/) has some of the best ASP.NET 2.0 articles around, and his examples are always well documented.

Matt recently wrote some articles about how to a design good looking Gridview’s (Building a vs2008 Styled Grid and Gridview Themes based on Google’s Personalized Homepage) which was exactly what I was after.

I have been looking for quite some time for a good looking Gridview and Matt’s were great, although they didn’t include any filtering. Nearly all of my Gridviews have filtering enabled and so here is a quick tutorial to show you how to design a stylish Gridview based on Matt’s design with filtering enabled for some of the columns.

Creating a great looking Gridview with filtering

Step 1: Create the Gridview and Datasource

Create a simple Gridview and Datasouce.  In this example I am using a SQL Datasource, but I recommend using a ObjectDataSource for production environments.  Set the ConnectionString to the value in your web.config file and the ItemStyle-Width for each of your fields depending on the type of data and how much space you have.

<asp:GridView ID="Gridview1" runat="server" AutoGenerateColumns="False" AllowPaging="True"
AllowSorting="true" DataSourceID="dsGridview" Width="650px" PageSize="20"
CssClass="Gridview">
<Columns>
    <asp:BoundField DataField="id" HeaderText="id" SortExpression="id"
        ItemStyle-Width="50px" ItemStyle-HorizontalAlign="Center" />
    <asp:BoundField DataField="FirstName" HeaderText="Sort" SortExpression="FirstName"
        ItemStyle-Width="150px" />
    <asp:BoundField DataField="LastName" HeaderText="Sort" SortExpression="LastName"
        ItemStyle-Width="150px" />
    <asp:BoundField DataField="Department" HeaderText="Sort" SortExpression="Department"
        ItemStyle-Width="150px" />
    <asp:BoundField DataField="Location" HeaderText="Sort" SortExpression="Location"
        ItemStyle-Width="150px" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:EvonetConnectionString %>"
SelectCommand="SELECT * FROM [T_Employees]" />

Step 2: Create the table used for the Gridview headers

Now we create a simple table to hold the headings and filter drop down boxes.

<table style="width: 650px" border="0" cellpadding="0" cellspacing="1"
class="GridviewTable">
<tr>
    <td style="width: 50px;">
        ID
    </td>
    <td style="width: 150px;">
        First Name
    </td>
    <td style="width: 150px;">
        Last Name
    </td>
    <td style="width: 150px;">
        Department
    </td>
    <td style="width: 150px;">
        Location
    </td>
</tr>
<tr>
    <td style="width: 50px;">
    </td>
    <td style="width: 150px;">
    </td>
    <td style="width: 150px;">
    </td>
    <td style="width: 150px;">
        <asp:DropDownList ID="ddldepartment" DataSourceID="dsPopulateDepartment"
            AutoPostBack="true" DataValueField="department" runat="server" Width="130px"
            Font-Size="11px" AppendDataBoundItems="true">
            <asp:ListItem Text="All" Value="%"></asp:ListItem>
        </asp:DropDownList>
    </td>
    <td style="width: 150px;">
        <asp:DropDownList ID="ddlLocation" DataSourceID="dsPopulateLocation"
            AutoPostBack="true" DataValueField="location" runat="server" Width="130px"
            Font-Size="11px" AppendDataBoundItems="true">
            <asp:ListItem Text="All" Value="%"></asp:ListItem>
        </asp:DropDownList>
    </td>
</tr>
<tr>
    <td colspan="5">
        <asp:GridView ID="Gridview1" runat="server" AutoGenerateColumns="False"
            AllowPaging="True" AllowSorting="true" DataSourceID="dsGridview"
            Width="650px" PageSize="10" CssClass="Gridview">
            <Columns>
                <asp:BoundField DataField="id" HeaderText="Sort" SortExpression="id"
                    ItemStyle-Width="50px" ItemStyle-HorizontalAlign="Center" />
                <asp:BoundField DataField="FirstName" HeaderText="Sort"
                    SortExpression="FirstName" ItemStyle-Width="150px" />
                <asp:BoundField DataField="LastName" HeaderText="Sort"
                    SortExpression="LastName" ItemStyle-Width="150px" />
                <asp:BoundField DataField="Department" HeaderText="Sort"
                    SortExpression="Department" ItemStyle-Width="150px" />
                <asp:BoundField DataField="Location" HeaderText="Sort"
                    SortExpression="Location" ItemStyle-Width="150px" />
            </Columns>
        </asp:GridView>
    </td>
</tr>
</table>

For the last cell, set the td colspan value to the number of fields in your Gridview.  Move your Gridview into that last cell.

Step 3: Create the style sheet

The stylesheet that I use has the following items:

.GridviewDiv {font-size: 62.5%; font-family: 'Lucida Grande',
    'Lucida Sans Unicode', Verdana, Arial, Helevetica, sans-serif; color: #303933;}
Table.Gridview{border:solid 1px #df5015;}
.GridviewTable{border:none}
.GridviewTable td{margin-top:0;padding: 0; vertical-align:middle }
.GridviewTable tr{color: White; background-color: #df5015; height: 30px; text-align:center}
.Gridview th{color:#FFFFFF;border-right-color:#abb079;border-bottom-color:#abb079;
    padding:0.5em 0.5em 0.5em 0.5em;text-align:center}
.Gridview td{border-bottom-color:#f0f2da;border-right-color:#f0f2da;
    padding:0.5em 0.5em 0.5em 0.5em;}
.Gridview tr{color: Black; background-color: White; text-align:left}
:link,:visited { color: #DF4F13; text-decoration:none }

You should be able to just copy this into your css file without it affecting your exiting style sheets, although be careful if you have already set :link and :visited in your site.

Step 4: Add the filtering drop down boxes and data sources

In the table created in Step 2, add a dropdownlist to each of the cells in the second row that contain the field you want to filter.  Make sure eac dropdownlist is smaller than the cell it is going into, otherwise your table borders will not be aligned.  Set up a datasource which gets the each possible value of that field within your table.  I do this by running a DISTINCT for all values in the table I am filtering:

<asp:DropDownList ID="ddldepartment" DataSourceID="dsPopulateDepartment"
AutoPostBack="true" DataValueField="department" runat="server" Width="130px" Font-Size="11px"
AppendDataBoundItems="true">
    <asp:ListItem Text="All" Value="%"></asp:ListItem>
</asp:DropDownList>
<asp:SqlDataSource ID="dsPopulateDepartment" runat="server"
ConnectionString="<%$ ConnectionStrings:EvonetConnectionString %>" SelectCommand="SELECT
DISTINCT Department from [T_Employees]"></asp:SqlDataSource>

Create as many dropdownlists as fields that you would like to filter by.

A few things to note:

  • Set the AppendDataBoundItems=True property for your dropdownlist as it will be filled at runtime.
  • Set the AutoPostBack=True property so that the Gridview is refreshed when the selection changes.
  • Make sure your ListItem for ‘All’ has ‘%’ as the value.  Your filter expression will be SELECT * FROM [TABLE NAME] Where [FieldName] like ‘{0}%’ where {0} is the value in your dropdownlist.  If your dropdownlist is set to all then the query string will be SELECT * FROM [TABLE NAME] Where [FieldName] like ‘%%’ which, in SQL returns all values.

Step 5: Add filtering to your Gridview’s Datasource

Add a FilterExpress so your Gridview’s Datasource such as

[Field1] like ‘{0}%’ and [Field2] like ‘{1}%’ and [Field3] like ‘{2}%’ and [Field4] like ‘{3}%’ and … etc

Your fields then need to be added to the FilterParameters section in the same order as your filter expression.  The FilterParameters section references the SelectedValue of your dropdownlists.

<asp:SqlDataSource ID="dsGridview" runat="server"
    ConnectionString="<%$ ConnectionStrings:EvonetConnectionString %>"
    SelectCommand="SELECT * FROM [T_Employees]" FilterExpression="Department like '{0}%'
    and Location like '{1}%'">
    <FilterParameters>
        <asp:ControlParameter Name="Department" ControlID="ddldepartment"
            PropertyName="SelectedValue" />
        <asp:ControlParameter Name="Location" ControlID="ddllocation"
            PropertyName="SelectedValue" />
    </FilterParameters>
</asp:SqlDataSource>

That’s it!

Tags:
  • Annette Wright

    This was great and works well except for numeric and integers. Do you have suggestions?

  • Vanitha

    For numeric and integers don’t use the quotes and the word like they should be coded as below if the department is integer
    Department = {0}

    Also in the dropdown for department you should not have

    • Jcarr

      I have run into two problems, one involving a integer.  I have a table that has a list of reports, with the following fields:  Year, OperatorName, UnitName and Filename.  Year contains the 4 digit year as an integer, OperatorName contains the (yes, you guessed it) the Operator’s name as a varchar, the UnitName contains the Unit’s Name as a varchar and finally the FileName field contains the name of the .pdf file that will be getting point to by a .

      1.) I wanted to filter by Year, OperatorName and UnitName.  I was able to use your suggestion of changing Year={0}% to Year={0} which works fine as long as you don’t want to get results for all years.   Is there a way to return all rows from an interger field like you do from string fields?

      2.)  This problem is more complicated, but I’ll do my best to explain it.  It is hard to explain the Operator/Unit relationship, but it would be like a State/City relationship.  1 Operator may have 1 or more Units.  So I tried to build the Unit’s dropdownlist with a WHERE clause that only populates the Units, based on the selected Operator.  Works great until I include the and enable the AppendDataBoundItems=”true”.  When a selection is made in the Operator’s dropdownlist the first time, the Unit’s dropdownlist populates with the right results.  However, the second and subsequent Operator selections just append the new results in the Unit dropdown, leaving the previous results in the list.  The list only returns to it’s default after leaving the page and returning.

      I know that answer some or most of this is probably beyond the scope of this blog, but I was hoping for some general direction.  I have only recently started designing with asp.net using VS and VB, having spent years with Classic ASP.  I actually have a working page done in classic asp doing what I’m now trying to get .NET to do.

      Thanks for any suggestions.

  • Mahesh

    Insted of sql text “Select * from T_Employees”, is it possible to use a Stored Procedure with FilterParameters?

  • http://danish-islam.blogspot.com Danish Islam

    Thanks it is of great help.

  • Arun Bera

    firstly thanks a lot, it is very good unexpected element

  • jennifer72

    Any Idea to do the same with Linq DataSources?

  • timw

    Table header and Gridview don’t line up in Firefox or Chrome.

  • Anton Kruger

    This is a great solution except for one thing. You will have less problems with column alignment if you scrap the wrapping table. Just convert the fields that you want to filter to TemplateFields. Then add a header temple for the dropdowns. 

                                                    <asp:Label ID="Label2" runat="server" Text='’>                                                                    Called Service                                                                   

    • Jason Pichie

      Anton, this is the biggest issue I have been having with this (column alignment with table and grid).
      How do I access the values in the dropdown in the gridview header? I am getting %dropdown% does not exist in current context.
      Thanks in advance!

      • http://www.greentdi.com surfponto

        You have to use the “findcontrol” method
        Something like this C#
         if (e.Item.ItemType == ListItemType.Header)  {  
        TextBox myTextBox = (TextBox)e.Item.FindControl(“myControl”);  
        myTextBox.Text = “GridView Header TextBox”; 
        }

  • CyrilMan

    Thanks you for this work.
    i will try to explain myself in english.
    I have some problem with a filter.
    1. When i try to use in my filter expression ” data like ‘{0}%’ “  there is nothing in my gridview.

    2. When i use ” data = {0}  ” my filter work but the problem appear when i add the second parameter ” data = {0}  and country = {1} “.
    But when i use this form  FilterExpression=”data = ‘{0}’ and country= ‘{1}’ ” the second filter work and use the selected value of the first filter, but the first don’t work.

    I don’t understand where is the problem.

    Thank you for your replies.

    • CyrilMan

      Hello.

      I’ve find another way to do filter. in the default value of my dropdownlist i put “%%” like this :

               
       

                

       

      in the datasource of the gridview, my selectcommand look like this :

      SelectCommand=”SELECT [data], [country] from database where ([data] like @data) and ([country] like @country )

      In the selectparameters :

                
                 

  • http://www.itgeared.com/ JM

    Nice!  Thanks for the article.  I needed a filtering solution.

  • http://www.facebook.com/people/Meda-Rajesh-Chowdary/100001802564229 Meda Rajesh Chowdary

    hi..i m rajesh frm indosoft int company…..the code really hepled me alot…thanks fr the share…:)

  • http://profile.yahoo.com/47243HIF3XP6FBHGYCNGOWLGLI Sean D

    After a view tweeks I was able to get it to work the way I needed. Been looking for something like this for a while. Thanks so much for the post!

  • Alex Dove

    How would you create a Textbox filter-as-you-type solution?  For instance, if you wanted to filter on Last Name by typing in text into the textbox and have the grid automatically filter the data. How would this be accomplished? I really like your original solution, but having the ability to filter by characters entered into a textbox is important to me. I am also using a SharePoint SPDataSource instead of an SQLDataSource. How would that impact the code?
    Thanks in advance.

  • Aman Ahmed

    If you get following error “Could not find control ‘x’ in ControlParameter ‘y’ ” then do this.

    The ControlID needs to be prefixed with the ID of the ContentPlaceHolder which contains the DropDownList:”

© Copyright - Evonet