0

I'm having trouble with my select command. The scenario is when i'm putting many value in my variable I got nothing but if i put only one value in variable it is working.

All I want is to run this script:

SELECT * 
FROM gsm00 a 
INNER JOIN gsd00 b ON a.idnum=b.idnum WHERE b.idnum IN ('0001','0002')

Here's my sample code:

string val= "0001" - this is working

//string val= "0001,0002" - this is not working

query = "SELECT * FROM gsm00 a INNER JOIN gsd00 b ON a.idnum=b.idnum WHERE b.idnum IN (@searchKey)";

MySqlDataAdapter dataAdapter = new MySqlDataAdapter(query, dbconfig.conn);
dataAdapter.SelectCommand.Parameters.AddWithValue("@searchKey", val);
dataAdapter.Fill(dataTable);
dataAdapter.Dispose();
Farhad Zamani
  • 5,381
  • 2
  • 16
  • 41
kaloy
  • 3
  • 1

2 Answers2

0

In the first case, your @searchKey Value ist "0001" and this means your statement looks like 

SELECT * FROM gsm00 a INNER JOIN gsd00 b ON a.idnum=b.idnum WHERE b.idnum IN ('0001')

this works because it is 1 parameter and the internal paramter handling is putting single-quotes around it.

The seconds case looks like this:

SELECT * FROM gsm00 a INNER JOIN gsd00 b ON a.idnum=b.idnum WHERE b.idnum IN ('0001,0002')

but it has to look like 

SELECT * FROM gsm00 a INNER JOIN gsd00 b ON a.idnum=b.idnum WHERE b.idnum IN ('0001','0002')

Its not implicitly possible to check from your statement if its 1 paremeter or multiple parameters because the "," can be a part of your IN-Clause value.

This is a similar question with solutions, maybe you have also a look at this:

How to pass sqlparameter to IN()?

Foxenstein
  • 84
  • 5
-1

try:

query = "SELECT * FROM gsm00 a INNER JOIN gsd00 b ON a.idnum=b.idnum WHERE b.idnum IN ({@searchKey})";
...
var a = new string [] {"0001", "0002" } ;
dataAdapter.SelectCommand.AddArrayParameters("@searchKey", a );
draz
  • 793
  • 6
  • 10