?
Solved

Beginner's question: C# visual studio .net 2003 and sql, how to select records based on pattern matching

Posted on 2005-04-05
9
Medium Priority
?
225 Views
Last Modified: 2010-04-17
I am learning C# using VS .net 2003 and want to know how to display records in an Access database based on specific search terms.
What I am using as a learning model is the following article in MSDN Library:
Walkthrough: Displaying Data in a Windows Form Using a Parameterized Query

The code to pass the parameter to MSDE to display only records with specific State (such as CA) is:
oleDbDataAdapter1.SelectCommand.Parameters["state"].Value = txtStateParameter.Text;

So far, I managed to use another Access database and search on another field and can pattern match using %SearchTerm% fine.

Now I want to improve this search. Let say when user enter "red shirt", I want to display records that has "red" and "shirt" in any order. Not just records that has "red shirt". How do I pass this to the sql select statement to achieve my result. I know how to split the search string to 2 strings "red" and "shirt".

Thanks.
0
Comment
Question by:rj94070
  • 4
  • 4
9 Comments
 
LVL 3

Expert Comment

by:stu_pb
ID: 13710846
You will need to use an OR and two Search Patterns.

(%red%shirt% OR %shirt%red%)

You will have to add another parameter to the query and use the second string.

Good Luck!
0
 

Author Comment

by:rj94070
ID: 13711148
Thanks, Stu. I basically know this but the question is how do I pass this to the sql Select statement.

The sql statement use a question mark ? as parameter placeholder.
WHERE items LIKE ?
is the condition.

I could pass %red shirt% to it without problem and it parsed "WHERE items LIKE %red shirt%" fine.

But I don't know how to pass (%red%shirt% OR %shirt%red%) because the Select statement will be:

WHERE items LIKE (%red%shirt% OR %shirt%red%)
although this looks perfectly reasonable it didn't work. Don't know exactly why.

Besides, another BIG problem with this solution is: what to do when I have 4 or more words to search for? The number of ORs will grow exponentially!!! because words can be in any order.

I would like actual code if possible.
0
 
LVL 10

Expert Comment

by:Eduard Ghergu
ID: 13713983
You can use something like :
WHERE (items LIKE (%red%shirt%)) OR (items LIKE (%shirt%red%))

Also, have a look at: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_154e.asp

Greetings!
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:rj94070
ID: 13714129
ghergu,
Again this won't work because of the reason I mentioned earlier: let's say user enter a search string with 16 words in it. I will need over 65 thousand OR's!!!
The only acceptable way is to generate a dataset that has the records containing the first word, then search this dataset and create a new one that contains the 2nd word, and so on.

My problem is how to do this using C#, VS .NET and the oledbdataadapter...
Thanks.
0
 
LVL 3

Expert Comment

by:stu_pb
ID: 13715648
You could try this:

SELECT * FROM TABLE
WHERE
     items LIKE (%red%) AND
     items LIKE (%shirt%) AND
     .
     .
     .

This way you will only have to have one AND for each word you are searching for, this will prevent the exponential growth of ORs.

Here is some code to create the OleDbDataAdapter in C#:

OleDbConnection conn = new OleDbConnection(connection);
OleDbDataAdapter adapter = new OleDbDataAdapter();
OleDbCommand query = new OleDbCommand("SELECT * FROM TABLE WHERE (items LIKE %@Param1%) AND (items LIKE %@Param2%)", conn);
query.Parameters.Add(new OleDbParameter("@Param1","red");
query.Parameters.Add(new OleDbParameter("@Param2","shirt");
adapter.SelectCommand = new OleDbCommand(query, conn);
adapter.Fill(dataset);
 return dataset;

Here is where I got the basis for this code: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdataoledboledbdataadapterclasstopic.asp

Good Luck!



0
 

Author Comment

by:rj94070
ID: 13718609
Stu,
I think we are getting somewhere now with the Add method. Give me a day or two to implement the code and test it. Because the number of search words varies, I will have to dynamically generate the query string (ie, I cannot hard code it as having only 2 LIKEs.
Thanks.
0
 
LVL 3

Accepted Solution

by:
stu_pb earned 2000 total points
ID: 13718685
You could pretty easily but a loop around the query.Parameters.Add function to implement any number of parameters.

OleDbCommand query = new OleDbCommand();
query.Connection = conn;

for (int i = 0;i < parameterCount;i++)
{
     if (i == 0)
          query.CommandText = "SELECT * FROM TABLE WHERE (items LIKE %@Param1%)";
     else
          query.CommandText += "AND items( LIKE %@Param" + i.ToString + "%)";

     query.Parameters.Add(new SqlParameter("@Param" + i.ToString(),queryStringArray[i]);
}

Hope it  works out!
0
 

Author Comment

by:rj94070
ID: 13722373
Stu,
I haven't had time to code and test but I believe you pointed me to the right direction to research therefore you should receive full credit right away.
Thanks.
0
 
LVL 3

Expert Comment

by:stu_pb
ID: 13725719
I just looked at my last post, and there is a slight error in it.  I have posted the revised code below.  I changed the following statement:

query.CommandText = "SELECT * FROM TABLE WHERE (items LIKE %@Param0%)"; // Changed Param1 to Param0

Here is all the code.

OleDbCommand query = new OleDbCommand();
query.Connection = conn;

for (int i = 0;i < parameterCount;i++)
{
     if (i == 0)
          query.CommandText = "SELECT * FROM TABLE WHERE (items LIKE %@Param1%)";
     else
          query.CommandText += "AND items( LIKE %@Param" + i.ToString + "%)";

     query.Parameters.Add(new SqlParameter("@Param" + i.ToString(),queryStringArray[i]);
}

Thanks for the points and Good Luck!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Progress

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question