I've got a really weird problem and I can't seem to find a solution to it. I've got an SqlDataSource with a set of parameters and a select command in form of a stored procedure. One of the parameters is a string called @SearchPhrase which gets its value from a textbox that when left empty is suppose to return all posts. The parameter gets its value via a search button that first set the value of searchBox.Text.Trim() to the @SearchPhrase and then databinds a gridview that displays the search result.
The stored procedure works fine and returns the result as long as a value is entered in the textbox but when left empty it turns out the stored procedure is never executed. I know this because I've added a line to it that stores all of the parameters inserted every time it's called.
How come I can't get it to run without entering something in the textbox? The only way I can get it to execute is to enter a blank space and pass " " to the stored procedure and after that do a left and right trim on it. Why not when passing a string with length = 0?
Here's the code for the datasource and the assigning of the parameters. It's only the @SearchPhrase parameter that is causing the problem, the rest work just fine. The first three lines of the click event (the commented) is the somewhat ugly way to go if I want to assign a " " and that way make it work.
<asp:SqlDataSource ID="enterprisesDS" runat="server"
ConnectionString="<%$ ConnectionStrings:CRMdb %>"
SelectCommand="entGetEnterprises" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:Parameter Name="SearchPhrase" Type="String" />
<asp:Parameter Name="IsActive" Type="Boolean" DefaultValue="true" />
<asp:Parameter Name="Country" Type="Byte" />
<asp:Parameter Name="LocalReference" Type="String" />
<asp:Parameter Name="Class" Type="String" />
<asp:Parameter Name="LocationID" Type="Byte" />
</SelectParameters>
</asp:SqlDataSource>
protected void search_Click(object sender, EventArgs e)
{
//string searchPhrase = " ";
//if (searchBox.Text.Trim().Length > 0)
// searchPhrase = searchBox.Text.Trim();
enterprisesDS.SelectParameters["SearchPhrase"].DefaultValue = searchBox.Text;
enterprisesDS.SelectParameters["Country"].DefaultValue = countries.SelectedValue;
enterprisesDS.SelectParameters["LocalReference"].DefaultValue = localReferences.SelectedValue;
enterprisesDS.SelectParameters["Class"].DefaultValue = classes.SelectedValue;
string locID = "0";
if (locations.SelectedValue != null)
locID = locations.SelectedValue;
enterprisesDS.SelectParameters["LocationID"].DefaultValue = locID;
enterprises.DataBind();
}
