ASP.NET Search on multiple parameters

Question

I am trying to display the results of a search on a gridview. I want the search to show the results for both last and first name. I am using ASP.NET with Subsonic and can't figure out how to modify the statemnt below. I am guessing it needs a wildcard somewhere?

Name: <asp:TextBox ID="txtSearchName" runat="server"></asp:TextBox> 

    GridView1.DataSource = new Select(PastAwardName.Schema.TableName + ".*", PastAwardType.Schema.TableName + ".*")
          .From(PastAwardName.Schema)
          .InnerJoin(PastAwardType.Schema.TableName, PastAwardType.Columns.VolID, PastAwardName.Schema.TableName, PastAwardName.Columns.VolID)
          .Where(PastAwardName.Columns.LName).IsEqualTo(this.txtSearchName.Text)
          .Or(PastAwardName.Columns.FName).IsEqualTo(this.txtSearchName.Text)
          .OrderAsc(PastAwardType.Columns.AwardYear)
          .ExecuteDataSet();

Answer 1

I think that should work.

Are you familiar with getting the generated SQL from that query?

SubSonic.SqlQuery q = new Select(PastAwardName.Schema.TableName + ".*", PastAwardType.Schema.TableName + ".*")
          .From(PastAwardName.Schema)
          .InnerJoin(PastAwardType.Schema.TableName, PastAwardType.Columns.VolID, PastAwardName.Schema.TableName, PastAwardName.Columns.VolID)
          .Where(PastAwardName.Columns.LName).IsEqualTo(this.txtSearchName.Text)
          .Or(PastAwardName.Columns.FName).IsEqualTo(this.txtSearchName.Text)
          .OrderAsc(PastAwardType.Columns.AwardYear);
string sql = q.BuildSqlStatement();

Check the value of sql (with a breakpoint on that line) to provide further clues into the problem.

For the wildcard - use the ContainsString() method instead of hardcoding a wildcard like so:

.Where(PastAwardName.Columns.LName).ContainsString(this.txtSearchName.Text)

This will automatically add the provider-specific wildcard character to the beginning and end of the parameter. You can also do StartsWith() and EndsWith().