Filter GridView without using SQLDataSource

Posted on 2009-02-21
Last Modified: 2013-11-07

I have a GridView with all BoundFields. Above the BoundFields outside of the gridview is a row with either a TextBox or DropDownList for each column (see first code snippet).  We do not use an SQLDataSource on the .aspx page to get data.  

So my questions is, how do I filter the gridview by the textbox or dropdownlist without the SQLDataSource?

1. Where do I define the filters and how?

2. What event triggers the filter on these controls and how do I filter then in the event?

Thanks in advance.  

        <tr style="background-color: #cccccc">

            <td style="width: 105px; text-align: center; font-weight: bold">TripFile Name</td>

            <td style="width: 105px; text-align: center; font-weight: bold">Supplier</td>

            <td style="width: 65px; text-align: center; font-weight: bold">Destination</td>

            <td style="width: 75px; text-align: center; font-weight: bold">Departure Date</td>

            <td style="width: 75px; text-align: center; font-weight: bold">Travel Type</td>

            <td style="width: 70px; text-align: center; font-weight: bold">Stage</td>

            <td style="width: 50px; text-align: center; font-weight: bold">Duration</td>

            <td style="width: 110px; text-align: center; font-weight: bold">Branch</td>

            <td style="width: 100px; text-align: center; font-weight: bold">Agent</td>

            <td style="width: 60px; text-align: center; font-weight: bold">Date Created</td>



            <td><asp:TextBox ID="txtTripFileName" runat="server" Width="105px"></asp:TextBox></td>

            <td><asp:TextBox ID="txtSupplier" runat="server" Width="105px"></asp:TextBox></td>

            <td><asp:TextBox ID="txtDestination" runat="server" Width="65px"></asp:TextBox></td>

            <td><asp:TextBox ID="txtDepartureDate" runat="server" Width="75px"></asp:TextBox></td>

            <td><asp:DropDownList ID="ddTravelType" runat="server" Width="75px"></asp:DropDownList></td>

            <td><asp:DropDownList ID="ddStage" runat="server" Width="70px"></asp:DropDownList></td>

            <td><asp:TextBox ID="txtDuration" runat="server" Width="50px"></asp:TextBox></td>

            <td><asp:DropDownList ID="ddBranch" runat="server" Width="110px"></asp:DropDownList></td>

            <td><asp:DropDownList ID="ddAgent" runat="server" Width="100px"></asp:DropDownList></td>

            <td><asp:TextBox ID="txtDateCreated" runat="server" Width="60px"></asp:TextBox></td>



            <td colspan="10">

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






                                <asp:ButtonField Text="SingleClick" CommandName="SingleClick" Visible="False" />                        

                                <asp:BoundField DataField="TripFileName" ItemStyle-Width="112px" />

                                <asp:BoundField DataField="SupplierName" ItemStyle-Width="114px" />

                                <asp:BoundField DataField="Destination" ItemStyle-Width="70px" />

                                <asp:BoundField DataField="DepartureDate" ItemStyle-Width="85px" />

                                <asp:BoundField DataField="TravelType" ItemStyle-Width="75px" />

                                <asp:BoundField DataField="Stage" ItemStyle-Width="70px" />

                                <asp:BoundField DataField="Duration" ItemStyle-Width="55px" />

                                <asp:BoundField DataField="BranchName" ItemStyle-Width="115px" />

                                <asp:BoundField DataField="AgentName" ItemStyle-Width="100px" />

                                <asp:BoundField DataField="DateCreated" ItemStyle-Width="60px" DataFormatString="{0:MM/dd/yy}" HtmlEncode="False"/>






Open in new window

Question by:kruegerste
    LVL 27

    Expert Comment

    The trigger for the filter would be a button click.. placed in the same row as the txtTripFileName, txtSupplier.. and the rest of the filter fields.

    Here's how I'd define the filters:

    The user enters/selects his/her choice of the filter.. that could be any combination of the filter fields that are defined above the grid view. When the user clicks the 'Filter' button, the event fired, checks which fields have a value in them and builds a select statement accordingly. See example below:

    // inside the filterButton_Click event
    string selectStatement = "SELECT [cols needed] from table1";
    string whereClause = string.Empty;
    if (txtTripFileName.Text.Length > 0)
         whereClause = "TripFileName = '" + txtTripFileName.Text.Trim() + "'";
    if (ddStage.SelectedIndex > 0) // index 0 is probably an informative line like 'Choose Stage'
        if(whereClause.Length > 0)
            whereClause = whereClause + " AND Stage = '" + ddStage.SelectedItem.Text + "'";
            whereClause = "Stage = '" + ddStage.SelectedItem.Text + "'";
    //... so on and so forth
    // finally combine the whereClause to the selectStatement;
    if( whereClause.Length > 0 )
       selectStatement = selectStatement + " WHERE " + whereClause;
    // pass this select statement to a SqlCommand

    Open in new window

    LVL 27

    Expert Comment

    and bind the gridview's datasource property to the datasource/datatable/datareader that is returned once the sql statement is executed.
    LVL 4

    Author Comment

    Thanks for the suggestion, but that does not sound like something that I want to do, if I'm understanding it correctly.  There has to be an easier way than building sql statement manually, that is the way we use to do it using classic Asp.

    I don't want to hit the database (execute new query) on every filter, too much overhead, especially when I have the dataset sitting in viewstate.  Your suggestion also becomes more complicated since Paging is enabled.  

    Filtering with a SQLDataSource defined on the UI is extremely simple, there has to be some easier way to use these same methods in a round about way.  

    LVL 27

    Accepted Solution

    LVL 4

    Author Comment

    Ok, so it seems I sort of answered a part of my question when I said I have a dataset in viewstate waiting to be filtered.  

    So I build a button click event as you suggested, on event firing, check the values of each control, filter the dataset and redisplay to the page.  Seems simple enough.  

    So I guess if this is the route that I take, how do I keep the state of paging correctly?  The built in filtering methods manage this already, so it seems I have to do some manual manipulation.  
    LVL 27

    Expert Comment

    The way I have set this up is to go back to the first page after any kind of filtering. Let's the user went to page 4 in the first search and found that there was too much data and decided to narrow it down. On this search, if the results did not extend to the 4th page, an error would be thrown.

    To avoid this, it makes sense explicitly code the gridview to show the first page on every filter (either a narrowing or widening effect).
    LVL 4

    Author Closing Comment

    Thanks, talking through this helped me realize what needs to be done.  I will reset any filter request to page 1 and just filter dataset and redisplay on buttonclick event.  Thanks again.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Suggested Solutions

    For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK ( for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now