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.

VB.NET:
    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
 
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.
 
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.
 
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.
 
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 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.
 
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

  • ManyToManyDemo.zip
    220.8 KB · Views: 50
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
 
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.
 
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.
 
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.
 
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.
 
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.
 
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
 
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

  • example.zip
    53 KB · Views: 37
Last edited by a moderator:
Back
Top