Setting Select Parameter = “” If User Doesn’t Provide Input From Search Page

I’m trying to run a simple select statement, and then output the results to a GridView in ASP.NET/ C# WebForms. The database I’m pulling from is a Microsoft SQL Server Database.

I’m trying to pass the variables received from user input from the search page to the results page through URL variables. I will then get them on the results page, and apply them to the query. If the results page is loaded without going through the search page first, the query string, From will be undefined, and the results page will display everything in the dataset.

The problem I’m running into is with the LastName. Let’s say LastName is optional in the search and may or may not be added. FirstName is required and the page cannot be submitted to the results page without it (for simplicity). The results page however can be accessed without being submitted from the search page. In this case, we simply do a select * from the table and populate the gridview with the entire result set.

Code behind for results page:

string fromval = Request.QueryString["from"];
//checking to see if from is next, otherwise just run the regular select * query.  If from = next, URL Variables exist.  If URL Variables exist, we need to add them to the query.
if (fromval == "next")   
{
    //set variables from URL, sent from text boxes found on the search page
    string sfName = Request.QueryString["pfName"];
    string slName = Request.QueryString["plname"];
}

dsResults.SelectParameters.Add("FirstName", sfName.ToString());   
//we will never hit this code without providing first name, so we know it will always be there

if (slName != "")  //if last name was provided in the search box
{
    dsResults.SelectParameters.Add("LastName", slName.ToString());  
    //if I provide a value for last name, this works fine
}
else  
{
    //simply set the value of @LastName to "" so like will ignore it. 'WHERE LASTNAME Like ""' should allow every row.  Runs fine with LastName LIKE "" in SqlServer Mgt. Studio. 
    dsResults.SelectParameters.Add("LastName", "");
}

dsResults.SelectCommand = "SELECT * FROM [exampleTable] WHERE FirstName LIKE '%' + @FirstName + '%' AND LastName LIKE '%' + @LastName + '%';"

//check for a postback
if (!Page.IsPostBack)
{
    //bind the gridview data
    gvResults.DataSource = dsResults;
    gvResults.DataBind();
}

If I provide a value for Last Name in the URL, it runs fine with no problem, and gives the expected results. If I don’t, the query pulls nothing as if it’s completely ignored.

Results page code simplified as much as possible:

<asp:GridView ID="gvResults" runat="server" AutoGenerateColumns="False" OnRowDataBound="gvResults_RowDataBound" CssClass="table table-bordered">
    <Columns>
        <asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
        <asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
    </Columns>
</asp:GridView>

<!-- If the query is not changed in the code behind (happens when URL Variables Exist) then it simply selects * from the example table. -->
<asp:SqlDataSource ID="dsResults" runat="server" ConnectionString="myConnectionString" SelectCommand="SELECT * FROM [exampleTable]"></asp:SqlDataSource>

Search page code simplified as much as possible:

<asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequireFN" runat="server" ErrorMessage="First Name is required." ControlToValidate="txtFirstName"></asp:RequiredFieldValidator>
<asp:TextBox ID="txtLastName" runat="server"></asp:TextBox>

<asp:Button ID="btnSearch" runat="server" OnClick="btnSearch_Click" text="Search" /></div>

Code behind for search page:

protected void btnSearch_Click(object sender, EventArgs e) 
{
    Response.Redirect("ResultsPage.aspx?from=next&pfName=" + txtFirstName.Text + "&plName=" + txtLastName.Text);
}

I realize that this may not be the best way to go about this, and am open to other suggestions if you have any.

Thank you.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Change

SELECT *
FROM [exampleTable]
WHERE FirstName LIKE '%' + @FirstName + '%' AND LastName LIKE '%' + @LastName + '%';

To this, where you bypass the LastName test when it is an empty string.

SELECT *
FROM [exampleTable]
WHERE FirstName LIKE '%' + @FirstName + '%' AND (coalesce(@LastName,'') = '' OR LastName LIKE '%' + @LastName + '%');

As an aside I recommend changing

dsResults.SelectParameters.Add("LastName", slName.ToString());

To the following where you fully specify the datatype and length rather than allowing them to be automatically determined. Doing it this way will avoid hard to find bugs and potentially improve performance.

dsResults.SelectParameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 128) { Value = slName.ToString() });

Where you use the correct SqlDbType and length to match the column in your database.

You might also want to check that the LastName string provided isn’t just a long series of spaces because that will also return no results to the user and could be confusing to them.

Method 2

You may have missed NULLs and DBNULL.Value values. This amplifies the previous answer a bit…

Please change:

if (fromval == "next")   
{
    //set variables from URL, sent from text boxes found on the search page
    string sfName = Request.QueryString["pfName"];
    string slName = Request.QueryString["plname"];
}

to read:

if (fromval == "next")   
{
    //set variables from URL, sent from text boxes found on the search page
    string sfName = Request.QueryString["pfName"];
    string slName = Request.QueryString["plname"];
}
if (!(sfName is string)) { sfName = ""; }
if (!(slName is string)) { slName = ""; }

And in case you have spaces as your whole string (unlikely from Request.QueryString), please change to:

dsResults.SelectParameters.Add("LastName", SqlDbType.NVarChar, 128). value = slName.ToString().Trim();

— no condition needed so eliminate the test on slName.

Finally, change the SQL to read like this…

dsResults.SelectCommand = "SELECT * FROM [exampleTable] WHERE FirstName LIKE '%' + @FirstName + '%' AND (@LastName = '' OR LastName LIKE '%' + @LastName + '%');"

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0