Create a Search

[snoopy80]

Hi,
I want to create a textbox for user to enter a search criteria.
Let say I have a SQL table Grocery with 2 columns "Manufactor" and "ItemDescription". The table will have entries like these:

Manufactor Itemdescription
ABA sardine with tomatoes sauce 8oz.
ABA sardine with olive oil 8oz.
CDC sardine with olive oil 8oz.
CDC tuna with olive oil 4oz.

when the user enter in the criteria text box "olive oil", then the gridview will display:
ABA sardine with olive oil 8oz.
CDC sardine with olive oil 4oz.
CDC tuna with olive oil 4oz.
and when the user enter in the criteria text box "sardine olive oil"
then the gridview will display:
ABA sardine with olive oil 8oz.
CDC sardine with olive oil 8oz.
or when criteria is "ABA then this is displays:
ABA sardine with tomatoes sauce 8oz.
ABA sardine with olive oil 8oz.

I can implement when the search is only 1 string long (for example "sardine") but I am having hard time to create a search that can allow user to enter a part of a description and find all the entries that is partially matched. Can someone give me an idea or guide me to the right direction to get information on how to implement this kind of search?

Any help is greatly appreciated.

 

[taree]

Why do not you try to do it from the database side and pass the value to your sql statement using parameter or querystring.

 

[jmeckley]

this is a sql question not an asp.net question. asp.net is just a web framework.

to answer your question use a like preadicate instead of an equals predicate

public DataTable GetProductsLike(string searchTerm)
{
using(SqlConnection cnn = new SqlConnection("db cnn str"))
using(SqlCommand cmd = new SqlCommand(cnn))
{
   cmd.CommandText = "select [Manufactor], [Itemdescription] from [table] where [Itemdescription] like @desc"
   cmd.AddParameter("desc", string.Format("%{0}%", searchTerm));
   results.Load(cmd.ExecuteReader());
   DataTable results = new DataTable();
   return results;
}
}

Jason Meckley
Programmer
Specialty Bakers, Inc.