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();
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: