ObjectDataSource and null parameters

Question

Hi everybody,

I'm using Visual Web Developer 2008 EE using a Dataset (.xsd) to develop an invoicing application and i'm having trouble creating a custom search query. I have an ObjectDataSource that expects 4 ControlParameters, like so:

<asp:ObjectDataSource ID="odsInvoices" runat="server" SelectMethod="getInvoices" TypeName="bllInvoice">
    <SelectParameters>            
        <asp:ControlParameter ControlID="drpProjectsFilter" Type="Int32" Name="intProject" PropertyName="SelectedValue" ConvertEmptyStringToNull="True" />       
        <asp:ControlParameter ControlID="drpMonthsFilter" Type="Int32" Name="intMonth" PropertyName="SelectedValue" ConvertEmptyStringToNull="True" />
        <asp:ControlParameter ControlID="drpYearFilter" Type="Int32" Name="intYear" PropertyName="SelectedValue" ConvertEmptyStringToNull="True" />
        <asp:ControlParameter ControlID="drpStatusFilter" Type="Int32" Name="intStatus" PropertyName="SelectedValue" ConvertEmptyStringToNull="True" />
    </SelectParameters>

For each of these controls (dropdowns) I have a default value of "" (=empty string) and I have added the option ConvertEmptyStringToNull="True" for each parameter. The query that is behind the ObjectDataSource is this one:

SELECT ... FROM ...
WHERE (YEAR(invoices.invoice_date) = COALESCE (@year, YEAR(invoices.invoice_date))) 
AND (invoices.fk_project_id = COALESCE (@projectID, invoices.fk_project_id)) 
AND (MONTH(invoices.invoice_date) = COALESCE (@month, MONTH(invoices.invoice_date))) 
AND (invoices.invoice_status = COALESCE (@statusID, invoices.invoice_status))

Using COALESCE, I'm basically saying to ignore any of the 4 parameters if they are null and to return all rows.

The problem is that this query doesn't return any rows, unless I specify a value for each of the 4 parameters, essentially debunking the whole purpose of this custom search.

Any thoughts on why this isn't working? Thanks in advance!

Answer 1

From the MSSQL documentation:

ISNULL and COALESCE though equivalent, can behave differently. An expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters is considered to be NULL.

So try changing your query to look like this:

select ...
  from ...
 where year(invoices.invoice_date)  = isnull(@year, year(invoices.invoice_date))
   and invoices.fk_project_id       = isnull(@projectID, invoices.fk_project_id)
   and month(invoices.invoice_date) = isnull(@month, month(invoices.invoice_date))
   and invoices.invoice_status      = isnull(@statusID, invoices.invoice_status)

If that doesn't work, use SQL Profiler to check exactly what is being passed to your SELECT when it's invoked.

Answer 2

I managed to solve my own problem. As it turns out, the ControlParameters did not return NULL (as in a dbnull value), but the actual number 0. I have adapted my SQL statement to the following:

select ...  from ... 
where ((@year=0) OR (year(invoices.invoice_date)=@year)) 
and ((@projectID=0) OR (invoices.fk_project_id=@projectID))   
and ((@month=0) OR (month(invoices.invoice_date)=@month))
and ((@statusID=0) OR (invoices.invoice_status=@statusID))

This way, if a ControlParameter has a value of 0, it will still return all rows. Hope this can help somebody.

Regards, Stijn