• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4529
  • Last Modified:

Filter GridView without using SQLDataSource

Hello,

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.  
<table>
        <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>
        </tr>
        <tr>
            <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>
        </tr>
        <tr>
            <td colspan="10">
                        <asp:GridView ID="gvTripFileSearch" runat="server" 
                        OnPageIndexChanging="TripFileSearch_OnPageIndexChanging"
                        OnRowCommand="TripFileSearch_OnRowCommand"
                        OnRowDataBound="TripFileSearch_OnRowDataBound"
                        DataKeyNames="TripFileId">
                            <Columns>
                                <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"/>
                            </Columns>
                        </asp:GridView>
            </td>
        </tr>
    </table>

Open in new window

0
kruegerste
Asked:
kruegerste
  • 4
  • 3
1 Solution
 
nmarunCommented:
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 + "'";
    }
    else
    {
        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

0
 
nmarunCommented:
and bind the gridview's datasource property to the datasource/datatable/datareader that is returned once the sql statement is executed.
0
 
kruegersteAuthor Commented:
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.  

0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
kruegersteAuthor Commented:
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.  
0
 
nmarunCommented:
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).
0
 
kruegersteAuthor Commented:
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.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now