Question How to add Parent and Child record in Details View ??

Paul P

Member
Joined
Apr 26, 2015
Messages
5
Location
Gold Coast, Australia
Programming Experience
3-5
Hi

I greatly appreciate some help, I'm sure this should be simple!

I'm trying to make a simple data entry form to create a new Contact (parent) and one new Location (child).

The design is:

- Tables are in SQLServer. Contacts table (parent) and Locations table (child)
- Relation and Foreign Key Constraint in place at database and dataset level.
- Update and Delete Rule = Cascade.
- Using Table AdapterManager.UpdateAll

When I do this using a simple form with 2 x DataGridViews it works as expected. It backfills the Parent primary key to the Child etc. All fine!

If I use exactly the same project, but swap out the DataGridViews for a 'Details' view, created by dragging from the DataSources, it does not work.

- When I 'Add New' it clears all the text boxes in Parent and Child fields, and inserts temporary Primary Key in both = -1.
- When I 'Save', it saves the Parent Record successfully, but does not save the Child. It just clears all the child fields including the temporary primary key

I have broken the code down and the problem is that when the .endedit is called on the parent table -> it clears the Locations (child) details.

I have trawled the net and found lots of threads that describe exactly this problem going back many years. But, I haven't found an explanation on how to get it to work yet.

I am sure this will turn out to be straight forward!

Really appreciate some help.

Cheers

PP




Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

Me.ContactsTableAdapter.Fill(Me.DataSet1.Contacts)
Me.LocationsTableAdapter.Fill(Me.DataSet1.Location  s)

 End Sub
 
  

Private Sub AddNewButton_Click(sender As Object, e As EventArgs) Handles AddNewButton.Click

Me.ContactsBindingSource.AddNew()
Me.LocationsBindingSource.AddNew()

 End Sub
 
  

Private Sub ContactsBindingNavigatorSaveItem_Click_1(sender As Object, e As EventArgs) Handles ContactsBindingNavigatorSaveItem.Click

Me.Validate()
Me.ContactsBindingSource.EndEdit()
Me.LocationsBindingSource.EndEdit() 

Me.TableAdapterManager.UpdateAll(Me.DataSet1)


 End Sub

 
 
Last edited by a moderator:

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,148
Location
Sydney, Australia
Programming Experience
10+
Have you examined the contents of the two DataTables immediately before calling UpdateAll? Debugging is a critical part of development and it involves more than just reading the code. You need to watch it in action and, at critical points, determine what the expected state is and test whether the actual state matches.
 

Paul P

Member
Joined
Apr 26, 2015
Messages
5
Location
Gold Coast, Australia
Programming Experience
3-5
Thanks

I appreciate your advice.

If I break immediately prior to the tablemanager.updateall the row count for the parent table has increased by one, as expected.

The row count for the child table does not increase after 'AddNew' is called. It is just the same as in the underlying SQLServer table. (ie. no row added to the Locations (child) table.)

Looking at the trace in SQLServer profiler reveals the same. After the tablemanager.updateall, it shows one INSERT SQL command for the Contacts (parent) table. There is no SQL command sent by the tableadaptermanager for the Locations (child) table.

I know the tableadaptermanager and the underlying Locations INSERT statement are ok, because as I mentioned above, the same project all works perfectly if using a DataGridView - just not when in details view. I've also looked them over to check them.

This is also reflected in the GUI when the program runs:

- Both the Contacts and Locations populate with new/empty fields, except for the PrimaryKey (eg. -1)
- After the ParentBindingSource.endedit all the details in the Child's text boxes, including the Child Primary Key clear.

So, the Row Count and SQL Profiler Trace support what I am seeing in the GUI.

I really don't understand why the GUI should affect these DataBinding components?

That's actually a similar comment to what you have explained previously...

http://www.vbdotnetforums.com/ado-net/57591-save-form-data-two-tables.html


That's completely irrelevant. It's the DataTables that matter. How the data in those tables is displayed and edited is completely irrelevant. That said, you should always be binding the data via a BindingSource and calling EndEdit on each BindingSource before saving. That ensures that any edits in progress will be committed to the DataTable before saving.

Thanks for your help.

Cheers

Paul
 

Paul P

Member
Joined
Apr 26, 2015
Messages
5
Location
Gold Coast, Australia
Programming Experience
3-5
Hi

I guess what is happening is:

- The textboxes on the GUI are linked to the ContactsBindingSource and LocationsBindingSource.

- The ContactsBindingSource has the DataSource as the Dataset, and the DataMebmber as the ContactsDataTable.

- The LocationsBindingSource has the DataSource as ContactsBindingSource, and the DataMember as the FK_Locations_Contacts.

When ContactsBindingSoource.endedit is called it flushes all the contact details from the textboxes on the GUI to the underlying Contacts DataTable.

For some reason at that point if also clears all the values in the Location Textboxes.

So, when LocationsBindingSource.endedit is called it there is nothing to push through to the Locations DataTable.

But, if the GUI has a parent/child DataGridViews, also dragged from the designer, instead of 'Details View' it works fine.

Cheers...
 
Top Bottom