Solved

Selecting Child Rows using RowFilter

Posted on 2006-07-04
6
662 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Reactjs with .NET 3 124
vb.net application has warrnings about VB6 calls. 2 45
Shared Service Environment 2 54
Datagridview column resizing 8 25
This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
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…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

737 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