CODEWITHSUNDEEP
c#mysqlasp.net
weirdgyn
weirdgyn

Reputation: 926

ASP.NET SqlDataSource filter behaviour

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?

Upvotes: 0

Views: 314

Answers (1)

Wibbler
Wibbler

Reputation: 1045

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;
}

Upvotes: 1

Related Questions