what is wrong with the query ? or code? [closed]

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answ开发者_Go百科ered in its current form. For help clarifying this question so that it can be reopened, visit the help center. Closed 11 years ago.

A user enter in the search charactre like this*"blue,yellow green,red pink,stack over flow"* Now i want to put this into array, and search it using the following code,

string[] search = mysearch.Split(',');
List<Result> myresult = new List<Result>();

for (int kk = 1; kk < search.Length; kk++)
  {  


where += " And '%" + search[kk] + "%'";              
    OleDbCommand sqlcmdCommand0 = new OleDbCommand("select Distinct name from table1  where       search like '%" + search[0] + "%' " + where + " order by name", myCon);
     sqlcmdCommand0.CommandType = CommandType.Text;
      OleDbDataReader sda = sqlcmdCommand0.ExecuteReader();

 while(sda.read())
    {
         myresult.name= sda.getString(0);
         Result.add(myresult);
    }
}
return Result;

The query Look like this :

select Distinct name from table1 where search like '%blue%' And '%yellow%' And '%Green %' order by name

and it is supposed to look like this:

select Distinct name from table1 where search like '%blue%' And '%yellow Green %' order by name

but the problem is that it seperate the string after the space not at the comma, and i want to put the string into the array after the occurances of comma not after the space between the character.

---

OK, I think you might just want to throw away your current code... Here's what you need to do to generate the WHERE condition:

string[] searchTerms = mySearch.Split(',');
StringBuilder conditions = new StringBuilder();
foreach(string term in searchTerms)
{
    if (conditions.Length > 0)
        conditions.Append(" AND ");
    conditions.AppendFormat("search LIKE '%{0}%'", term.Replace("'", "''"));
}
string query = "select Distinct name from table1";
if (conditions.Length > 0)
    query = query + " WHERE " + conditions;

---

Your SQL is not correct, you need a like operator for each comparison, and two operands for each operator. Also, you probably want to use the or operator between the comparisons, otherwise you will only find the records that contains all the colors, not any of the colors:

select Distinct name
from table1
where
  search like '%blue%' or
  search like '%yellow%' or
  search like '%green%'
order by name

---

If you want it to split on comma and space use the overload that takes a params argument, and pass a comma char and a whitespace char:

"red, blue green".Split(',', ' ');