CODEWITHSUNDEEP
c#sqlwinforms
chopperfield
chopperfield

Reputation: 567

select query where clause with more than 1 value

I have a column named ast_code

enter image description here

which I have already retrieved to a string variable. I'm trying to run a select query using a where clause based on the string value that I stored in that variable.

Here's the code I tried:

public void grid()
{
     datatable dt = new datatable();
     SqlDataAdapter adapter = new SqlDataAdapter();
     SqlCommand command = new SqlCommand();
     try
     {
     command.Connection = myConnection;
     command.CommandText = "SELECT code, name from table.menu where code <> '"+ ast_code + "'";
     adapter.SelectCommand = command;
            adapter.Fill(ds);
            adapter.Fill(dt);
            myConnection.Open();
     }
        catch(Exception ex)
        {
            MessageBox.Show("error" + ex);
        }
        myConnection.Close();
     gridControl1.DataSource = dt;          
    }

When I ran the query, it returned no results unless the value in the string variable only contained one value (e.g. 0110300).

I then tried to transform the contents of the variable:

 ast_code = a.Replace(';',',').Replace(' ','\'');

but it returned an error due a missing '. Don't mind the a; it was a parsing variable value. I have already tried to store them to a list but that isn't working properly, either.

What I need to do is generate a where clause that can handle multiple values.

UPDATE

iam using @rbr94 suggestion ast_code = a.Replace("; ", "', '"); 

 command.CommandText = "SELECT code, name from table.menu where code not in '"+ ast_code + "'";

it works when iam using the top string value, but for the second row `string value give me an error enter image description here enter image description here

Upvotes: 0

Views: 446

Answers (3)

chopperfield
chopperfield

Reputation: 567

so, my solution to my own problem is that first iam splitting it to store in a list<> so iam just directly store the ast_code = a

and then the code i use to store

  string[] code_asst = ast_code.Split(';');
  List<string> clean_code_asst = new List<string>();
  foreach (string s in code_asst)
        {
            clean_code_asst.Add("'"+s.Trim()+"'");
        }
  string types = string.Join(",", clean_code_asst.ToArray());

for the query i use is

  var sql = "Select code from table.menu where code NOT IN ("+types+")";
  command.CommandText = sql;

Upvotes: 0

rbr94
rbr94

Reputation: 2287

The error you get results from wrongly setting the ' in your where clause. I give an example:

You have a string like this: 0110300; 0110370

Then you use ast_code = a.Replace(';',',').Replace(' ','\''); and this results in this: 0110300,'0110370. When you use this in your where clause it is obvious that this won't work: 

where code <> '0110300,'0110370'

First of all use WHERE NOT IN and then you need to do the following:

C#:

ast_code = string.Format("'{0}'", a.Replace("; ","', '"));

SQL:

 where code NOT IN (" + ast_code + ")

 //results in
 where code NOT IN ('0110300','0110370')

This sets ' before and after your ast_code string. Then you replace your ; and the space after it with '; ' so that each value is encapsulated with ' marks on both sides.

This will result in this: '0110300', '0110370'

Upvotes: 2

Wudge
Wudge

Reputation: 357

I think you have to use NOT IN in your statement like this:

command.CommandText = "SELECT code, name from table.menu where code NOT IN ('" + ast_code.Replace(";", "','") + "')";

Upvotes: 0

Related Questions