SQL "SELECT IN (Value1, Value2...)" with passing variable of values into GridView

Hi there, I have a strange encounter when creating a GridView using "SELECT..WHERE.. IN (value1, val2...)".

in the "Configure datasource" tab, if i hard code the values "SELECT ....WHERE field1 in ('AAA', 'BBB', 'CCC'), the system works well.

However, if I define a new parameter and pass in a concatenated string of values using a variable; be it a @session, Control or querystring; e.g. "SELECT .... WHERE field1 in @SESSION" the result is always empty.

I did another experiment by reducing the parameter content to only one single value, it works well.

in short, if I hardcode a string of values, it works, if I pass a variable with single value only, it works, but if i pass a varialbe with two values; it failed.

Pls advise if I have make any mistake or it is a known bug.

BR SDIGI

From stackoverflow

• Take a look at the answer to this question (which is very similar to yours)

  http://stackoverflow.com/questions/337704/parameterizing-a-sql-in-clause

  Which ultimately links (via a convoluted route) to this definitive answer:

  http://www.sommarskog.se/arrays-in-sql.html

• If you go to using a stored procedure, you can use this method, which I discussed in regards to how to do it in SQL.

• Thanks all.

  I tried "CancelSelectOnNullParameter" to False n problem persist.

  The fact is that I created only one parameter and I am not leaving the parameter to NULL value.

  When I pass in the single value "'AAA'", it works; but when I pass in the value "'AAA', 'BBB'", it fails. and if I hardcode the values "'AAA', 'BBB'" in the SELECT statement without passing via a variable, it works well too.

  For Andrew and Mitchel's article, we will try creating a new table to store the values.

  will keep you posted on the outcome.

  BR

  Timothy Khouri : Are you setting a single variable to "'AAA','BBB'" ? because that won't work at all. You would have to be building a dynamic SQL string or use one of the suggested answers below.

• Thanks everyone who responded to help me. I appreciate :-)

  Timothy is sharp enough to spot my silly mistake! salute to you...

  Yes, once I change to dynamic SQL, it works.

  I also tried with Mitchel's suggestion, thanks.

  BR