SqlDataSource 问题上多个可能的 FilterExpression

发布于 2024-10-01 09:15:24 字数 930 浏览 4 评论 0原文

我在页面中有一个 SqlDataSource，它将根据 1 或 2 个查询字符串和/或过滤器文本框显示不同的结果。

这是我的 SqlDataSource 和过滤器：

  <asp:SqlDataSource ID="sdsAudits" runat="server" 
        ConnectionString="<%$ ConnectionStrings:constring %>" 
        SelectCommand="SELECT * FROM [Audit]" FilterExpression="source = {0} AND customer = {1} AND (itemID like '%{2}%' OR parentID like '%{2}%')">
        <FilterParameters>
        <asp:QueryStringParameter Name="source" QueryStringField="source" />
        <asp:QueryStringParameter Name="customer" QueryStringField="customer" />
        <asp:ControlParameter Name="txtFilter" ControlID="txtFilter" PropertyName="Text" />
        </FilterParameters>

但由于某种原因，对 3 个可能的过滤器中任何一个的过滤都不起作用。我尝试取出 2 个查询字符串过滤器，只留下文本框过滤器，然后它工作得很好 - 所以我猜我的过滤器表达式是错误的？

大家有什么想法吗？请记住，如果 3 个过滤器具有 2 个查询字符串并已在文本框中输入，则所有 3 个过滤器可能会同时处于“活动”状态，也可能根本没有，或者当然也可能处于两者之间。

原文

I have an SqlDataSource in a page, which will display different results depending on 1 or 2 query strings, and/or a filter textbox.

Here is my SqlDataSource and filters:

  <asp:SqlDataSource ID="sdsAudits" runat="server" 
        ConnectionString="<%$ ConnectionStrings:constring %>" 
        SelectCommand="SELECT * FROM [Audit]" FilterExpression="source = {0} AND customer = {1} AND (itemID like '%{2}%' OR parentID like '%{2}%')">
        <FilterParameters>
        <asp:QueryStringParameter Name="source" QueryStringField="source" />
        <asp:QueryStringParameter Name="customer" QueryStringField="customer" />
        <asp:ControlParameter Name="txtFilter" ControlID="txtFilter" PropertyName="Text" />
        </FilterParameters>

But for some reason, the filtering for any of the 3 possible filters don't work. I tried taking out the 2 query string filters, and leaving just the textbox filter, and it worked fine then - so I'm guessing my filter expression is wrong?

Any ideas guys? Remember all 3 filters could be 'active' at once, if they have the 2 query strings and have typed into the textbox, or they could be none at all, or of course anything in between.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

贩梦商人 2024-10-08 09:15:24

过滤器旨在在数据集模式下工作 - 将数据检索到内存中，然后进行过滤。效率不高 - 在 select 命令本身中使用参数会更好，如下所示：

<asp:SqlDataSource ID="sdsAudits" runat="server" 
    ConnectionString="<%$ ConnectionStrings:constring %>" 
    SelectCommand="SELECT * FROM [Audit] where source = @source AND customer = @customer AND (itemID like '%' + @txtFilter + '%' OR parentID like '%'+@txtFilter+'%')">
    <SelectParameters>
      <asp:QueryStringParameter Name="source" QueryStringField="source" />
      <asp:QueryStringParameter Name="customer" QueryStringField="customer" />
      <asp:ControlParameter Name="txtFilter" ControlID="txtFilter" PropertyName="Text" />
    </SelectParameters>
</asp:SqlDataSource>

或者只是添加

  DataSourceMode="DataSet"

到 SqlDataSource 标记中

Filter is intended to work in DataSet mode - data is retrieved into memory, and then filtered. Not very efficient - much better to use parameters in the select command itself, like so:

<asp:SqlDataSource ID="sdsAudits" runat="server" 
    ConnectionString="<%$ ConnectionStrings:constring %>" 
    SelectCommand="SELECT * FROM [Audit] where source = @source AND customer = @customer AND (itemID like '%' + @txtFilter + '%' OR parentID like '%'+@txtFilter+'%')">
    <SelectParameters>
      <asp:QueryStringParameter Name="source" QueryStringField="source" />
      <asp:QueryStringParameter Name="customer" QueryStringField="customer" />
      <asp:ControlParameter Name="txtFilter" ControlID="txtFilter" PropertyName="Text" />
    </SelectParameters>
</asp:SqlDataSource>

or just add