Passing comma delimited list as parameter to IN clause for db2 query using designer in visual studio 2008

Question

I want to pass a comma delemited list of values as a parameter to a query I'm building using the designer in Visual Studio 2008 based on some strongly typed DAL tutorials I was going through. The query is going against a DB2 database. Here's what I want to do:

select * from prices where customer in(?)

It works fine win I pass in 123456 as ?

But fails when I pass in '123456' (it is a char field so I don't know why this doesn't work; it must be adding these behind the scenes) or 123456, 123457 or '123456', '123457'

I'm adding this page to a portal where all the data access is being done based on the DAL designer model with a BLL that calls it so I wanted to do it this way for consistency. Is this possible or is this a situation where the tool just isn't flexible enough to accomplish what I need it to do? Thanks.

Answer 1

This is a very common mistake people make with parameterized queries. You have to remember that a single parameter placeholder "?" is a substitute for a single value.

See the question link below for a clever solution for this problem from Joel Spolsky.

"Parameterizing a SQL IN clause?"

Also a bunch of other people answered the same question, reiterating that the standard solution is to construct the SQL query dynamically, appending a parameter placeholder for each value you need to pass.