select query where clause with more than 1 value

Question

I have a column named ast_code

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

Hi, I would advise you to use parameterized queries. http://stackoverflow.com/questions/35163361/how-can-i-add-user-supplied-input-to-an-sql-statement — xszaboj, Nov 16 '16 at 09:27
@xszaboj if i use parameterized, only 1 value, and also need to put it manually via code.. — chopperfield, Nov 16 '16 at 10:52

rbr94 · Answer 1 · 2016-11-16T10:55:08.013

2

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'

edited Nov 16 '16 at 10:55

answered Nov 16 '16 at 09:31

rbr94

2,227
3
23
39

thx for reply, but when itry to put `ast_code = string.Format('\'{0}\'', a.Replace('; ','\', \''));` its give me error of `too many characters in character literal` – chopperfield Nov 16 '16 at 10:44
`('; ')` isnt it two character including the space ? – chopperfield Nov 16 '16 at 10:46
@chopperfield I edited my post. I wrongly used `'` instead of `"` because it's a `string` instead of a `char` – rbr94 Nov 16 '16 at 10:55
i think maybe should using the `list<>` for seperated them into 1 slot (or kind like of an array) the thing is i dont know how to use it when iam putting it , in the `where clause` – chopperfield Nov 17 '16 at 03:01
@chopperfield You can do this. Upvote my answer if it is useful for you – rbr94 Nov 17 '16 at 06:49
@chopperfield And my propose you should do the same as your approach if you use `string.Format("'{0}'", a.Replace("; ","', '"))` and in SQL `where code NOT IN (" + ast_code + ")` – rbr94 Nov 17 '16 at 06:55
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/128320/discussion-between-rbr94-and-chopperfield). – rbr94 Nov 17 '16 at 06:55

score 0 · Answer 2 · answered Nov 16 '16 at 09:26

0

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(";", "','") + "')";

answered Nov 16 '16 at 09:26

Wudge

357
1
6
14

its still showing the 2nd value. e.g i got two value - 001,002 then the first value 001 was not showing (was true) but the second value 002 still showing.. – chopperfield Nov 16 '16 at 10:42
1

That's because it must be `ast_code.Replace("; ", "', '")` – rbr94 Nov 16 '16 at 11:20

score 0 · Answer 3 · answered Nov 17 '16 at 03:19

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;

select query where clause with more than 1 value

3 Answers3