Sharing the goodness…
Beth Massi is a Senior Program Manager on the Visual Studio team at Microsoft and a community champion for business application developers. Learn more about Beth.
More videos »
Recently I've had more than a few questions about how to handle working with data across multiple forms. If you've watched my Forms over Data video series you know how to create a database, connect to it, save your data properly, and work with data in your code. In this post I'll expand upon those videos and walk through an example of how to create multiple forms that work against the same DataSet. We'll use the Northwind Categories and Products for this example.
There are actually many ways to connect multiple forms to the same DataSet depending on your scenario, but I'll take a common example of displaying editable detail forms from a main form with a grid-style edit like this:
Here we've got a form with two DataGridViews displaying Categories and their related Products. Once you connect to the Northwind Database with the Data Sources Window, you can choose Categories and Products tables and that will create a DataSet and the corresponding TableAdapters for you just like I showed in the One-to-Many video. Once your DataSet is set up then you can drag and drop the Categories and related Products tables from the Data Sources Window to your form. This will set up the two BindingSources and bind the grids properly as well as add a BindingNavigator ToolStrip to the form. Now what we want to do is create a second form that allows us to edit the product details.
First I added a couple lines of code to the TableNewRow event handler on the Categories and Products DataTables so that it would be easier working with the data. In this handler I just set some defaults for the non-nullable fields. To get to the code behind file for your DataSet just right-click on the DataSet in the Solution Explorer and select "View Code". This is where you can add simple validation as well like I showed in the video on adding validation.
Partial Class CategoriesProductsDataSet
Partial Class CategoriesDataTable
Private Sub CategoriesDataTable_TableNewRow(ByVal sender As Object, _
ByVal e As System.Data.DataTableNewRowEventArgs) _
'Set defaults for non-nullable fields
Dim category As CategoriesRow = CType(e.Row, CategoriesRow)
category.CategoryName = "New Category"
Partial Class ProductsDataTable
Private Sub ProductsDataTable_TableNewRow(ByVal sender As Object, _
ByVal e As System.Data.DataTableNewRowEventArgs) _
Dim product As ProductsRow = CType(e.Row, ProductsRow)
product.ProductName = "New Product"
product.Discontinued = False
Next I added a new form to the project and again used the Data Sources Window to drag and drop the Product fields I want to edit onto the new form. This time, however, I don't need the BindingNavigator ToolStrip so I just selected that and deleted it and instead added two buttons on the bottom of the form for OK and Cancel.
Next I deleted ProductsTableAdapter in the component tray and then opened up the code-behind of the detail form. I then deleted the Form.Load handler with the Fill code that was auto-generated for us. This is because we do not want to re-fill the DataSet from the database, instead we are going to pass the data to this form from our main form.
The easiest way to pass the data in this situation is to create a new constructor that accepts the DataSet we're editing on the main form and the primary key of the product we want to edit. Then we can set the ProductBindingSource's DataSource and Filter property on the detail form so that the it displays the selected row. This keeps the main form and the detail form edits in sync. In the details form code right under the class definition type "Sub New" and hit enter to auto-generate the correct constructor call, then change the signature and set the ProductBindingSource properties.
Sub New(ByVal ds As CategoriesProductsDataSet, ByVal id As Integer)
' This call is required by the Windows Form Designer.
' Add any initialization after the InitializeComponent() call.
' Set the DataSource of the BindingSource and then set the Filter
' so that the correct row will be displayed on the detail form.
Me.ProductsBindingSource.DataSource = ds
Me.ProductsBindingSource.Filter = "ProductID = " & id.ToString
Then back in the main form I created a button on the ToolStrip that opens this details form. I call ProductsBindingSource.EndEdit() first so that any changes made on the main form are pushed into the DataSet before we attempt to edit the row on the detail form. Then I get the current Product row by casting the ProductBindingSource.Current property to ProductsRow and pass the ProductID to the detail form's constructor along with the reference to the CategoriesProductsDataSet on the main form:
Private Sub ToolStripButton1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
If Me.ProductsBindingSource.Position > -1 Then
'Get the current product row
Dim row As CategoriesProductsDataSet.ProductsRow
row = CType(CType(Me.ProductsBindingSource.Current, _
'Open the product detail form passing the dataset and the product ID
Dim frm As New Form2(Me.CategoriesProductsDataSet, row.ProductID)
You can open the details form modal or modeless. Here I'm allowing the user to open as many detail forms as they want but if you want to only open one at a time then just change the frm.Show() line to frm.ShowDialog() instead. This example demonstrates editing the current row and does nothing if there is no product row selected. You could optionally add a call to ProductBindingSource.AddNew() before the EndEdit() call in this scenario if you need to add a row programmatically before the form is opened. (NOTE: It's important that you add defaults for non-nullable fields like I showed above in the DataTable partial classes if you call AddNew() then EndEdit() in succession.)
The last bit of code is on the detail form that calls EndEdit() or CancelEdit() on the ProductsBindingSource depending on if they clicked Cancel or OK on the detail form, or just closed the form without selecting OK. This will either accept or discard the changes made to the product row on the particular detail form.
Private Sub Form2_FormClosing(ByVal sender As Object, _
ByVal e As System.Windows.Forms.FormClosingEventArgs) _
Private Sub cmdCancel_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles cmdCancel.Click
Private Sub cmdOK_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles cmdOK.Click
I've attached a complete example to this post that works against the Northwind database. It also shows how to properly save related DataTables like I demonstrated in this video and this post. Remember the key to working with data on your forms is to use the BindingSource object as this class really makes working with data and form controls very simple. For more information see the Understanding Data video.
can someone help me out and rep me on my email firstname.lastname@example.org
Check the Position property of the BindingSource and make sure it is correct the first time.
Hi, Beth, thanks for the article! I've been searching and looking and googling and scratching my brain trying to solve a problem I'm having, and maybe you can help.
How oh how would I be able to use the same form to change data that is stored in *multiple* datagridviews in a tabbed control?
In my production environment the users all have touch screens and there are several datagridviews in a tabbed control on the main form. All of the datagridviews interact with the same database, but different tables.
I made a nifty little numberpad for them to enter the data, but I don't know how to make it remember from which datagridview cellclick event it is being called.
Maybe I'm just approaching the whole thing wrong! Can you help me or point me in the right direction. I know I could make six different numberpads, but I also figure there's a much more elegant solution.
I came across this article on binding which I found very informative. However what I want to do is to have the Product Detail (Form2) change the entry when the product changes in the main form (Form1). Have you written anything that sheds light on how to do that?
Can anyone please help me. Using Article Data across multiple form. I want to add new complainant and pass complainant_ID to a Registration form and add an Offender to that registration Form. when done, we have on Registration, Registration_ID, Complainant_ID, Offender_ID .
How can i create a new customer and push the customerID into the product table
You could do this a couple ways. One way would be to share the ProductBindingSource across the forms, or handle the PositionChanged of the main form's bindingsource. But a cleaner way in this example would be to just raise an event from the main form that passed the ProductID as the event argument. Then the detail form could just handle the event and set the Filter again.
I'm not sure what you mean, there is no customerID in the example above. But you can create a new product by clicking the New button in the example. If you want to add a new Category then you sould just call AddNew on the CategoryBindingSource.
Here's a couple videos that may help you understand these data objects better:
Thanks for reply..i meant categoryid.
category is the parent table and product details is the child.. when i go add, new category creates and i want to create a new product for this new category at the same time.
I have posted full explaination for what I want to accomplish
How can I use this example using two parent tables feeding into one child table..
for example, Complainant table and Offender table as parent tables and create a registration as a child table.
Can anyone help me.. if I add an entry on a parent table. can the PK_ID parse to a child table as FK on the child table simultaneously..If that's so how..
what i want to do is that I have two parent tables, i want to create a registration on those two PK key on the registration table.
Guide me if possible..
If you have your BindingSources set up properly then when you add a new child it will automatically add the FK for you. In the example above if you want to add a new product "at the same time" when a new category is added then just call:
Note that a category must exist before a product can be added and that the above code assumes the data rows are valid before calling EndEdit.
You may want to ask your question in the forums, there are a lot more people there including MSFT tech support that can help. http://social.msdn.microsoft.com/Forums/en-US/category/visualbasic
Please also read up on the BindingSource as well as how datasets work. You'll need to configure them correctly in order for them to save hierarchical data like I showed in this video:
I really love your How-Do-I videos.
I came from a VB6 environment and now we are building our apps in C# .NET (just recently and it was a business justification to use C# over VB.NET).
thanks for your help and examples.
Dennis Smits (The Netherlands)
I use this technique to parse the values from formA to load another formB...These two forms are from the same table. same dataset.
When save formA, it saves correctly, when click OK on formB which is a save function, nothing happens on the database, but I click save on formA, whatever I update on formB save on the database..
i need when I click on OK on formB, to save/update the database not when go back to FormA for it to save..
Can you guide me on what to do