locked
Using the asp.SqlDataSource control I need to implement a conditional parameter RRS feed

  • Question

  • User-375129337 posted

    I have an asp.SqlDataSource control that I need to have a conditional parameter in the SelectCommand. When the ddlSermonList SelectedValue is 0 I need to remove the part of the WHERE clause with "AND Sermon.Sermon_ID = @Sermon_Equal". I have tried using the CASE WHEN THEN ELSE END construct but it seems it does not like putting  AND Sermon.Sermon_ID = @Sermon_Equal" after the THEN statement.

    Like this: CASE @Sermon_Equal WHEN 0 THEN Sermon.Sermon_ID > @Sermon_Equal ELSE Sermon.Sermon_ID = @Sermon_Equal END

    Of course When SelectedValue of ddlSermonList is 0 I get nothing. What I want is when the Selected Value is 0 All records are returned and when it is other than 0 only the slected record is returned.

     

    Here is what I currently have:

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:conGospelStr %>" SelectCommand="SELECT Sermon.Sermon_ID, Sermon.Sermon_Title, Sermon.Sermon_Summary, Sermon.Sermon_Lessons, Sermon.Sermon_CDPrice,

    Sermon.Sermon_PDF, Sermon.Sermon_Image, Sermon.Saint_ID, Sermon.Sermon_DateCreated, Sermon_Review.Review_Stars FROM Sermon

    LEFT OUTER JOIN Sermon_Review ON Sermon.Sermon_ID = Sermon_Review.Sermon_ID WHERE (Sermon.Sermon_Status = 'Act')

    AND Sermon.Sermon_ID = @Sermon_Equal"> <SelectParameters> <asp:ControlParameter Name="Sermon_Equal" ControlID="ddlSermonList" PropertyName="SelectedValue" /> </SelectParameters> </asp:SqlDataSource>

    Sunday, April 3, 2011 5:12 PM

Answers

  • User77042963 posted

    Here is a sample:

     

    --create table Sermon (id int identity(1,1), col1 varchar(50) default 'something', 
    --Sermon_Status varchar(10) default 'Act')
    --go
    
    --INSERT INTO Sermon default values
    --go 10
    
    
    declare @id int
    set @id=0  --Return all
    --set @id=8  --Return selected
    
    SELECT * from  Sermon 
    WHERE Sermon_Status = 'Act' AND (id=@id OR @id=0)
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 4, 2011 9:36 AM

All replies

  • User77042963 posted

    You can modify your WHERE clause:

    WHERE (Sermon.Sermon_Status = 'Act') AND (Sermon.Sermon_ID = @Sermon_Equa OR @Sermon_Equal=0)">

    Sunday, April 3, 2011 6:47 PM
  • User-375129337 posted

    Thanks for your replied, but unfortunately this doesn't really solve my problem. The issue is this: If @Sermon_Equal = 0 then the statment needs to read:

    WHERE (Sermon.Sermon_Status = 'Act') AND (Sermon.Sermon_ID > @Sermon_Equal) this allows all records to be returned.

    If @Sermon_Equal is greater than 0 then the statement needs to read:

    WHERE (Sermon.Sermon_Status = 'Act') AND (Sermon.Sermon_ID = @Sermon_Equal) this allows for that one specific record to be returned.

     

    Sunday, April 3, 2011 8:44 PM
  • User77042963 posted

    Have you tried the modify query?

     Why does it become this condition "Sermon.Sermon_ID > @Sermon_Equa"l when the @Sermon_Equal = 0? The code with Or  @Sermon_Equal=0  will return will recods for this column (0=0 is true all the time).

     If you tried the query and it does not work, please use a few rows data to demonstrate the problem. Thanks.

    Sunday, April 3, 2011 10:27 PM
  • User-375129337 posted
    Yes I did try the query, thank you. I applogize for not making this clearer. I have a drop down list, ddlSermonList, that contains a list of Items. The first item in the list has a value of 0, the text for that item is: Select All Items. The remainder of the list contains the unique ID number of the items as the value. If Select All is selected then by stating Sermon.Sermon_ID > @Sermon_Equal then all items should be returned. If any other item is slected the the query needs to use the Sermon_Sermon = @Sermon_Equal so that the just that one, selected ID, item should be returned. Your example will always return items with all items with an ID > than zero. I was hoping to be able to use a CASE statment something like:
    CASE @Sermon_Equal
    WHEN 0 THEN Sermon.Sermon_ID > @Sermon_Equal     'Return all Records
    ELSE Sermon.Sermon_ID = @Sermon_Equal    'Return just the selected Recods
    END
    However SQL Server 2008 doesn't seem to like including the entire phrase into the query statement.
     (@Sermon_Equal contains the Value of the item selected from the drop down list)
     CODE SAMPLE:
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:conGospelStr %>"
      SelectCommand="SELECT Sermon.Sermon_ID, Sermon.Sermon_Title, Sermon.Sermon_Summary, Sermon.Sermon_Lessons, Sermon.Sermon_CDPrice, 
      Sermon.Sermon_PDF, Sermon.Sermon_Image, Sermon.Saint_ID, Sermon.Sermon_DateCreated, Sermon_Review.Review_Stars FROM Sermon 
      LEFT OUTER JOIN Sermon_Review ON Sermon.Sermon_ID = Sermon_Review.Sermon_ID WHERE (Sermon.Sermon_Status = 'Act') AND 
      (CASE &Sermon_Equal 
       WHEN 0 THEN Sermon.Sermon_ID &gt; @Sermon_Equal 
       ELSE Sermon.Sermon_ID = @Sermon_Equal 
       END)">
        <SelectParameters>
             <asp:ControlParameter Name="Sermon_Equal"  ControlID="ddlSermonList" PropertyName="SelectedValue" />
       </SelectParameters>
    </asp:SqlDataSource>
    Monday, April 4, 2011 8:11 AM
  • User77042963 posted

    Here is a sample:

     

    --create table Sermon (id int identity(1,1), col1 varchar(50) default 'something', 
    --Sermon_Status varchar(10) default 'Act')
    --go
    
    --INSERT INTO Sermon default values
    --go 10
    
    
    declare @id int
    set @id=0  --Return all
    --set @id=8  --Return selected
    
    SELECT * from  Sermon 
    WHERE Sermon_Status = 'Act' AND (id=@id OR @id=0)
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 4, 2011 9:36 AM