WHERE IN() with parameters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

Just for fun, I was investigating wheither I could use OleDbParameters in
queries with WHERE IN() clauses.

Given the code:
....
cmd.CommandText = "SELECT title, txt FROM tblFoo WHERE bar IN(?)";
int ids[] = {1, ... };
for(int i=0; i<ids.Length; i++)
cmd.Parameters.Add().Value = ids[ i ];
....

After some hours with head scratching and banging I found that for every
record I needed to get I had to add a new question-mark to the IN() clause.
Now, this isn't a lot better than the good old string-concatenation i
usually use
since i have to figure out how many question-marks are needed in the clause.

So is there a more elegant way to use OleDbParameters with IN clauses?
Obviously passing an array to the parameter won't work, and a single
question-mark will only use the first parameter.
 
Hi,



WHERE ( ',' + ? + ',' ) LIKE ('%,'+ bar + ',%')


is a possibility if you supply, for parameter, a list with a coma as
delimiter (no space), a little bit like: 4,567,8,9


I would only use that for small list, and small table. Use an inner join
(with a temp table getting the values, without duplicated entities) for
large table, since index can be used on inner join, but not with IN list.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top