Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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?

share|improve this question
 
Be careful when beginning like strings with '%' as it means you will not be able to use indexes on the filtered column. –  Steve Weet Mar 26 '09 at 22:00
 
Elaborate on what you mean. In this casse, I have no choice, I have to use LIKE –  Xaisoft Mar 26 '09 at 22:06
add comment

3 Answers

up vote 2 down vote accepted

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.

share|improve this answer
 
Can you elaborate on this a bit more? Thanks! –  Xaisoft Mar 26 '09 at 22:00
 
This format was suggested to me a while back in a question and it has worked flawlessly. I'd recommend testing for empty string first, since it makes the final query a little more readable. –  Dillie-O Mar 26 '09 at 22:01
 
Oh, the question I mentioned is found here: stackoverflow.com/questions/205526/… –  Dillie-O Mar 26 '09 at 22:01
 
ok, I will check that out. What I have is a TextBox called txtFRN for example and I am just setting up a <asp:ControlParameter>. Is there a way I can set the @frn to null if the txtFRN is blank. –  Xaisoft Mar 26 '09 at 22:05
 
Do I just set the SelectParameters[frn] = null –  Xaisoft Mar 26 '09 at 22:07
show 9 more comments

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.

share|improve this answer
add comment

"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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.