Filter or Search records in a GridView based on a Date Range using FilterExpression property in Asp.Net

I have previously shared an article in my blog showing how to search a GridView control for records using FilterParameters and FilterExpression of SqlDataSource web server control in Asp.Net. Now here, using similar methods I am going to show you filter records in a GridView based on date range.

Note: I am assuming you know how to add a GridView control in your web page and bind it with SqlDataSource control. If you haven't used GridView and SqlDataSource control before, I would recommend checking this article. This will help you understand how to bind a GridView control to a database table using SqlDataSource.

Let's get on with the example.

GridView and SqlDateSource Properties

In the markup section, I have a GridView control and its data source is SqlDataSource1. So, the grid gets the data via SqlDataSource control.

For date range, I have added two textbox controls (to and from date).

To filter records I have used the FilterExpression property and <FilterParameters> attribute.

The ControlID attributes of the two ControlParameter (of <FilterParameters> attribute) has the textbox ids. The values from the two textboxes will be passed to FilterExpression property to filter out records (based on the dates).

Let us assume, I have a SQL Server table called "dbo.Collage" and it has a column named "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, which is a property in SQL Server.

<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 />

    <!-- two textbox controls for entering date range. -->
    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>

Remember: You must 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.

← PreviousNext →