Home

SiteMap

Filter or Search Records in a GridView Based on a Date Range using Asp.Net FilterExpression Property

← PrevNext →

I have devoted an entire article on how to search a GridView control for records using Asp.Net FilterParameters and FilterExpression properties of SqlDataSource web server control in Asp.Net. The previous article and its example proved very useful to many newbie’s, however recently I received a query from a user, who asked how to search for records in a GridView control based on a Date range (from and to date).

I am not going to elaborate much about the properties and its usage here, since I have already explained in length about the control with an example. Please check the link below.

Therefore, I’ll straight away show you an example on how you should apply the date range option in your project. Usually, for a date range you will need two date pickers (or input boxes). Therefore, I’ll add two input boxes in this example. I’ll show you how to use the dates in the FilterExpression to filter records in the GridView. In-fact, the emphasis is more on the FilterExpression property of SqlDataSource control.

The FilterParameters will now have two control parameters defining the two input boxes with a special type. I have explained the type later in the article.

The Markup With GridView and SqlDateSource Properties
<div>
    <asp:GridView ID="GridView" 
        DataSourceID="SqlDataSource1" 
        CellPadding="5" 
        AllowPaging="True" PageSize="5"
        runat="server" 
        AutoGenerateColumns="true" 
        DataKeyNames="ContentID">
            
        <HeaderStyle BackColor="#989898" ForeColor="white" />
    </asp:GridView>

    <asp:SqlDataSource
        ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:YOURDBConnectionString %>"
                
        <!-- FilterExpression WITH MULTIPLE CLAUSE. -->
        SelectCommand="SELECT *FROM [Collage]"
           FilterExpression="AdmissionDate >= '#{0}#' AND AdmissionDate <= '#{1}#'">

        <FilterParameters>
            <asp:ControlParameter Name="AdmissionDate" ControlID="tbDateFrom" 
                Type="DateTime" PropertyName="Text" />
            <asp:ControlParameter Name=" AdmissionDate" ControlID="tbDateTo" 
                Type="DateTime" PropertyName="Text" />
        </FilterParameters>
    </asp:SqlDataSource>

    <br />

    Enter the Date Range: 
    <asp:TextBox ID="tbDateFrom" runat="server"></asp:TextBox>
    <asp:TextBox ID="tbDateTo" runat="server"></asp:TextBox>

    <input type="submit" id="btSubmit" runat="server" />
</div>

Let us assume, we have a table called Collage and it has a column called AdmissionDate. In the FilterExpression property, I have defined the date range, where I am checking for rows in the Collage table based the selected dates (>= and <=).

Note: Do not use the between keyword in the expression. The FilterExpression takes string value, it will not understand between, as it’s a property of SQL Server.

Later, I have defined the two input boxes as ControlParameters, inside the FilterParameters property. The Name property of each control parameter has the date column, with which we will filter. The type of each parameter is DateTime. The controllD point towards the input boxes.

To test your code, enter the date in the <input> boxes in mm/dd/yyyy format, since this is the default format for type DateTime in FilterParameters property.

That’s it.

Conclusion

We learned how to search records in a GridView control based on a Date range using FilterExpression and FilterParameter properties of SqlDataSource control in Asp.Net. Although, I am filtering the GridView based on a date range, the example is also useful if your search requires the use of multiple values. Look inside the FilterExpression string, I have used the AND keyword for multiple values.

← PreviousNext →