Solved

Selecting Child Rows using RowFilter

Posted on 2006-07-04
6
656 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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

The object model of .Net can be overwhelming at times – so overwhelming that quite trivial tasks often take hours of research. In this case, the task at hand was to populate the datagrid from SQL Server database in Visual Studio 2008 Windows applica…
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…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

792 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