Many-to-Many Data Binding
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!
Comments
Anonymous
May 31, 2007
The comment has been removedAnonymous
May 31, 2007
Hi Erik, You're correct in pointing out my example is not scalable but not because of the filter code. It's not good practice to bring down all the data in your database, that is what is not scalable. I show how to create paramererized queries in my video series and also I have a post on how to do that. Remember that when we build the filter for the Products, it's done by reading the filtered set of OrderDetails. So unless you brought down thousands of OrderDetails that were on one SINGLE Order, you'd be just fine. This would not be a scenario to worry about in my Order - OrderDetails scenario, however if you did have a scenario like this then make sure to create a parameterized query that also limits the linking table's rows. Cheers, -BAnonymous
June 03, 2007
In your "CreatingYour Database.wmv" video serries, how did you define your snippets. I recreated your sample 5 times already and I get everything correctly except the storedprocedure node, on the toolbox tab, there are no usable controls in this group. What did I miss? Can you help me on this issue? Thanks.Anonymous
June 04, 2007
Hi Edilberto, You can create code snippets by selecting text in the code editor and then dragging that onto the toolbox. Then you can use that snippet of code again by dragging from the toolbox into the editor. I wrote the stored proceedures and saved them as snippets for the video. They are contained in the database -- look in the Server Explorer under "Stored Proceedures". HTH, -BAnonymous
June 11, 2007
I followed instruction in your "Connecting To Database" video, but how come it seems like it doesnt work for me. It was all connected to database but mine won't update, insert or delete like yours? What did I miss? can you please help? Thanks.Anonymous
June 12, 2007
Hi Mikaela, Please read this post to see if it resolves your issue: http://blogs.msdn.com/bethmassi/archive/2007/05/29/working-with-a-local-data-file-in-vs.aspx Cheers, -BAnonymous
June 12, 2007
The comment has been removedAnonymous
June 12, 2007
Sorry, if I ask again. I follwed your instruction in "Creating A Search Video". My prob now is that, it won't search the Lastname that I enter. It is in the the list ofcourse, coz i added it before searching. And also, after running, it deletes some datas. Why is that? Please help...Thanks!Anonymous
June 13, 2007
Hi Mikaela, What is your search SELECT query? For help on writing SQL statements you can check out the SQL-Server Development center documentation here: http://msdn2.microsoft.com/en-us/sql/aa336367.aspx?wt.svl=1 -BAnonymous
August 15, 2007
How do I setup a one to one relationship. I am using an Access Database.Anonymous
October 25, 2007
I have taken this excellent information and tried to apply it to my situation. Instead of Orders 1:M to OrderDetails and Products 1:M to OrderDetails, I have Emps 1:M to EmpTitles (my bridge table for a M:M) and Titles 1:M to EmpTitles. So, for instance, your OrderDetailBindingSource became my EmpTitlesBindingSource. And, in the "foreach", your orderDetail became my empTitles. But when I come to append the ProductID (which is my TitleID), the 3rd parameter won't take (it is titleId in both my Titles and EmpTitles tables). I thought perhaps it was using a property, so I changed it to TitleId. In either case, it errs and tells me "the name does not exist in the current context." What in the world am I doing wrong? Could it really be this hard? I think not, but I would certainly appreciate any assistance. Thanks!Anonymous
January 13, 2008
it doesnt work if the "id" column properties is not an integerAnonymous
January 21, 2008
Arthur, You just need to construct the filter properly. If the key is a string you need quotes around the value. filter.Append(String.Format("ProductID = '{0}'", orderDetail!ProductID))Anonymous
January 24, 2008
Is it possibe to obtain that as sample project?Anonymous
January 24, 2008
is it possible to make many-to-many relation whit sql query? Are ther other ways for it?Anonymous
May 11, 2008
thank you Beth it is working perfectAnonymous
June 01, 2008
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:
- subscribing to a Product table event: dataSet.Product.RowChanged += new DataRowChangeEventHandler(Product_RowChanged);
- 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?
Anonymous
June 06, 2008
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. YoAnonymous
August 11, 2008
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?Anonymous
August 29, 2008
Can someone help me with this ProductID is neither a DataColumn nor a DataRelation for table Orders. this message comes from ListChanged event plz helpAnonymous
August 29, 2008
Can you plz post a file example VB.NET project to examine it in a real test. plz do this.Anonymous
August 30, 2008
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.Anonymous
September 02, 2008
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, -BAnonymous
September 23, 2008
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?Anonymous
September 26, 2008
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!Anonymous
September 26, 2008
The comment has been removedAnonymous
October 27, 2009
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.Anonymous
March 31, 2010
The comment has been removedAnonymous
August 25, 2010
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. CurtisAnonymous
December 06, 2010
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.Anonymous
September 26, 2011
Hi Thank you for your time, I have a question : If we have two table A and B, these two table related to parent table by column "Parent_ID", now I want to create another table that link all record in table A to each record in table B automatically based on there Parent_ID, are there way to do that, I really need your help because i'm stuck with this for over two weekAnonymous
September 28, 2011
Hi Hassan, The way you have it set up could use the Parent_ID foreign key in table A to get all the related records in table B. You should be able to do this with a filter using the parent_ID. You can also try asking in the forums: social.msdn.microsoft.com/.../threads HTH, -BethAnonymous
March 11, 2012
I want to make a listbox containing all the dates that a Patient has visited a doctor. When the user clicks a specified date from the listbox the grid containing the fields Reason for visiting,Diagnosis and Receipt from that specific date are automatically displayed. Does anyone knows how to implement this.Anonymous
October 03, 2012
Hi Beth, I don't expect you are monitoring this so long after, but I'm having a runtime problem I don't understand with this code. In "For Each orderDetail As DataRowView In CType(Me.OrderDetailBindingSource.List, DataView)" I get an error that a DataViewManager cannot be cast to a DataView. How can I control the list being the rows of the table and not the tables in the dataset as you have in your example?Anonymous
October 03, 2012
The comment has been removedAnonymous
March 04, 2013
Thanks for the great tutorials. Have you created a tutorial on how to populate the linking data table on a many-to-many relationship? Regards Gareth.Anonymous
May 16, 2013
So I got your solution working for my needs, to display the information, but how do I add and delete items?Anonymous
May 10, 2014
This was a great help. Thanks.Anonymous
September 18, 2014
How do I change the ListChanged event code if the data source tables are coming from a LINQ to SQL Class instead of a DataSet? I get an error, unable to cast the binding list to a System.Data.Dataview.Anonymous
June 02, 2016
Hi Beth,If I'm going to the 'Forms over Data Video Series' and I click on one of your videos, I will be redirected to the same url:https://www.visualstudio.com/And this happens with every video.Are those video's removed or moved?Regards,Mark