Question Auto-increment PK columns in master-detail form based on two Datagridviews

[afh]

I've a setup form where master as well as detail block are displayed as datagridviews. I'm getting following error message when I enter a record in master block and corresponding records in detail block and then press save button. I think this message is displayed due to negative values in primary key auto-increment column. How I may get rid of this.

You cannot add or change a record because a related record is required in table.

 

[jmcilhinney]

Here's what generally happens and where the problem generally arises. In the database, you have a master table with an auto-incremented ID and a detail table with an auto-incremented ID and a foreign key to the master ID. When these database tables get mapped to DataTables in a DataSet, the primary key columns are also set to auto-increment too. When you add rows to your DataTables, they generate temporary PK values and the temporary PKs from the master DataTable are used as FKs in the detail DataTable. When you save your changes, you must save new records for the master table first. Inserting the master records causes the final PK values to be generated in the database, which may not be the same as the temporary values in the DataTable. If you then try to save the detail records containing the temporary master IDs, it will fail because the FK constraint is violated. The solution is to retrieve the final IDs generated by the database back into your DataTable. How you do that best depends on the database.

While I was writing that previous paragraph, it occurred to me that I was assuming that the issue was arising when saving the data from the DataSet to the database, rather than when adding the data to the DataSet. If that is not the case, can you please provide more specific details about exactly what you did to generate that error message?

 

[afh]

Thanks for your reply. Your assumption is correct that I get this error upon saving. I'm actually Oracle programmer and never had to worry about such things while developing forms in Oracle. In Oracle forms, a pre-insert trigger on relevant block resolves such problems.

I will try to do some research as per your proposed solution. And if you get some time, pl post some examples relevant to my problem.

 

[jmcilhinney]

I've pretty much never used Oracle so I'm not sure how this is usually done there. The first consideration is whether your ADO.NET provider supports multiple SQL statements per command. Using SqlClient to access SQL Server, you can do an insert like this:

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

SCOPE_IDENTITY returns the last auto-generated ID for the current scope and that code will refresh the ID column of the DataRow with that value. If you have the DataRelation in your DataSet to propagate on update then that new value will be automatically pushed to the related detail DataRows before they get saved. I would guess that the Oracle ADO.NET provider would support something similar, although I'm not sure what the equivalent of SCOPE_IDENTITY would be.

If your provider doesn't support multiple SQL statements then you can use a method that will work for any provider. Here's one I prepared earlier for Access:

Retrieve Access AutoNumber Value After Insert

 

[afh]

I've tried to develop the form according you the example given by you but got same error message.
You cannot add or change a record because a related record is required in table.

Can you pl identify my mistake.

 

[afh]

Problem has been resolved. Thanks for your all your support jmcilhinney.