Solved

Selecting Child Rows using RowFilter

Posted on 2006-07-04
6
655 Views
Last Modified: 2007-12-19
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
Comment
Question by:anthon007r
  • 3
  • 3
6 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 17035842
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
 
LVL 1

Author Comment

by:anthon007r
ID: 17035997
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
 
LVL 1

Author Comment

by:anthon007r
ID: 17036001
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 44

Accepted Solution

by:
bruintje earned 500 total points
ID: 17036047
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
 
LVL 1

Author Comment

by:anthon007r
ID: 17036918
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
 
LVL 44

Expert Comment

by:bruintje
ID: 17037152
well i'm glad it works :) thanks for the grade
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Flash (http://en.wikipedia.org/wiki/Adobe_Flash) has evolved over the years to where it has become a masterful tool for displaying content screen.  It has excellent layout placement, UI precision as well as rendering capabilities. This, along with t…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

832 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