[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 668
  • Last Modified:

Selecting Child Rows using RowFilter

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

Here is my schema:

tblUsers
 - ID PK
 - Username VARCHAR

tblRoles
 - ID PK
 - Role VARCHAR

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





0
anthon007r
Asked:
anthon007r
  • 3
  • 3
1 Solution
 
bruintjeCommented:
Hi anthon007r,
----------

you can't do it like that
http://www.developersdex.com/csharp/message.asp?p=1111&r=5100010

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

----------
bruintje
0
 
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?
0
 
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?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
bruintjeCommented:
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;
0
 
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.
0
 
bruintjeCommented:
well i'm glad it works :) thanks for the grade
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now