Using SqlDataSource SelectParameters to show gridview rows where a column is NULL

Question

I am using ASP.NET (4.0 Framework) with VB.NET in the codebehind. I have this SqlDataSource which my gridview is using as a datasource:

<asp:SqlDataSource ID="repairApproverData" runat="server" 
        ConnectionString="<%$ ConnectionStrings:SystemsConnectionString %>" 
        SelectCommand="SELECT CASE(dateFixed) WHEN ISNULL(dateFixed,1) THEN DATEDIFF(day,[date],[dateFixed]) ELSE DATEDIFF(day,[date],GETDATE()) END AS daysWaited,[id],[date],[lot],[stock],[vin],[makeModel],[issue],[approved],[dateFixed],[comments] FROM [repairApprover] WHERE [lot] = IsNull(@lot,lot)"
        DeleteCommand="DELETE FROM [repairApprover] WHERE id = @id"
        CancelSelectOnNullParameter="false"
        >

As you can see at the end of SelectCommand I have WHERE [lot] = IsNull(@lot,lot) which references this ControlParameter:

    <SelectParameters>
        <asp:ControlParameter ControlID="lotBox" Name="lot" PropertyName="Text" Type="String" />
    </SelectParameters> 

The control parameters gets the value to filter lot from a DropDownList:

<asp:DropDownList ID="lotBox" runat="server" ClientIDMode="Static" AutoPostBack="True">
    <asp:ListItem Value="">All</asp:ListItem>
    <asp:ListItem Value="1">1</asp:ListItem>
    <asp:ListItem Value="2">2</asp:ListItem>
    <asp:ListItem Value="3">3</asp:ListItem>
    <asp:ListItem Value="5">5</asp:ListItem>
    <asp:ListItem Value="6">6</asp:ListItem>
    <asp:ListItem Value="40">40</asp:ListItem>
</asp:DropDownList>

This all works great. However, I do not know how to go about adding another DropDownList that will allow the user to filter out/in rows that have a NULL value in a column called "dateFixed".

Basically I need to be able to do something similiar to the above that will determine which of the following three strings are added to my SelectCommand

AND [dateFixed] IS NOT NULL
AND [dateFixed] IS NULL

and the third string would be empty.

Answer 1

You need to build your SELECT dynamically. Something like this:

var sel = "Select ......FROM ...... ";
bool hasWhere;
if (combo1.SelectedIndex > -1)
{
    if (!hasWhere) 
    { 
        hasWhere = true;
        sel += " where ";
    }
    sel += "<your where condition 1>";
} 

if (combo2.SelectedIndex > -1)
{
    if (!hasWhere) 
    { 
        hasWhere = true; 
        sel += " where "; 
    }
    else 
        sel += " and ";
    sel += "<your where condition 1>";
}

if (combo3.SelectedIndex > -1)
{
    if (!hasWhere) 
    { 
        hasWhere = true; 
        sel += " where "; 
    }
    else 
        sel += " and ";
    sel += "<your where condition 2>";
}

Hopefully this is what you've asked about. Please note that this crude example. You may want to use StringBuilder, etc.

Answer 2

@dataFixed paramter

AND ([dateFixed] = @dateFixed OR @dateFixed IS NULL)