[Last Call] Learn how to a build a cloud-first strategyRegister Now


IN clause in datatable.select

Posted on 2005-04-29
Medium Priority
Last Modified: 2010-08-05
Hi all
How to use an IN clause in Select method of DataTable.
I have a table with list of entities and parent relation as columns. I populated the rows from database to DataTable. I'm able to use query like below.

Dim Rows as DataRow()
Rows = DataTable.Select("ParentRelation = 'ID1'  OR ParentRelation = 'ID2'")

But ultimatly I want to use something like what we do in SQL query

SELECT * FROM table1 WHERE ParentRelation IN ('ID1','ID2')

Any help would be much appreciated.
Thanks in advance
Question by:mudharan

Expert Comment

ID: 13896763
You are limited by the DataTable.Select's own requirements. It expects a filter expression, which is what you did above.

My only suggestion would be to write a Helper function of some type, roughly:

Public Function IN(ByVal ColName As String, Byval Values As String()) As String
Dim sbAs New StringBuilder
Dim Terms() As String = Split(values,",")
For Each s As String in Terms
sb.Append (ColName & "=" & s & " OR ")
sb.Remove(4) ' I forget the syntax, but we are just stripping off the extra " OR " at the end
Return sb.ToString
End Function

That's off the top off my head, so sorry if any errors - but you get the idea. Then just

LVL 24

Accepted Solution

Jeff Certain earned 375 total points
ID: 13896769
1. Why don't you just populate the list from a SQL query as you mentioned, instead of doing it in your application?
2. You could probably use a dataview. As a general rule, the RowFilter property can be set to whatever SQL WHERE clause you'd like...
dim dv as dataview = new dataview(datatable)
dv.RowFilter = "ParentRelation IN ('ID1','ID2')"

Author Comment

ID: 13897055
We are already using a helper class, but would like to avoid constructing string etc...

This would be fetched dynamically everytime user selects some options. To avoid the round trips to database we are using a datatable and helper class to implement it.
LVL 24

Expert Comment

by:Jeff Certain
ID: 13897087
Sounds like the dataview approach would work just fine...
LVL 34

Expert Comment

ID: 13897134
I have used it successfully in an array of DataRows such as you are using.  The only difference between my code and yours is that I put the filter - just in the form "ParentRelation IN ('ID1','ID2')" - in a String variable. I also had a sorting clause in a String variable, and I then used  Rows = DataTable.Select(inFilter, sortExp).


Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Integration Management Part 2
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses
Course of the Month18 days, 13 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question