How to save new Auto Increment Id in Parent Child tables - eg Orders and Orderlines

[Anthonynz]

Greetings

I am trying to create a quoting database, using Visual Studio 2012, vb.net and sql express tables.
It is the same as the OrderHeader Orderline concept where the user will enter a new order and orderlines, and then save or cancel.
If the user saves the quote I want to update a Quoteheader table with Header details including an ID, and a Quotelines table with the QuoteHeader Id and line details.

At the moment I am opening a Dataset, and connecting a QuoteHeaderTableAdapter, and QuotelinesTableAdapter. I am able to click on an existing QuoteHeader and show the relevant QuoteLines in a bound dataviewgrid. I can then click on a Quoteline and show a new form with relevant QuoteLine details.

My problem is how to save the related data when creating New Quotes or Quotelines.
Currently the user may create a new quote - and when they click save I set a QuoteId and loop through the Quotelines datatable to assign that Id to each line
For Each dr As DataRow In dTable.Rows
dr.Item("QuoteId") = scrQuoteId.Text ' Populate
Next
Me.QuoteHeaderBindingSource.EndEdit() ' Keep Changes
Me.QuoteLinesBindingSource.EndEdit() ' Keep Changes
QuoteHeaderTableAdapter.Update(DataSet1.QuoteHeader)
QuoteLinesTableAdapter.Update(DataSet1.QuoteLines)

I realise that I should be able to Auto Increment the QuoteId by defining it in the database as ID INT IDENTITY PRIMARY KEY
But then how does this get saved on the Quotelines table?

Googling has lead me to topics such as Parent Child Relationships and TableAdapterManager.UpdateAll but I do not understand how to use them. And I would like to understand the best (and easiest) way to do this type of thing for the next part of my project, ... entering the QuoteLine detail as a third level.

A quoteline can be one of 5 types, that require different data. I have created a table and a windows form for each type. Each table will have a QuoteId and I am thinking that each table should have an Auto Increment Id

I want the user to be able to enter these different types of Quotelines (on different forms) and click save, which will add a new quoteline to the bound QuoteLinedatagridview. When they have finished entering lines they can click Save or Cancel. Only save will update the datatables.

So - to summarise - The various QuoteLineType tables will have a QuoteId and a AutoIncrement ID
The QuoteLine Table will have a QuoteId and a QuoteLineTypeId
The QuoteHeaderTable will have a QuoteId

But none of the New Auto Increment Ids are created until the data is saved, so how do they get placed in the related tables?
(Note: Sometimes entry of a Quotetype can add two lines to the orderlines table - so two lines can link to one Quotetype)

Thanks for reading - I hope I was clear.

[Leary222]

Identity is set at a database level so within vb you wouldn't do this.

Personally I prefer to not use binding sources and table adapters etc as you lose some control over your data.

However if you go to your dataset and add the relationship into the data set then when adding your data through the data sources panel you will notice that you can also add child tables and fields this is how you would keep the relationship for new data rows

[jmcilhinney]

I haven't read post #1 in its entirety but I think I've got the gist and will provide a generic answer to what is a common question. It works like this. In the database, you create your foreign key between your parent table and child table and configure it to cascade on update. What that means is that if the value in the parent column changes, that will be propagated automatically to the child column of any related records. That doesn't actually help you in the database itself in most cases, because you shouldn't be modifying the primary key value anyway, but the Data Source wizard will pick up that configuration and do the same for your DataSet, which does help you.

When the wizard generates the INSERT statement for the parent table, it will end up looking something like this:

INSERT INTO MyTable (Column1, Column2)
VALUES (@Column1, @Column2);
SELECT ID = SCOPE_IDENTITY()

What that does is insert the new record and then retrieve the last generated identity value back into the ID column of the DataRow. Retrieving that value updates the parent column of the DataRelation and, courtesy of the cascade on update, that value gets propagated to the child column of any related records. When you then save the child data to the database, the correct foreign key values will be inserted.

If you configure the foreign key correctly in the database then you should find that everything else happens automatically. You can modify the InsertCommand of your table adapter and your DataRelation manually in the DataSet designer if you need or want to though.

[Anthonynz]

Thanks Guys - I will modify my Orderlines table to CREATE TABLE QuoteLines (QuoteHID INT REFERENCES QuoteHeader(QuoteID) ON DELETE CASCADE ON UPDATE CASCADE, etc and give this a try.

This sounds like it will suit the QuoteHeader/QuoteLines tables, but I suspect that I cannot employ Auto Increment for the details behind the Quotelines.
Reason is the Quoteline can be for one of five types - for example a room.
The user will click to add a new room quoteline which displays a popup form for them to enter the rooms details - which get saved in the Rooms Bindingsource, but not saved to the database until the user Saves the whole Quote....this much Auto Increment could maybe handle?
But I also need to give the user the ability to copy and paste QuoteLines, so that a quote for several rooms can be created quickly, with the user modifying the details of the copied rooms if necessary.

So being able to have a new quote with new quote lines....and each line potentially linked to new rooms - without a unique identifier created until all data is saved together - is doing my head in - I just cant think of a good way to handle it???

I suspect that I am going to have to create some unique identifier on the room - so that when the user copies the quote line I create a copy of the room also with a new identifier - or something similar - any better suggestions?

[jmcilhinney]

So being able to have a new quote with new quote lines....and each line potentially linked to new rooms - without a unique identifier created until all data is saved together - is doing my head in - I just cant think of a good way to handle it???

You already know how to handle because I just told you. It doesn't matter how many tables you have or how they are related, every relation involves a parent and a child. All you have to do is configure the relation to cascade on update and save the parent first. That's it. All you have is three tables and therefore two relations. One relation has QuoteHeader as the parent and QuoteLine as the child while the other has Room as the parent and QuoteLine as the child. All you have to do is make sure to save the QuoteHeader and Room records first, before the QuoteLine records. Both parent tables will generate IDs and propagate them to the child table before it's saved. You could have a million tables and it would still work exactly the same way. The only requirement is that the whole set of tables are hierarchical, i.e. there are no circular relationships.

[Anthonynz]

Thanks for your input JM and Leary222
The concept that I am struggling with is ....
The user creates a new quoteline by entering the details of a room (for example) and then wishes to edit it again (before it has been saved to the database)
Or wishes to copy and paste that line, duplicating it - requiring a copy of the line details (room)
How can I know how the Quoteline and room are linked when the ID has not yet been created? - or is it perhaps not necessary to know?
Or are these two issues irreconcilable and require another approach.
Thanks in advance for your advice.

[jmcilhinney]

Thanks for your input JM and Leary222
The concept that I am struggling with is ....
The user creates a new quoteline by entering the details of a room (for example) and then wishes to edit it again (before it has been saved to the database)
Or wishes to copy and paste that line, duplicating it - requiring a copy of the line details (room)
How can I know how the Quoteline and room are linked when the ID has not yet been created? - or is it perhaps not necessary to know?
Or are these two issues irreconcilable and require another approach.
Thanks in advance for your advice.

The DataTables generate temporary IDs so there's no issue with related records before saving. When you insert the parent record into the database, the temporary ID is replaced with the final ID from the database and propagated to the child. Put simply, everything just works.