vb.netdatagridscope-identity

How to get the Scope_identity value for the parent record using VS19 with bound datasets

I have two tables in MSSQL which are related tbl1.PK tbl2.PK, tbl2FK(_tbl1PK)

I have created a dataset in Visual Studio and they show up with the relation when I drop the tables as datagrids onto a form I get the parentDG and childDG. VS adds in the binding navigator which adds the save procedure

tbl1.bindingsource.endedit
tableadaptermanager.updateall

When I add data to the parentDG and press save the data gets saved to the DB. When I add a new parent and then add a new child details and press save I get

System.Data.SqlClient.SqlException: 'The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblChild_tblParent". The conflict occurred in database "xxxxx", table "dbo.tblParent", column 'PK'.

I have tried updating the parentTableadapter and then ds.acceptchanges but I can not get the new child row to update.

I understand why but my questions are:

I can see in the dataset code the insert command:

Me._adapter.InsertCommand.CommandText = "INSERT INTO [] FROM tbl1 WHERE (PK= SCOPE_IDENTITY())"

BUT I cannot for the life of me see how to get this value.
I have a lot of tables with a lot of columns which is why I want/need to use the power of the IDE to populate and bind my controls and so I really do not want to go down the route of manually creating my datasets.

I have searched and searched on this and can't find anything that speaks to how you do this using VS auto generated code.

thanks

John

Solution

  • I just tested and the addition of the query in the InsertCommand happened automatically so you must not have done something properly. Here's EXACTLY what I did:

    1. Created a new database named Test on my local default SQL Server instance via SSMS.
    2. Added a table named Parent with columns ParentId (int, PK, identity) and ParentName (varchar).
    3. Added a table named Child with columns ChildId (int, PK, identity), ParentId (int) and ChildName (varchar).
    4. Created a foreign key in the Child table to the ParentId column from the ParentId column in the Parent table, setting the Update Rule to Cascade.
    5. Created a new VB Windows Forms application project.
    6. Added a new Data Source via the Data Sources window for the Test database.
    7. Opened the TestDataSet from the Solution Explorer in the DataSet designer.
    8. Selected the DataRelation between the two DataTables.
    9. In the Properties window, clicked Edit Relation.
    10. Checked 'Both Relation and Foreign Key Constraint', when Update Rule changed automatically to Cascade.

    At this point, but even after step 7, I was able to select the ParentTableAdapter in the designer, expand the InsertCommand and view the CommandText to see this:

    INSERT INTO [dbo].[Parent] ([ParentName]) VALUES (@ParentName);
SELECT ParentId, ParentName FROM Parent WHERE (ParentId = SCOPE_IDENTITY())

    If you don't see that, you can set it yourself. What happens now is that, when the InsertCommand is executed to insert a new record, that query immediately retrieves the data from that record back into the DataRow in your DataSet. That will update the ParentId column with the newly generated value and the Update Rule will cause that new value to cascade to any related DataRows in the Child DataTable, so you can just go ahead and insert them without worry.