ObjectDataSource and null parameters
I am using Visual Web Developer 2008 EE using dataset (.xsd) to develop an invoicing application and I am having trouble creating a custom search query. I have an ObjectDataSource that expects 4 ControlParameters, for example:
<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 added a ConvertEmptyStringToNull =" True "parameter for each parameter. The query behind the ObjectDataSource is this:
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 basically say to ignore any of the 4 parameters if they are null and return all strings.
The problem is that this query doesn't return any rows unless I provide 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? Thank you in advance!
source to share
From the MSSQL documentation:
ISNULL and COALESCE, while equivalent, can behave differently. An expression that includes ISNULL with nonzero parameters is considered NOT NULL, and expressions with COALESCE with nonzero parameters are considered 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 passed to yours SELECT
when it is called.
source to share
I managed to solve my own problem. As it turns out, ControlParameters did not return NULL (as in the dbnull value), but the actual number is 0. I applied the 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))
Thus, if the ControlParameter is 0, it will still return all rows. Hope this helps someone.
Regards, Stein
source to share