ASP.NET SqlDataSource filter behaviour

Question

I have a GridView object fed trough a SqlDataSource. In the same form I have also a number of TextBoxes used to build a filtering expression for the datasource. The filtering is started by pressing a Button.

<asp:GridView
    DataSourceID="sdsTable1"
    OnSorting="gvTable1_Sorting"
    OnPageIndexChanging="gvTable1_PageIndexChanging"
    runat="server"
    CssClass="list_table"
    ID="_gvTable1"
    CellPadding="0" CellSpacing="0"
    AutoGenerateColumns="false"
    EmptyDataText="No data."
    ShowHeader="true" ShowFooter="true"
    AllowSorting="true"
    AllowPaging="true"
    PageSize="10" 
    OnRowDataBound="gvTable1_RowDataBound" >
    <HeaderStyle CssClass="header" />
    <FooterStyle CssClass="footer" />
    <PagerSettings
        Visible="true"
        Mode="NumericFirstLast"
        PageButtonCount="3"
        Position="Bottom"
        NextPageText="Next page"
        PreviousPageText="Prev page"
        FirstPageText="First page"
        LastPageText="Last page" />
    <RowStyle CssClass="odd" />
    <AlternatingRowStyle CssClass="even" />
    <PagerStyle HorizontalAlign="Center" />
    <Columns>
        <asp:TemplateField Visible="false">
         <HeaderTemplate>&nbsp;</HeaderTemplate>
         <ItemTemplate>
          <%#Eval("id")%>
         </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Date" SortExpression="date">
         <ItemTemplate>
          <%#Eval("date","{0:dd/MM/yyyy HH:mm:ss}")%>
         </ItemTemplate>
         <FooterTemplate>
          TOTALE:
         </FooterTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Price" SortExpression="price">
         <ItemTemplate>
          <asp:Label ID="lblPrice" runat="server" Text='<%# Bind("price","{0:F2} &euro;") %>'>></asp:Label>
         </ItemTemplate>
         <FooterTemplate>
          <asp:Label ID="lblTotal" runat="server" Text="0"></asp:Label
         </FooterTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="description" HeaderText="Description" SortExpression="description" />
    </Columns>
</asp:GridView>

<asp:SqlDataSource ID="sdsTable1" runat="server"
    ConnectionString="<%$ ConnectionStrings:_db %>"
    ProviderName="<%$ ConnectionStrings:_db.ProviderName %>"
    DataSourceMode="DataSet"
    SelectCommand=" SELECT id, id_user, price, description FROM view1 WHERE id_user = @id_user;">
    <SelectParameters>
        <asp:SessionParameter Type="Int32" Name="id_user" SessionField="USER_ID" />
    </SelectParameters>
</asp:SqlDataSource>

In codebehind (in the event hanlder associated with the Button mentioned above) I build up the filter expression chaining TextBoxes values, with this code:

    if (!string.IsNullOrWhiteSpace(_txtFilter0.Text.Trim()))
    {
        _sFilter += "(description LIKE '%" + _txtFilter0.Text.Trim() + "%')";
    }

    if (!string.IsNullOrWhiteSpace(_txtFilter1.Text.Trim()))
    {
        if (!string.IsNullOrWhiteSpace(_sFilter))
            _sFilter += " AND";

        _sFilter += "(description LIKE '%" + _txtFilter1.Text.Trim() + "%')";
    }

    sdsTable1.FilterExpression = _sFilter;

Everything works until I clear the fields that leads to an empty filter, im such circumstances I expected to retrieve all the records but for some reason, in this case, the last recordset is kept and shown apparently without a reason.

I tried also to disable the SQLDataSource caching feature without luck:

    EnableCaching="false"

I tried also to issue a Select command, again without luck:

 sdsTable1.Select(DataSourceSelectArguments.Empty);

Where I'm wrong?

To be more precise the SQLDataSource use a View instead a table. — weirdgyn
– weirdgyn, Commented Jul 9, 2015 at 11:43
Looks like _sFilter is not getting set back to null if the search fields are cleared. It probably still contains the last filter clause because it only changes if the fields are NOT empty. Make sure you reset it. — Crowcoder
– Crowcoder, Commented Jul 9, 2015 at 11:46

Wibbler · Accepted Answer · 2015-07-09 11:53:52Z

1

Your _sFilter property will be persisted across postbacks, so as you're only updating it when your filter text boxes are not empty it will remain at the last set value when you clear them. Putting a breakpoint in your code at the line _sdsTable1.FilterExpression = _sFilter; should confirm this.

To solve the issue, either clear the _sFilter property before rebuilding it in your event handler, or write an additional check:

if (string.IsNullOrWhiteSpace(_txtFilter1.Text.Trim()) & string.IsNullOrWhiteSpace(_txtFilter0.Text.Trim()) )
{
  _sFilter = null;
}

answered Jul 9, 2015 at 11:53

Wibbler

1,0439 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

weirdgyn Over a year ago

@Crowcoder the real problem is not that... _sFilter was being reset to "" each time the event handler was triggered. But you came really close to the real problem since to force SQLDataSouce to 'reset' I had to set FilterExpression to null... yet your solution was correct.

Collectives™ on Stack Overflow

ASP.NET SqlDataSource filter behaviour

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related