IN clause in

Posted on 2005-04-29
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
    LVL 8

    Expert Comment

    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

    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

    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
    Sounds like the dataview approach would work just fine...
    LVL 34

    Expert Comment

    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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (…
    If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
    This video discusses moving either the default database or any database to a new volume.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    732 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now