Save form data in two tables

dsk96m

Well-known member
Joined
Jan 11, 2013
Messages
173
Programming Experience
1-3
So I need to update two tables from one form. The first table is the main table, the second has the id of the main table in it.

Table One

flightcard_id
name
revision
runtime
et

Table Two
system_setup_id
flightcard_id - the id from the first table
position
tilt
etc

Table two i have in details view, not DGV. They are combo boxes for a selection.

So when I create a new record, i need to save both tables. How can I do this, i tried several different things.

Code:
    Private Sub FlightcardBindingNavigatorSaveItem_Click(sender As System.Object, e As System.EventArgs) Handles FlightcardBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.System_setupBindingSource.EndEdit()
        Me.FlightcardBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.FlightTestApp2DataSet)

        System_setupTableAdapter.Insert(Me.ComboBox1.SelectedValue, Me.ComboBox2.SelectedValue, vbNull, vbNull, vbNull, vbNull, vbNull, vbNull, vbNull, vbNull, vbNull, vbNull, vbNull, DirectCast(DirectCast(Me.FlightcardBindingSource.Current, System.Data.DataRowView).Row, Flight_Test_App.FlightTestApp2DataSet.flightcardRow).flightcard_id)

    End Sub
Please help, i am stuck
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,148
Location
Sydney, Australia
Programming Experience
10+
What UI you use to display and edit the data is not relevant in this case. All that matters is that you have the data in the appropriate DataTables in the DataSet and that there's a DataRelation between them. Calling UpdateAll on the TableAdapterManager should be all you need. You should get rid of that last line. If the UpdateAll is not doing what you expect then please describe exactly what it is doing.
 

dsk96m

Well-known member
Joined
Jan 11, 2013
Messages
173
Programming Experience
1-3
i am using a windows forms application. I have added the details of two different tables to the form. I tried what you said, it didnt save the second table. It just saves the first. The datarelation exists.

On a side not, were you able to look at that other issue i had? The one you were going to put something together for. We were emailing about it.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,148
Location
Sydney, Australia
Programming Experience
10+
i am using a windows forms application. I have added the details of two different tables to the form. I tried what you said, it didnt save the second table. It just saves the first. The datarelation exists.
If you call UpdateAll then it should do just that. As a test, I suggest that you call GetChanges on each DataTable and then get the Count of each to see how many rows there are that need saving. You can then test the value returned UpdateAll to see how many actually were saved and if they match.
On a side not, were you able to look at that other issue i had? The one you were going to put something together for. We were emailing about it.
My machine's at home have all got SQL Server 2012 Express on them, which is not compatible with VS 2010, so I haven't been able to create a demo at home. I haven't had much chance at work but I did start doing it on one occasion but the something went wrong with the project and I had to trash it unfortunately. I have a day off tomorrow so I'll remote into my work machine and create a demo there. Sorry that it's taken this long.
 

dsk96m

Well-known member
Joined
Jan 11, 2013
Messages
173
Programming Experience
1-3
It is ok, just wasn't sure if you forgot or not, lol. SQL 2012 isnt compatible with vs 2010, really, that is a big problem, lol
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,148
Location
Sydney, Australia
Programming Experience
10+
It is ok, just wasn't sure if you forgot or not, lol. SQL 2012 isnt compatible with vs 2010, really, that is a big problem, lol
It's not that SQL Server 2012 isn't compatible with VS 2010. A VS 2010 project can connect to a SQL Server 2012 database without a problem. The issue is that VS 2010 can't actually create an MDF data file and add it to the project using SQL Server 2012 Express. In that capacity, VS 2010 only supports up to SQL Server 2008 R2 Express, with VS 2012 required to create a SQL Server 2012 Express MDF data file.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,148
Location
Sydney, Australia
Programming Experience
10+
I don't recall which was the original thread so I'm just going to post this here. Please find attached a demo of saving data in a many:many relationship built with VS 2010 and SQL Server 2008 R2 Express.
 

Attachments

dsk96m

Well-known member
Joined
Jan 11, 2013
Messages
173
Programming Experience
1-3
Thank you, but this shows it will two DGVs. I think I understand how to do it that way, but how do I do it when both are details
 

dsk96m

Well-known member
Joined
Jan 11, 2013
Messages
173
Programming Experience
1-3
does this work? i can't get it to work, is there suppose to be data in the tables? what is the add edit dialoque

getting error on save
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_MainReference_Main". The conflict occurred in database "ManyToMany", table "dbo.Main", column 'MainId'.
The statement has been terminated.
 

dsk96m

Well-known member
Joined
Jan 11, 2013
Messages
173
Programming Experience
1-3
i tried the get changes, the main table has a changed row, but the secondary table does not

Do i pick the field from the table that shows under the main table or from the second table itself?

it is like it isnt even attempting to create the row in the child table. Not sure i am binding it correctly.

Maybe if I create an example, I can show you what I mean. I work on it and get your what I have.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,148
Location
Sydney, Australia
Programming Experience
10+
Thank you, but this shows it will two DGVs. I think I understand how to do it that way, but how do I do it when both are details
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.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,148
Location
Sydney, Australia
Programming Experience
10+
does this work? i can't get it to work, is there suppose to be data in the tables? what is the add edit dialoque

getting error on save
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_MainReference_Main". The conflict occurred in database "ManyToMany", table "dbo.Main", column 'MainId'.
The statement has been terminated.
It works perfectly for me. If you've looked at the database and the code then you will have seen that there are three tables: Main, Reference and MainReference. The Main table contains no data while the Reference table contains five records. The MainReference table provides a many:many relationship between the other two. In the application, the Main records are displayed in the upper grid and the Reference records related to the currently selected Main record are displayed in the lower grid. The AddEditDialogue is for adding new or editing existing Main records. You click the Add button on the tool bar to add a new Main record and you click the Delete button to delete the currently selected Main record. You double-click on the row header for a Main record to edit that record. In the AddEditDialogue, you can set the MainName of the Main record and select which Reference records it is related to. When editing, the current name and references are displayed by default. You click the Save button on the tool bar to save all your changes to the database.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,148
Location
Sydney, Australia
Programming Experience
10+
does this work? i can't get it to work, is there suppose to be data in the tables? what is the add edit dialoque

getting error on save
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_MainReference_Main". The conflict occurred in database "ManyToMany", table "dbo.Main", column 'MainId'.
The statement has been terminated.
Something's wrong there. That sounds like the ID generated by the database when you insert a new Main record is not being propagated back to the MainDataTable in the DataSet. Try this:

1. Start with a fresh database. To do that, select the data file in the Solution Explorer and open the Properties window, then set the Copy To Output Directory property to Always Copy. Rebuild the project to copy the source database over the working database, then set the property back to Copy If Newer.
2. Run the project.
3. Click the Add button on the tool bar.
4. In the Add/Edit Dialogue, set the Name to First but do not select any Referencees, then click OK.
5. Repeat steps 3 and 4 to add another Main record with the Name set to Second.

At this point, the records should be showing -1 and -2 in the MainId column.

6. Click the Save button.

That last step should save the two new records to the database. The database should generate final values for the MainId column and those values should be propagated back to the DataTable, which means that you should see the -1 and -2 replaced with 1 and 2. If you don't then that is where the problem is. Close the application and run it again and see if the values in the MainId column are what's expected when retrieved directly from the database.
 

dsk96m

Well-known member
Joined
Jan 11, 2013
Messages
173
Programming Experience
1-3
Question, when I create a new record by clicking the + button, do I need to add code to add a new record for the second source. I know the first source is whatever the first control you move over onto the form, but what about the second table
 

dsk96m

Well-known member
Joined
Jan 11, 2013
Messages
173
Programming Experience
1-3
Ok, so I put this simple program together. I am not as versed as you with adding in the db, but I added the sql file for creating the db in sql server. You will need to fix the connection string in settings. db is called example. I am using VS 2010.

There is Form 2 and Form 3. Form 2 shows how it works, where as form 3 shows how I need it to work. I can't figure it out. Can you take a look, what am i doing wrong
 

Attachments

Last edited by a moderator:

dsk96m

Well-known member
Joined
Jan 11, 2013
Messages
173
Programming Experience
1-3
jmcilhinney - Have you been able to look at the solution i attached and see what I am trying to do?
 

Paul P

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

Did you ever sort out this problem? I am experiencing the same behaviour.

- Two tables in SQLServer. Parent/Child. Relation and Foreign Key Constraint in place. Update and Delete Rule = Cascade.
- Trying to add new record to Parent table (Contacts) and related record to Child table (Locations).
- 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 = -1.

I agree with the comments above that the GUI should be irrelevant, but it keeps happening to me.

Appreciate your feedback if you ever sorted it out. Otherwise I may create a new thread.

Cheers - Paul
 

Paul P

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

This is the only code in the small test project - works with DGV, not with Details view.



Public Class Form1


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

'TODO: This line of code loads data into the 'DataSet1.Contacts' table. You can move, or remove it, as needed.
Me.ContactsTableAdapter.Fill(Me.DataSet1.Contacts)
Me.LocationsTableAdapter.Fill(Me.DataSet1.Locations) ' CHANGED THE ORDER OF THESE SO PARENT LOADS FIRST...

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() ' ADDED EndEdit() HERE TO THE CHILD BINDING SOURCE TOO ...

Me.TableAdapterManager.UpdateAll(Me.DataSet1)


End Sub

End Class
 
Last edited:
Top Bottom