SqlDataSource ControlParameter Null Value

Question

I am using SqlDataSource control to list out search result when user choose the date, if the date is null, then it list out all record.

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

But it return syntax error.

I want all to list all records when user leave the textbox blank. How to do that?

regards, Joe

dotnetstep · Accepted Answer · 2012-01-08 11:42:16Z

1

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

I did not check it but if you pass empty value than it convert to null so you have to check @sel_to_date is null instead @sel_to_date = '' use my snippet of code.

answered Jan 8, 2012 at 11:42

dotnetstep

17.5k6 gold badges61 silver badges80 bronze badges

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

Comments

Cristian Lupascu · Accepted Answer · 2012-01-08 11:46:12Z

Your case statement is incorrect. The case statement in SQL should return a value, not perform a comparison. See CASE (Transact-SQL)

A solution would be to replace the case with a conditional like so:

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

For your convenience, here it is on a single line so you can copy-paste it into the data source declaration:

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

Collectives™ on Stack Overflow

SqlDataSource ControlParameter Null Value

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related