2

I have a dataset connection to an MS Access database.

I want to use IN operator in WHERE clause like:

WHERE DepartmentID IN (1,2,3)

This means that all record with an ID of 1, 2 and 3 will be filtered. But the problem is I cannot create a parameter like:

WHERE DepartmentID IN (?)

It only works with WHERE DepartmentID IN (?,?,?). This is okay but the parameter should not limit only to 3 parameter.

A value in IN operator could be 1 or more value.

How to do this in VB.NET?

jaypabs
  • 1,519
  • 10
  • 43
  • 62
  • http://stackoverflow.com/a/337792/284240 – Tim Schmelter Mar 11 '12 at 23:14
  • I am pretty sure this is a duplicate (but not of the one currently linked ... this is much more language-specific). In any case, the trick is to generate the `(?,...?)` dynamically based on the *count* of parameters (do not put in any data here!) and then to add the same number of parameters to the query. (This is both extensible and prevents injection attacks.) –  Mar 11 '12 at 23:20
  • [Look at this](http://stackoverflow.com/questions/337704/parameterizing-an-sql-in-clause), second answer it is really like the solution suggested by @pst. – Steve Mar 12 '12 at 00:00
  • @DanAndrews Find a duplicate SO question using it? :) Then we could close this question legitimately. –  Mar 12 '12 at 01:24
  • I don't think this is a duplicate since I am asking for ms access as the database. – jaypabs Mar 12 '12 at 23:53

1 Answers1

1

The IN operator allows you to specify multiple values in a WHERE clause.

SQL IN Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name IN (@value1,@value2,...)

You can do a loop instead but you will be prone to SQL injection

Prince Jea
  • 5,524
  • 7
  • 28
  • 46
  • 1
    -1 You **can** use the in operator to just pass one value. Apart from that you don't explain how to parametrize a `IN` clause in VB.NET. – Tim Schmelter Mar 11 '12 at 23:18
  • Why is a loop _prone to SQL-Injection_? You're open to SQL injection only if you don't use parameters and not if you're building these parameters programmatically(f.e. in a loop). – Tim Schmelter Mar 12 '12 at 10:27
  • It will be tricky..The parameter in IN clause he is asking are not fixed it could be 10 , 5 , or 2.. I my case I cannot think a way to generate parameters. – Prince Jea Mar 12 '12 at 16:30