Selecting Child Rows using RowFilter

Hi, im having problems regarding how to filter child rows given a certain condition.

Here is my schema:

 - ID PK
 - Username VARCHAR

 - ID PK

 - ID PK
 - UserID FK -> tblUsers.ID
 - RoleID FK -> tblRoles.ID

Now, lets say we populate our dataset based on my given schema. My problem is I want to filter out rows using RowFilter. What I did is something like this:

daMyDataAdapter.Fill( myDataSet );

DataView dv = new DataView();
dv.table = myDataSet.tblUsers;
dv.RowFilter = "Child(tblUser2Role).RoleID = " + RoleID.toString();

Writing this, I getting the error "Cannot interpret token 'Child'".

Is there anyway I could do the same thing by using filters other than this? I dont want to fill again using dataAdaptor since it will be inefficient since the rows i want already exist in dataset, all I want is filtering it out.

Many thanks.

Who is Participating?

Improve company productivity with a Business Account.Sign Up

bruintjeConnect With a Mentor Commented:
i have no way to test this but i tried to rewrite the code from that thread using your tables

            DataRow[] drs = myDataSet.tblUsers2.Select("RoleID=" + RoleID.toString());
            string rowFilter = "ID in (";
            foreach (DataRow dr in drs)
                rowFilter += dr["RoleID"].ToString() + ",";
            //Note, the extra comma at the end doesn't matter
            rowFilter += ")";
            dv1 = new DataView(tblUsers, rowFilter, "ID", DataViewRowState.CurrentRows);
            dataGridView1.DataSource = dv1;
Hi anthon007r,

you can't do it like that

there are some alternatives offered there and i think for your situation the in (RoleID) syntax could be helpfull

anthon007rAuthor Commented:
Thanks bruintje for a swift reply, but how could I possibly filter out users who belong to a certain role using the "in" keyword in RowFilter?
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

anthon007rAuthor Commented:
I mean, tblUsers is a parent table, there's no roleID that could be referenced (using 'in') other than its child table which is tblUser2Role. Am I missing something?
anthon007rAuthor Commented:
Wow, great, got it! I wonder how or why did I missed the thought behind the "in".

Many thanks to you buintje :)
Im tired of googling this thing glad found it.
well i'm glad it works :) thanks for the grade
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.