Dynamically add parameter to a 'IN' clause in a query?

Question

I am attempting to query an MS Access DB with C#

Static query is -

Select FieldID , CDGroups.CDGroupID , Priority
from Fields , CDGroups
where Fields.CDGroupID = CDGroups.CDGroupID
  and Fields.FieldID in ('f1','f2','f3')
order by Priority

I need to replace f1,f2.. FieldID from fieldIdList(List<string> fieldIdList) which contains all these fields

How can I go about it?

what's the language you're programming in, and entity framework or other, are you using LINQ? — NSGaga-mostly-inactive, Apr 06 '12 at 12:41
I am connecting c# app with access databse,no I am not using LINQ. — NIlesh Lanke, Apr 06 '12 at 12:44
You still need to show some code, otherwise the best you can hope for here is "well, then do it" — Lasse V. Karlsen, Apr 06 '12 at 12:49
What do you want to get? The list of CDGroupID that have 'f1','f2' and 'f3'? — Devart, Apr 06 '12 at 12:59

score 1 · Answer 1 · edited May 23 '17 at 10:08

1

The correct way would be to use SQL parameters.
To do this, you need to loop through your list and create one parameter per list item - in the SQL string and in your query's list of SQL parameters.

Take a look at this answer to a similar question:
How to pass sqlparameter to IN()?

edited May 23 '17 at 10:08

Community

1
1

answered Apr 06 '12 at 12:57

Christian Specht

35,843
15
128
182

Raphaël Althaus · Answer 2 · 2012-04-06T12:55:51.823

0

 var listId= string.Join(",", fieldIdList.Select(m => string.Format("'{0}'", m)));;

then

"Field.FieldID in (" + listId+ ")

edited Apr 06 '12 at 12:55

answered Apr 06 '12 at 12:50

Raphaël Althaus

59,727
6
96
122

score 0 · Accepted Answer · answered Apr 06 '12 at 13:12

I used following code:

 StringBuilder sb = new StringBuilder();

                foreach (string fieldId in fieldIdList)
                {
                    sb.Append("'" + fieldId + "',");
                }

                string fieldList = sb.ToString().TrimEnd(',');

                string queryString =
                    "Select FieldID , CDGroups.CDGroupID , Priority from Fields , CDGroups where Fields.CDGroupID = CDGroups.CDGroupID and Fields.FieldID in (" + fieldList + ") order by Priority";

score 0 · Answer 4 · answered Apr 06 '12 at 14:23

Try this query -

SELECT CDGroupID FROM fields
  WHERE FieldID IN ('f1', 'f2', 'f3')
  GROUP BY CDGroupID
  HAVING(COUNT(DISTINCT FieldID)) >= 3

It will show a list of CDGroupID that have 'f1','f2' and 'f3' at least. If you want to show ones that have just 'f1','f2' and 'f3', then change WHERE condition to 'HAVING(COUNT(DISTINCT FieldID)) >= 3'.

Dynamically add parameter to a 'IN' clause in a query?

4 Answers4