title: ASP.NET SqlDataSource filter behaviour
body: <p>I have a <code>GridView</code> object feed trough a <code>SqlDataSource</code>. 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.</p>
  
  <pre><code>&lt;asp:GridView
      DataSourceID="_sdsTable1"
      OnSorting="_gvTable1_Sorting"
      OnPageIndexChanging="_gvMovimenti_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="_gvMovimenti_RowDataBound" &gt;
      &lt;HeaderStyle CssClass="header" /&gt;
      &lt;FooterStyle CssClass="footer" /&gt;
      &lt;PagerSettings
          Visible="true"
          Mode="NumericFirstLast"
          PageButtonCount="3"
          Position="Bottom"
          NextPageText="Next page"
          PreviousPageText="Prev page"
          FirstPageText="First page"
          LastPageText="Last page" /&gt;
      &lt;RowStyle CssClass="odd" /&gt;
      &lt;AlternatingRowStyle CssClass="even" /&gt;
      &lt;PagerStyle HorizontalAlign="Center" /&gt;
      &lt;Columns&gt;
          &lt;asp:TemplateField Visible="false"&gt;
           &lt;HeaderTemplate&gt;&amp;nbsp;&lt;/HeaderTemplate&gt;
           &lt;ItemTemplate&gt;
            &lt;%#Eval("id")%&gt;
           &lt;/ItemTemplate&gt;
          &lt;/asp:TemplateField&gt;
          &lt;asp:TemplateField HeaderText="Date" SortExpression="date"&gt;
           &lt;ItemTemplate&gt;
            &lt;%#Eval("date","{0:dd/MM/yyyy HH:mm:ss}")%&gt;
           &lt;/ItemTemplate&gt;
           &lt;FooterTemplate&gt;
            TOTALE:
           &lt;/FooterTemplate&gt;
          &lt;/asp:TemplateField&gt;
          &lt;asp:TemplateField HeaderText="Price" SortExpression="price"&gt;
           &lt;ItemTemplate&gt;
            &lt;asp:Label ID="_lblPrice" runat="server" Text='&lt;%# Bind("price","{0:F2} &amp;euro;") %&gt;'&gt;&gt;&lt;/asp:Label&gt;
           &lt;/ItemTemplate&gt;
           &lt;FooterTemplate&gt;
            &lt;asp:Label ID="_lblTotal" runat="server" Text="0"&gt;&lt;/asp:Label
           &lt;/FooterTemplate&gt;
          &lt;/asp:TemplateField&gt;
          &lt;asp:BoundField DataField="description" HeaderText="Description" SortExpression="description" /&gt;
      &lt;/Columns&gt;
  &lt;/asp:GridView&gt;
  
  &lt;asp:SqlDataSource ID="_sdsTable1" runat="server"
      ConnectionString="&lt;%$ ConnectionStrings:_db %&gt;"
      ProviderName="&lt;%$ ConnectionStrings:_db.ProviderName %&gt;" 
      SelectCommand=" SELECT * FROM view1 WHERE id_user = @id_user;"&gt;
      &lt;SelectParameters&gt;
          &lt;asp:SessionParameter Type="Int32" Name="id_user" SessionField="USER_ID" /&gt;
      &lt;/SelectParameters&gt;
  &lt;/asp:SqlDataSource&gt;
  </code></pre>
  
  <p>In codebehind (in the event hanlder associated with the 'button' mentioned above) I build up the filter expression chaining the textbox values, with this code:</p>
  
  <pre><code>    if (!string.IsNullOrWhiteSpace(_txtFilter0.Text.Trim()))
      {
          _sFilter += "(descrizione LIKE '%" + _txtFilter0.Text.Trim() + "%')";
      }
  
      if (!string.IsNullOrWhiteSpace(_txtFilter1.Text.Trim()))
      {
          if (!string.IsNullOrWhiteSpace(_sFilter))
              _sFilter += " AND";
  
          _sFilter += "(descrizione LIKE '%" + _txtFilter1.Text.Trim() + "%')";
      }
  
      _sdsTable1.FilterExpression = _sFilter;
  </code></pre>
  
  <p>Everything works until I clear the fields that leads to an empty filter. I expected to retrieve all the data. For some reason, in this case, the last recordset is kept and shown apparently without a reason.</p>
  
  <p>I tried also to disable the SQLDataSource caching feature without luck:</p>
  
  <pre><code>    EnableCaching="false"
  </code></pre>
  
  <p>I tried also to issue a Select command, again without luck:</p>
  
  <pre><code> _sdsCrediti.Select(DataSourceSelectArguments.Empty);
  </code></pre>
  
  <p>Where I'm wrong?</p>