title: ObjectDataSource and null parameters
body: <p>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:</p>
  
  <pre><code>&lt;asp:ObjectDataSource ID="odsInvoices" runat="server" SelectMethod="getInvoices" TypeName="bllInvoice"&gt;
      &lt;SelectParameters&gt;            
          &lt;asp:ControlParameter ControlID="drpProjectsFilter" Type="Int32" Name="intProject" PropertyName="SelectedValue" ConvertEmptyStringToNull="True" /&gt;       
          &lt;asp:ControlParameter ControlID="drpMonthsFilter" Type="Int32" Name="intMonth" PropertyName="SelectedValue" ConvertEmptyStringToNull="True" /&gt;
          &lt;asp:ControlParameter ControlID="drpYearFilter" Type="Int32" Name="intYear" PropertyName="SelectedValue" ConvertEmptyStringToNull="True" /&gt;
          &lt;asp:ControlParameter ControlID="drpStatusFilter" Type="Int32" Name="intStatus" PropertyName="SelectedValue" ConvertEmptyStringToNull="True" /&gt;
      &lt;/SelectParameters&gt;
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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))
  </code></pre>
  
  <p>Using COALESCE, I'm basically saying to ignore any of the 4 parameters if they are null and to return all rows.</p>
  
  <p>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.</p>
  
  <p>Any thoughts on why this isn't working?
  Thanks in advance! </p>