title: SqlDataSource SelectCommand using LIKE does not work
body: <p>I have the following T-SQL in a <code>SelectCommand</code>:</p>
  
  <pre><code>SELECT h.Business,
  hrl.frn
  FROM registration hrl
  INNER JOIN holder h on h.call = hrl.call
  WHERE 
  (h.Business like '%' + @business + '%' and h.Business is not null) 
  and 
  (hrl.frn = @frn and hrl.frn is not null)
  </code></pre>
  
  <p><code>business</code> and <code>frn</code> are tied to control parameters and it should return data even if one or both is left blank, but if I put in data just for <code>frn</code> for example, it does not return anything.  I think my T-SQL is not doing the right thing and I am also not sure if I am handling the <code>like</code> correctly.</p>
  
  <p>if both textboxes are left empty, it should return all data.  If <code>frn</code> is entered, but <code>business</code> is left blank, it should only return data related to that <code>frn</code>.  If <code>business</code> if entered, but <code>frn</code> is left blank, it should return all matches <code>like</code> <code>business</code>.  If both are entered, it should return data only matching the <code>frn</code> and the <code>business</code>.</p>
  
  <p>Also, I am not sure if doing the <code>and is not null</code> is actually necessary.</p>
  
  <pre><code>protected void btnSearch_Click(object sender, EventArgs e)
  {
      if (txtFRN.Text == "")
          frn = null;
  
      if (txtBusiness.Text == "")
          business = null;
  
      sqlDsMaster.SelectParameters[frn].DefaultValue = frn;
      sqlDsMaster.SelectParameters[business].DefaultValue = business;
  
      sqlDsMaster.DataBind();
  }
  </code></pre>
  
  <p>The above throws an "Object Reference not set to an instance" when it hits this line:</p>
  
  <pre><code>sqlDsMaster.SelectParameters[frn].DefaultValue = frn;
  </code></pre>
  
  <p><code>frn</code> and <code>business</code> are properties.</p>
  
  <hr>
  
  <p>Here is the <code>SearchMaster</code> stored procedure:</p>
  
  <pre><code>CREAETE PROCEDURE SearchMaster
  @business nvarchar(300) = NULL,
  @frn nvarchar(10) = NULL
  AS
  SELECT h.Business,
         hrl.frn
  FROM registration hrl
  INNER JOIN holder h on h.call = hrl.call
  WHERE (@business IS NULL OR h.Business like '%' + @business + '%') 
    AND (@frn IS NULL OR hrl.frn = @frn)
  </code></pre>
  
  <p>Here is the <code>SearchDetails</code> stored procedure:</p>
  
  <pre><code>CREATE PROCEDURE SearchDetails
  @business nvarchar(300) = NULL,
  @frn nvarchar(10) = NULL
  AS
  SELECT hrl.call 
  FROM registration hrl 
  INNER JOIN holder h ON h.call = hrl.call
  WHERE (@business IS NULL OR h.Business LIKE '%' + @business + '%') 
        AND (@frn IS NULL OR hrl.frn = @frn)
  </code></pre>
  
  <p>Here is the <code>SqlDataSource</code> for the <code>SearchMaster</code> procedure:</p>
  
  <pre><code>&lt;asp:SqlDataSource ID="sqlDsDetails" 
                     runat="server" 
                     ConnectionString="&lt;%$ ConnectionStrings:cnxString %&gt;
                     SelectCommandType="StoredProcedure" 
                     SelectCommand="SearchMaster"&gt;
    &lt;SelectParameters&gt;
      &lt;asp:ControlParameter Name="business" ControlID="txtBusiness" 
                            Type="String" PropertyName="Text"  
                            ConvertEmptyStringToNull="true" /&gt;
      &lt;asp:ControlParameter Name="frn" ControlID="txtFRN" 
                            Type="String" PropertyName="Text"
                            ConvertEmptyStringToNull="true"/&gt;
    &lt;/SelectParameters&gt;
  &lt;/asp:SqlDataSource&gt;
  </code></pre>
  
  <p>Here is the <code>SqlDataSource</code> for the <code>SearchDetails</code> procedure:</p>
  
  <pre><code>&lt;asp:SqlDataSource ID="sqlDsDetails" 
                     runat="server" 
                     ConnectionString="&lt;%$ ConnectionStrings:cnxString %&gt;
                     SelectCommandType="StoredProcedure" 
                     SelectCommand="SearchDetails"&gt;
    &lt;SelectParameters&gt;
      &lt;asp:Parameter Name="frn" Type="String" DefaultValue="" 
                     ConvertEmptyStringToNull="true" /&gt;
      &lt;asp:Parameter Name="business" Type="String" DefaultValue="" 
                     ConvertEmptyStringToNull="true" /&gt;
    &lt;/SelectParameters&gt;
  &lt;/asp:SqlDataSource&gt;
  </code></pre>
  
  <p>Here is the button click that binds the <code>SqlDsMaster</code>:</p>
  
  <pre><code>protected void btnSearch_Click(object sender, EventArgs e)
  {
      sqlDsMaster.DataBind();
  }
  </code></pre>
  
  <p>Here is the <code>gvMaster_RowCreated</code> that creates the rows for the details:</p>
  
  <pre><code>protected void gvMaster_RowCreated(object sender, GridViewRowEventArgs e)
  {
      if (e.Row.RowType == DataControlRowType.DataRow)
      {
          SqlDataSource ctrl = 
          e.Row.FindControl("sqlDsDetails") as SqlDataSource;
  
          if (ctrl != null &amp;&amp; e.Row.DataItem != null)
          {
              ctrl.SelectParameters["frn"].DefaultValue = 
              ((DataRowView)e.Row.DataItem)["frn"].ToString();
  
              ctrl.SelectParameters["business"].DefaultValue = 
              ((DataRowView)e.Row.DataItem)["business"].ToString();
           }
       }
   }
  </code></pre>
  
  <p><code>SearchMaster</code> and <code>SearchDetails</code> both work if I run it through SQL Server Management Studio and it works if I enter both data for <code>business</code> and <code>frn</code>, but if I enter just one, no data is returned.  Are the parameters set up correctly?  Also, if I am initializing the parameters to <code>null</code> in the procedure, is it still necessary to use <code>ConvertEmptyStringToNull</code>?</p>