SqlDataSource SelectCommand using LIKE does not work

Question

I have the following T-SQL in a SelectCommand:

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)

business and frn 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 frn 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 like correctly.

if both textboxes are left empty, it should return all data. If frn is entered, but business is left blank, it should only return data related to that frn. If business if entered, but frn is left blank, it should return all matches like business. If both are entered, it should return data only matching the frn and the business.

Also, I am not sure if doing the and is not null is actually necessary.

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();
}

The above throws an "Object Reference not set to an instance" when it hits this line:

sqlDsMaster.SelectParameters[frn].DefaultValue = frn;

frn and business are properties.

---

Here is the SearchMaster stored procedure:

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)

Here is the SearchDetails stored procedure:

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)

Here is the SqlDataSource for the SearchMaster procedure:

<asp:SqlDataSource ID="sqlDsDetails" 
                   runat="server" 
                   ConnectionString="<%$ ConnectionStrings:cnxString %>
                   SelectCommandType="StoredProcedure" 
                   SelectCommand="SearchMaster">
  <SelectParameters>
    <asp:ControlParameter Name="business" ControlID="txtBusiness" 
                          Type="String" PropertyName="Text"  
                          ConvertEmptyStringToNull="true" />
    <asp:ControlParameter Name="frn" ControlID="txtFRN" 
                          Type="String" PropertyName="Text"
                          ConvertEmptyStringToNull="true"/>
  </SelectParameters>
</asp:SqlDataSource>

Here is the SqlDataSource for the SearchDetails procedure:

<asp:SqlDataSource ID="sqlDsDetails" 
                   runat="server" 
                   ConnectionString="<%$ ConnectionStrings:cnxString %>
                   SelectCommandType="StoredProcedure" 
                   SelectCommand="SearchDetails">
  <SelectParameters>
    <asp:Parameter Name="frn" Type="String" DefaultValue="" 
                   ConvertEmptyStringToNull="true" />
    <asp:Parameter Name="business" Type="String" DefaultValue="" 
                   ConvertEmptyStringToNull="true" />
  </SelectParameters>
</asp:SqlDataSource>

Here is the button click that binds the SqlDsMaster:

protected void btnSearch_Click(object sender, EventArgs e)
{
    sqlDsMaster.DataBind();
}

Here is the gvMaster_RowCreated that creates the rows for the details:

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 && e.Row.DataItem != null)
        {
            ctrl.SelectParameters["frn"].DefaultValue = 
            ((DataRowView)e.Row.DataItem)["frn"].ToString();

            ctrl.SelectParameters["business"].DefaultValue = 
            ((DataRowView)e.Row.DataItem)["business"].ToString();
         }
     }
 }

SearchMaster and SearchDetails both work if I run it through SQL Server Management Studio and it works if I enter both data for business and frn, but if I enter just one, no data is returned. Are the parameters set up correctly? Also, if I am initializing the parameters to null in the procedure, is it still necessary to use ConvertEmptyStringToNull?

Answer 1

Change

"and h.Business is not null"

to

"or h.Business is null"

and

"and hrl.frn is not null"

to

"or hrl.frn is null"

That will return everything when those parameters are null.

Answer 2

I would do something like:

where (@business is null
          or @business = ''
          or h.Business like '%' + @business + '%')
      and (@frn is null
              or @frn = ''
              or hrl.frn = @frn)

If you make your empty search strings nulls before passing them, you can skip the @yyy = '' part.

Answer 3

"and is not null"

The stored procedures have it right: (@frn IS NULL OR hrl.frn = @frn). You want to select any line either if frn is null or it matches. The same pattern is applicable to the business property.

NullReferenceException

sqlDsMaster.SelectParameters[frn].DefaultValue = frn;

This fails, because you are using the value of the frn property (which may be null) instead of the string "frn" as index into the SelectParameters. You have the same error on the next line with business instead of "business".

ConvertEmptyStringToNull

I would pose the question the other way round: if you're using ConvertEmptyStringToNull, why would you want to burden the caller with the same functionality? Unless the empty string would be a valid value for frn or business, I'd leave ConvertEmptyStringToNull set and not care about it in the caller.

I hope that answers all your questions.