So I have a Customers table on a sql 2008 server which I bind to a gridview in my app. The search button rebinds the table with the following code:
ControlParameter ctrlParam = new ControlParameter();
ctrlParam.ControlID = "SearchTextBox";
ctrlParam.PropertyName = "Text";
ctrlParam.Name = "Search";
QueryStringParameter ctrlParam2 = new QueryStringParameter();
ctrlParam2.DefaultValue = "Canceled";
ctrlParam2.QueryStringField = "Canceled";
ctrlParam2.Name = "CustomerStatus";
ctrlParam2.Type = TypeCode.String;
SqlDataSource1.SelectParameters.Clear();
SqlDataSource1.SelectParameters.Add(ctrlParam);
SqlDataSource1.SelectParameters.Add(ctrlParam2);
SqlDataSource1.SelectCommand = "SELECT FullText_for_Customers.RANK, [CustomerId], [CustomerStatus], [CustomerGroup], [CustomerFirstName], [CustomerLastName], [CustomerFullName], [CompanyName], [CustomerAddress], [CustomerCity], [CustomerState], [CustomerZipcode], [CustomerFullAddress], [CustomerEmail], [CustomerEmail2], [CustomerPhoneNumber], [CustomerPhoneNumberExt], [CustomerType], [CustomerSubscriptionType], [CustomerCost], [CustomerPaymentMethod], [CustomerPickUpDay], [CustomerPickUpDay2], [CustomerSignUpDate], [CustomerNotes], [CustomerDriver] FROM [Customers] INNER JOIN FREETEXTTABLE(Customers, (CustomerFirstName, CustomerLastName, CustomerFullName, CompanyName, CustomerAddress, CustomerCity, CustomerState, CustomerZipcode, CustomerFullAddress, CustomerEmail, CustomerEmail2, CustomerPhoneNumber, CustomerNotes), @Search) as FullText_for_Customers ON FullText_for_Customers.[KEY]=Customers.CustomerId WHERE ([CustomerStatus] <> @CustomerStatus) ORDER BY FullText_for_Customers.RANK DESC";
Now when the GridView is split into CustomerID, CustomerFirstName, CustomerLastName, CustomerPickUpDay, and CustomerSignUpDate.
When I search, I search just names, but I have to enter the full first or last name, I can't just type in a part of it; like take my last name, Bonilla, I have to search Bonilla for a result to be displayed, I can't just search Bon or Bo or illa. How could I have this work?
Free text search doesn't really work like that. However, you can get an approximation. Use CONTAINSTABLE instead of FREETEXTTABLE and append an asterisk (*) to your search term. That way, you'll get names that start with "Bo" or "Bon". There is no way to get names that end in "illa", however.
(Additionally, by using FREETEXTTABLE, you're asking the database to put your search terms through a thesaurus, which, for names, is probably a bad idea.)