Many-to-Many Data Binding

Many-to-Many Data Binding

Rate This
  • Comments 39

In my Forms over Data videos series I show you how to create a one-to-many data entry form in video #3. Recently, I've had a few people ask how to create a form that displays a many-to-many relationship so I thought I'd post on how to do that today.

You can actually think of a many-to-many relationship as two one-to-many's and depending on how you are designing your UI, you may be displaying it one way or the other. Take the many-to-many relationship Orders --< OrderDetail >-- Product where an Order has many OrderDetails and Product also has many OrderDetails. Typically when users are entering the data, we're choosing to display one of the one-to-many relationships for editing. For instance we could have users enter one order at a time that displayed the Order fields as textboxes and the OrderDetails in a grid. Then we could choose Products from a dropdown list in the OrderDetails grid. (The Products are edited elsewhere on the Product Catalog form for instance.)

However when we want to display or report on data, we don't necessarily need it to be easy to enter the data, we want to be able to easily see the data instead. And depending on your UI and your target users, you still may be able to easily guide the users through editing. But what if we wanted to just get a list of all the Products on a particular Order? Or know all of the Orders for a particular Product? These are easy SQL queries but what if we want to reuse a DataSet we already have built, or we want to allow editing of the data?

Say I have a DataSet of what we're describing now: Orders --< OrderDetail >-- Product. 

 

We want a form that will allow the users to scroll through a list of orders and see all the products on that order. So we just want two grids on a form, one of Orders and one of Product. To get the filtering of rows set up properly, we will use the same exact technique for setting up automatic filtering on a One-to-Many form, but with one additional manual step which I'll show you isn't so bad. 

 

To get the form quickly designed and set up, from the Data Sources window I just drag the Orders table and it's related OrderDetails onto the Form. Make sure you select the related OrderDetail table under the Order table otherwise you won't get the automatic filtering set up on the BindingSources it creates for you.

 

 

 

Then drag the Product table onto the form and delete the OrderDetails grid becasue we don't want to display that to the user. This process sets up all the binding components in the Form's component tray properly for all three of our DataTables. It should look something like this:

Now when the position changes in the OrdersBindingSource, the OrderDetailBindingSource will filter the proper rows based on the selected Order. All that's left to do is to filter the ProductBindingSource based on this list of OrderDetails. So open up the code behind and we're going to handle the OrderDetailBindingSource.ListChanged event. In this handler we'll create the filter to apply on the ProductBindingSource based on the filtered rows in the OrderDetailsBindingSource.

Private Sub OrderDetailBindingSource_ListChanged(ByVal sender As Object, _

ByVal e As System.ComponentModel.ListChangedEventArgs) _

Handles OrderDetailBindingSource.ListChanged

 

    If Me.OrderDetailBindingSource.Count > 0 Then

        Dim filter As New System.Text.StringBuilder()

 

        For Each orderDetail As DataRowView In CType(Me.OrderDetailBindingSource.List, DataView)

 

            If filter.Length <> 0 Then

                ' Adding criteria

                filter.Append(" OR ")

            End If

            filter.Append(String.Format("ProductID = {0}", orderDetail!ProductID))

        Next

 

        Me.ProductBindingSource.Filter = filter.ToString

    Else

       ' display no rows

        Me.ProductBindingSource.Filter = "1 = 0"

    End If

End Sub

Now when we run this form, as the user scrolls through the Order rows in the first grid, the Product rows are displayed in the second grid for the entire order. To really understand what is going on just take a look at how the BindingSources are set up in the property sheet. The OrdersBindingSource has the DataSet as the Datasource and the DataMember is set to "Order". Then the OrderDetailBindingSource has its Datasource set to the OrdersBindingSource and its DataMember to "OrderDetails". This sets up the One-to-Many chaining and automatic filtering as explained in video #3 of my Forms over Data videos series.

All that's left in this case is getting the filtering on the ProductBindingSource which has the same Datasource as the OrderBindingsource, the DataSet, but its DataMember is set to "Product". We do this by simply handling the ListChanged event on the OrderDetailBindingSource. These grids are editable too, just be careful that your users understand the filtering that's being applied when working with the Product grid.

Happy Data Binding!

Leave a Comment
  • Please add 8 and 5 and type the answer here:
  • Post
  • thank you Beth

    it is working perfect

  • hi Beth! thanks for the cool article!

    i have written a code to add an remove rows from the Products table:

    when user adds new product, there two rows created: in Product table and in OrderDetail table, to link it to current selected order.

    when user deletes product, only the related row in OrderDetails deleted.

    Deletion works OK, but on adding an exception occurs: "An item with the same key has already been added".

    Here is a problem code:

    1. subscribing to a Product table event:

    dataSet.Product.RowChanged += new DataRowChangeEventHandler(Product_RowChanged);

    2. handler method:

    void Products_RowChanged(object sender, DataRowChangeEventArgs e)

    {

       if (e.Action == DataRowAction.Add)

      {

    dataSet.OrderDetails.Rows.Add(

    (int)ordersDataGridView.CurrentRow.Cells[0].Value,

    (e.Row as dataSet.ProductsRow).id);          

    }

    }

    please can you tell me what i doing wrong?

  • In my Forms over Data videos series I show you how to create a one-to-many data entry form in video #3. Recently, I've had a few people ask how to create a form that displays a many-to-many relationship so I thought I'd post on how to do that today. Yo

  • How would I accomplish this if the data in the tables were linked by GUIDs? I can get the code to work on fields where my Primary key is a numeric value or text, but I cannot get this to work with GUIDs. Is there a way achieve that?

  • Can someone help me with this

    ProductID is neither a DataColumn nor a DataRelation for table Orders.

    this message comes from ListChanged event

    plz help

  • Can you plz post a file example VB.NET project to examine it in a real test. plz do this.

  • Yep, now it works fine sorry for mistake I had posted the code in Order Binding source not in OrderDetialsBinding source.

    but now for Insertion how to catch both pks and insert them into OrderDetials table when saving data.

  • Hi Costar,

    Here are a couple links that should help you with updating master-detail forms:

    http://msdn.microsoft.com/en-us/vbasic/cc138241.aspx

    http://blogs.msdn.com/bethmassi/archive/2008/01/07/the-new-tableadaptermanager-in-visual-studio-2008.aspx

    HTH,

    -B

  • Beth, I love all your video series and was hoping this article would give me what I need.

    In my dataset, my middle table (OrderDetail) is just a cross-reference table of two ID's.  Really 1:M and 1:M, instead of your 1:M and M:1 relationships.

    As the user scrolls through the parent table of Category, I want to list all the Products.  I don't want to show the middle table ProductCategory to the user.

    When I tried your code, my form showed all Products and did not change as I scrolled through the Category grid.  Would it be a small change, or is my situation completely different than what you're doing?

  • Hi Ms. Beth! I am having an exception running the code above (Cannot perform '=' operation on System.String and System.Int32.) I think this is because my ProductID is of type varchar. Can you please help me in this matter? How can I change the code above to make the code work on my case. I'm new to VB. Thanks!

  • Oh! My question is similar with the comment of Mr. Arthur. It's now working. Thanks a lot for this post and your Video Series. It helps me a lot! God Bless.

  • Hi Beth, this example was something I've been searching for all day. When I tested this it worked great though I realized some weird behavior such as when the orderDetail table doesn't have a sorted order of productIds one product gets lost during the navigation through orders. If I navigate order records for the first time all the related products display correctly but when I continue navigating for multiple times those order records who have more than one product lose one product. I fixed this by adding an order by clause to the select statement but it still seems weird to me.

  • hello Beth

    I used that codes for many to many relations. it was working perfect but when the data getting growing Im getting error message at the line of  (Me.ProductBindingSource.Filter = filter.ToString)

    The exception code is (System.StackOverflowException was unhandled

     InnerException: )

  • Beth thank you for the videos… very well constructed and presented.

    I’m not sure if you are still monitoring the post but I did run into two issues.  I am able to create a working form for a Many-to-Many relationship per your instructions – it works beautifully when created with only datagridviews – one for each side of the relationship.  However I am unable to create a new record or save modifications to an existing one when using either of the following controls (BindingNavigatorAddNewItem_Click or BindingNavigator_ SaveItem_Click)  - respectfully, when both tables are represented as control objects on the form – Detail i.e. Combobox or Textbox. I have created the form with VB 2008 as a front-end with SQL Server 2008 as a back-end.

    Examples:

    Tables/From:

    Example  1 (this works fine)

    Employees -------------- Datagridview

    Employee_Addresses  --- Datagridview –  deleted per your instructions

    Addresses ----- Datagridview

    Example  2  (does not work. Unable to add or save data for the Addresses table.)

    Employees -------------- Detail- with combobox and textbox

    Employee_Addresses  --- Datagridview – deleted per your instructions

    Addresses ----- Details – with combobox and textbox

    Example 3 (works but visually unattractive)

    Employees -------------- Detail

    Employee_Addresses  --- Datagridview – added back to form for troubleshooting

    Addresses ----- Detail – with combobox and textbox

    If possible can you point me in the right direction?

    Thank you very much.

    Curtis

  • Thank you Beth for taking the time to give us the secrets that are hidden so well in the documentation and examples of VB.  With out your videos I would still be lost in world of trials and errors.

Page 2 of 3 (39 items) 123