Binding to multiple related tables

[JCampbell84]

I am having some trouble binding some related tables.

 

I have three tables which are related. users, locations, and privileges. My dataset dsAdmin contains these three tables.

 

User will have different "permissions" (plevel)for each location, which is defined in the privileges table.

 

http://i56.tinypic.com/solb95.png

 

I have a form with a listbox to display a list of users. A Combo Box to display a list of locations. And a Text box that displays the user's privilege for that location.

 

http://i53.tinypic.com/ossh11.jpg

 

Is there a way to bind these together using a data adapter? This is what I have so far:

 

       private void SetupPrivilegeBindings()
       {

           BindingSource bsUser = new BindingSource(dsAdmin, "users");
           BindingSource bsLoc = new BindingSource(dsAdmin, "locations");

           listUserP.DisplayMember = "username";
           listUserP.ValueMember = "user_id";
           listUserP.DataSource = bsUser;

           cbLocationP.DisplayMember = "location_name";
           cbLocationP.ValueMember = "location_id";
           cbLocationP.DataSource = bsLoc;

           daPrivileges.SelectCommand = new MySqlCommand("SELECT plevel_id, user_id, location_id, plevel FROM privileges", db.sqlCon);

           daPrivileges.Fill(dsAdmin, "privileges");

           dsAdmin.Relations.Add("userpriv", dsAdmin.Tables["users"].Columns["user_id"], dsAdmin.Tables["privileges"].Columns["user_id"]);
           dsAdmin.Relations.Add("locpriv", dsAdmin.Tables["locations"].Columns["location_id"], dsAdmin.Tables["privileges"].Columns["location_id"]);

           txtPlevel.DataBindings.Add("Text", dsAdmin.Tables["privileges"], "plevel");

         
       }

 

I would like for the user to be able to select a username, select a location, then have the text box display the plevel for that combination. There will also be instances where there will be no rows for that specified combination, in which I would need to INSERT a new privilege row.

 

I have a feeling a data adapter will not be possible to use for what I need. If someone could point me in the right direction, or can think of a better way to store the user's permissions that would be great!

 

Thank you for your time!