locked
How do I filter SqlDataSource with Boolean values? RRS feed

  • Question

  • User1519416827 posted

    I have an SqlDataSource, which is filtered by two RadioButtonLists. The markup is as follows:

     

    <asp:SqlDataSource ID="SqlDataSourceOrders" runat="server" 
            ConnectionString="<%$ ConnectionStrings:NPTCOREConnectionString %>" 
            SelectCommand="SELECT nptc_tblLicenses.nptcLicenses_LicenseName AS Produkt, nptc_tblLicenses.nptcLicenses_Amount AS Antal, nptc_tblLicenses.nptcLicenses_LicenseApproved AS Godkendt, aspnet_Users.UserName AS Login, nptc_tblCustomer.nptcCustomer_Name AS Navn, nptc_tblCustomer.nptcCustomer_Company AS Firma, nptc_tblCustomer.nptcCustomer_Country AS land, nptc_tblLicenses.nptcLicenses_Date AS Dato FROM aspnet_Users INNER JOIN nptc_tblLicenses ON aspnet_Users.UserId = nptc_tblLicenses.nptcLicenses_UserId INNER JOIN nptc_tblCustomer ON aspnet_Users.UserId = nptc_tblCustomer.nptcCustomer_UserId">
        <FilterParameters>
                 <asp:ControlParameter ControlID="RadioButtonListProducts" Name="Products" PropertyName="SelectedValue" />
                 <asp:ControlParameter ControlID="RadioButtonListStatus" Name="Status" PropertyName="SelectedValue"  />
        </FilterParameters>  
        </asp:SqlDataSource>
    
        
    
        Produkt:
        <asp:RadioButtonList ID="RadioButtonListProducts" runat="server" 
            AppendDataBoundItems="True" AutoPostBack="True" 
            DataSourceID="SqlDataSourceProducts" RepeatDirection="Horizontal" 
            DataTextField="nptcLicenses_LicenseName" 
            DataValueField="nptcLicenses_LicenseName" ForeColor="White">
            <asp:ListItem Text="Alle" Value="Alle" Selected="True"></asp:ListItem>
        </asp:RadioButtonList>
    
        Status:
        <asp:RadioButtonList ID="RadioButtonListStatus" runat="server" 
            AppendDataBoundItems="True" AutoPostBack="True" 
            RepeatDirection="Horizontal" 
            DataTextField="nptcLicenses_LicenseName" 
            DataValueField="nptcLicenses_LicenseName" ForeColor="White">
            <asp:ListItem Text="Alle" Value="Alle" Selected="True"></asp:ListItem>
            <asp:ListItem Text="Godkendte" Value="Godkendte" ></asp:ListItem>
            <asp:ListItem Text="Ikke godkendte" Value= "Ikke godkendte" ></asp:ListItem>
        </asp:RadioButtonList>

     

    In my code-behind I add the filterexpression when the radiobuttonlists are changed

    Protected Sub RadioButtonListStatus_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles RadioButtonListStatus.SelectedIndexChanged
            If RadioButtonListProducts.SelectedValue = "Alle" And RadioButtonListStatus.SelectedValue = "Alle" Then
                SqlDataSourceOrders.FilterExpression = ""
            End If
    
            If RadioButtonListProducts.SelectedValue <> "Alle" And RadioButtonListStatus.SelectedValue = "Alle" Then
                SqlDataSourceOrders.FilterExpression = "Produkt = '{0}'"
            End If
    
            If RadioButtonListProducts.SelectedValue = "Alle" And RadioButtonListStatus.SelectedValue = "Godkendte" Then
                SqlDataSourceOrders.FilterExpression = "Produkt = '{0}' AND Godkendt = True"
            End If
    
            
        End Sub

     

    My problem is that while it works fine when changing the RadioButtonListProducts the filtering works nicely, there is a problem with the RadioButtonListStatus, that filters on the Boolean column, 'Godkendt'. It works nicely if its set to 'Alle' (No filtering on that parameter) but when set to anything else, no records are shown. I suppose the problem is in the filterexpression

    "Produkt = '{0}' AND Godkendt = True"

    where I have been trying different things with no luck. Any suggestions?

    Thursday, February 17, 2011 5:27 AM

All replies

  • User1519416827 posted

    My, this forum works good. 10 seconds after pressing the Post-button i realized what was wrong.

    I was trying to filter the products with the value 'All' which is nonsense.

     

    It should have been:

    If RadioButtonListProducts.SelectedValue = "Alle" And RadioButtonListStatus.SelectedValue = "Godkendte" Then
                SqlDataSourceOrders.FilterExpression = "Godkendt = True"
            End If

    and

    If RadioButtonListProducts.SelectedValue <> "Alle" And RadioButtonListStatus.SelectedValue = "Godkendte" Then
                SqlDataSourceOrders.FilterExpression = "Produkt = '{0}' AND Godkendt = True"
            End If

    Thursday, February 17, 2011 5:47 AM