SqlDataSource ControlParameter空值

Question

当用户选择日期时，我使用SqlDataSource控件列出搜索结果，如果日期为空，则列出所有记录。

<asp:SqlDataSource ID="SqlDataSource1" runat="server"  meta:resourcekey="SqlDataSource1" ConnectionString="<%$ ConnectionStrings:Test_ConnectionString %>"
SelectCommand="select MeetingID, MeetName as MeetingName, MeetDate, MeetTime from Meeting where Status ='Recorded' and Case when @sel_to_date ='' then MeetDate <= '2200-12-31' else MeetDate = @sel_to_date end order by MeetDate desc, Meettime desc ">
<SelectParameters>                        
<asp:ControlParameter ConvertEmptyStringToNull="true" ControlID="datepicker" Name="sel_to_date" DefaultValue="" PropertyName="Text" Type="String"/>
</SelectParameters>
</asp:SqlDataSource>

但是它返回语法错误。

当用户将文本框留空时，我希望全部列出所有记录。如何做到这一点？

向您致敬，乔

Answer 1

您的case语句不正确。SQL中的case语句应该返回值，而不是执行比较。请参阅CASE (Transact-SQL)

一种解决方案是用条件替换case，如下所示：

select 
    MeetingID, MeetName as MeetingName, MeetDate, MeetTime 
from Meeting 
    where Status ='Recorded' 
    and 
    (
        (@sel_to_date ='' and MeetDate <= '2200-12-31' )
        or
        MeetDate = @sel_to_date
    )
order by MeetDate desc, Meettime desc

为了方便起见，这里只有一行，所以您可以将其复制粘贴到数据源声明中：

select MeetingID, MeetName as MeetingName, MeetDate, MeetTime from Meeting where Status ='Recorded' and ((@sel_to_date ='' and MeetDate <= '2200-12-31' ) or MeetDate = @sel_to_date) order by MeetDate desc, Meettime desc

Answer 2

<asp:SqlDataSource ID="SqlDataSource1" runat="server"  meta:resourcekey="SqlDataSource1" ConnectionString="<%$ ConnectionStrings:Test_ConnectionString %>"
SelectCommand="select MeetingID, MeetName as MeetingName, MeetDate, MeetTime from Meeting where Status ='Recorded' and Case when @sel_to_date is null then MeetDate <= '2200-12-31' else MeetDate = @sel_to_date end order by MeetDate desc, Meettime desc ">
<SelectParameters>                        
<asp:ControlParameter ConvertEmptyStringToNull="true" ControlID="datepicker" Name="sel_to_date" DefaultValue="" PropertyName="Text" Type="String"/>
</SelectParameters>
</asp:SqlDataSource>

我没有检查它，但是如果你传递的是空值，那么它会转换为null，所以你必须检查@sel_to_date is null，而不是@sel_to_date = '‘，使用我的代码片段。