Updating on form with many to many relationship

Hi all,
I need to design a form that will take care of updating user details and their roles withing the software.

Things to know...
1) The data relationships...
a) tblUsers
UserID
Name
UserName
Password
b) tblRoles
RoleID
RoleName
c) tblUserRoles
UserID
RoleID
CanEdit - Boolean
CanDelete - Boolean

The relationship is 1 to many for tblUser to tblUserRoles, and 1 to many from tblRoles to tblUserRoles. Ok, so I am sure this set up looks familiar.

Anyway, I need starting off regarding the best way to build a form to handle this situation.
I figure a combobox at the top of th page to select a user, textboxes for simple databinding and maybe a datagrid to represent the roles for the user so that they show as...
RoleName - CanEdit - CanDelete
Admin -------- Yes -------- No
Orders -------- No----------No

Specifically, I assume I would use one dataset, so how many adapters would this take for poulating and for the updates What is the sequence of code execution to populate and bind accordingly after a selection from the combobox How are all the updates handled, using the adapter

I am not expecting a comprehensive answer, but I do need help starting off and pointing in the right direction and maybe through an example

Thank you for you time!

Answer this question

Updating on form with many to many relationship

Ashwin Panse - MSFT
Hi there,

First off, I'd like to suggest searching this forum a bit... there are examples of all your questions scattered throughout this site.

Here's the basic components I would recommend:

3 DataAdapters, one for each Table that:  "SELECT * FROM [TableName]"

1 DataSet loaded with each table
  - Create your Adapters at design time, right click on the first one and "Generate DataSet" and in the dialog, check the other two tables

Now in code:

call DataAdapter.Fill(DataSet) on each DataAdapter for your DataSet

add the Relationships to the DataSet

Now how you bind your controls will depend on what you want the form to do.  All controls will take your DataSet as the DataSource and then either the ParentTableName (or ParentTableName.FieldName) for parent tables, or ParentTableName.RelationshipName (or ParentTableName.RelationshipName.ChildTableFieldName) for child tables, as the DataMember.

Your form design depends on what the user will do with the data.  Also, do you intend to use tblUserRoles.CanEdit to determine if they can edit the values listed or some other part of the application

Position changes and updates will all be controled by DataBinding and the DataAdapters.  You'll call update on the appropriate adapter for the table you want to update.  If done properly, most of your binding will take care of itself (although a binding manager base and manual position handeling is sometimes necessary depending on desired functionality).

That should get you started.  Good luck!
ron nash
You build the TableStyle with a TableName of the intended table. It doesn't matter that this intended table is a child in a relationship. Even though you are binding the grid to the relationship, the TableStyle will still look at the name given to the child table and if they match the style will be used.
UstesG
Gr8, thanks for that!
So, I would like to have in the datagrid the RoleName, CanAdd and CanEdit.

Can I build a tablestyle that maps to a relationship rather than a single table so I can show the RoleName rather than the RoleID If so, what steps do I need to take

(I feel like I have to build a table in code that adds the columns RoleName, CanAdd, CanEdit and then just give the datagrid a datasource to that table in the dataset This could of course be wrong because the updating method woould be thrown off)

Hmmm, I keep hitting this wall so any other help would be great.
Thanks again.
Updating on form with many to many relationship

Answer this question