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
– NSGaga, Commented Apr 6, 2012 at 12:41
I am connecting c# app with access databse,no I am not using LINQ. — NIlesh Lanke
– NIlesh Lanke, Commented Apr 6, 2012 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
– Lasse V. Karlsen, Commented Apr 6, 2012 at 12:49
What do you want to get? The list of CDGroupID that have 'f1','f2' and 'f3'? — Devart
– Devart, Commented Apr 6, 2012 at 12:59

Community · Accepted Answer · 2017-05-23 10:08:46Z

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, 2017 at 10:08

CommunityBot

11 silver badge

answered Apr 6, 2012 at 12:57

Christian Specht

36.6k16 gold badges132 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Raphaël Althaus · Accepted Answer · 2012-04-06 12:55:51Z

0

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

then

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

edited Apr 6, 2012 at 12:55

answered Apr 6, 2012 at 12:50

Raphaël Althaus

60.6k6 gold badges106 silver badges127 bronze badges

Comments

NIlesh Lanke · Accepted Answer · 2012-04-06 13:12:58Z

0

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";

answered Apr 6, 2012 at 13:12

NIlesh Lanke

1,2639 gold badges27 silver badges37 bronze badges

Comments

Devart · Accepted Answer · 2012-04-06 14:23:19Z

0

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'.

answered Apr 6, 2012 at 14:23

Devart

120k23 gold badges172 silver badges189 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related