none
TableAdapterManager Hierarchical Update Not Working

    Question

  • Using Typed Datasets in Winforms VS2010 VB.Net, SQL Server 2008 R2 backend.

    I created a DataSet consisting of two, related, properly normalized tables.
    Hierarchical Update is True.
    Refresh the data table is checked for both tables and the SELECT statements are being generated.

    I set up a master-detail form based on this DataSet.

    When I insert records in both the Master and Detail tables, call .EndEdit on the BindingSource, call .UpdateAll on the TableAdapterManager, it complains about a foreign key violation. Obviously, the SCOPE_IDENTITY() returned by the SELECT statement is not being seen or used by the UpdateAll routine.

    Public Class Form1
        Private Sub MasterBindingNavigatorSaveItem_Click(sender As System.Object, e As System.EventArgs) Handles MasterBindingNavigatorSaveItem.Click
            Me.Validate()
            Me.MasterBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.MasterDetailDataSet)
        End Sub
    
        Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
            Me.MasterTableAdapter.Fill(Me.MasterDetailDataSet.Master)
            Me.DetailTableAdapter.Fill(Me.MasterDetailDataSet.Detail)
        End Sub
    End Class
    A little research led me to believe Update Rule: Cascade should be set. I tried each of the three radio buttons in this dialog:

    Results on Insert of Master and new Detail:

    Relation Only: Fails with foreign key violation (as expected)
    Foreign Key Constraint Only + Update Rule: Cascade: Invalid Operation Exception is thrown when launching the application: An error occurred creating the form. See Exception.InnerException for details.  The error is: DataMember property 'FK_Detail_Master' cannot be found on the DataSource.
    Both Relation and Foreign Key Constraint + Update Rule: Cascade: Exception on validation of new Detail - System.Data.InvalidConstraintException: ForeignKeyConstraint FK_Detail_Master requires the child key values (-1) to exist in the parent table.

    Can someone help decipher this business?


    Edit: Moderator - this was not posted in the most appropriate forum. Please feel free to move it.
    • Edited by KorLibrary Thursday, October 22, 2015 3:50 AM
    Thursday, October 22, 2015 3:33 AM

Answers

  • I have found that with the DataSet and Relation Configured as shown below, hierarchical update works. Setting Enforce Contraints = False is counterintuitive, however. It is not mentioned in the walkthrough - nor is any discussion of configuring the DataSet, other than being reminded to set the property Hierarchical Update = True.

    I am not sure what precise effect setting Enforce Constraints to False (or True) has. Maybe this is a bug, or maybe the documentation needs to be updated.

    Thursday, October 22, 2015 1:19 PM

All replies

  • Hello,

    I would suggest reviewing the following article on saving data from related data tables (Hierarchical update). If that does not help come back and let us know.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my webpage under my profile but do not reply to forum questions.
    Microsoft Developer tools
    Developer’s Guide to Windows 10 video series

    Thursday, October 22, 2015 9:18 AM
    Moderator
  • Kevin shows you the newer tableadaptermanager. It is made to overcome the troubles which were by using a raw dataadapter or a tableadapter. 

    In that case you have to update in the following order:

    The New Master rows
    The New detail rows
    The deleted detail rows

    and then in whatever order the other rows.

    Be aware that in a dataset cannot be at the same time deleted, updated and removed rows with the same keys, some are trying to keep track of that.

    To do that you need to use the following method

    https://msdn.microsoft.com/en-us/library/5dxfaha8(v=vs.110).aspx

    Be aware every type of changed rows has to be updated individually 

    Therefore, go to the tableadaptermanager if you are not yet using it.


    Success
    Cor


    • Edited by Cor Ligthert Thursday, October 22, 2015 10:19 AM
    Thursday, October 22, 2015 10:16 AM
  • Can you post the InsertCommand statements from each of your TableAdapters? The DetailTableAdapter InsertCommand should have an INSERT SQL statement followed by the SELECT with the SCOPE_IDENTITY statement (separated by a semi-colon).

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, October 22, 2015 12:05 PM
  • I forgot to note that I'm also handling the AddingNew event on the DetailBindingSource

        Private Sub DetailBindingSource_AddingNew(sender As Object, e As System.ComponentModel.AddingNewEventArgs) Handles DetailBindingSource.AddingNew
            MasterBindingSource.EndEdit()
        End Sub

    Thursday, October 22, 2015 12:07 PM
  • I've already reviewed it, and followed it step by step.

    Please try this on your own system and let me know if it works for you. If it does, please post your solution.

    Thank you.

    • Edited by KorLibrary Thursday, October 22, 2015 12:10 PM
    Thursday, October 22, 2015 12:08 PM
  • I forgot to note that I'm also handling the AddingNew event on the DetailBindingSource

        Private Sub DetailBindingSource_AddingNew(sender As Object, e As System.ComponentModel.AddingNewEventArgs) Handles DetailBindingSource.AddingNew
            MasterBindingSource.EndEdit()
        End Sub


    When posting your question please supply as many details as possible.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my webpage under my profile but do not reply to forum questions.
    Microsoft Developer tools
    Developer’s Guide to Windows 10 video series

    Thursday, October 22, 2015 12:40 PM
    Moderator
  • I've already reviewed it, and followed it step by step.

    Please try this on your own system and let me know if it works for you. If it does, please post your solution.

    Thank you.

    We are not here to create a project to replicate this issue, consider how much time it would take someone here to create a project and mock up data. We are all volunteers, keep this in mind.

    Also a note regarding the reply of yours I removed, please be respectful of others trying to assist you. If you don't care for a reply simply ignore it is generally best.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my webpage under my profile but do not reply to forum questions.
    Microsoft Developer tools
    Developer’s Guide to Windows 10 video series

    Thursday, October 22, 2015 12:42 PM
    Moderator
  • Paul,

    Thank you. As stated above, the SELECT statements are being generated.

    Here they are...

    Detail:

            Me._adapter.InsertCommand.CommandText = "INSERT INTO [dbo].[Detail] ([MasterID], [DetailName]) VALUES (@MasterID, @DetailN" & _
        "ame);" & Global.Microsoft.VisualBasic.ChrW(13) & Global.Microsoft.VisualBasic.ChrW(10) & "SELECT DetailID, MasterID, DetailName FROM Detail WHERE (DetailID = SCOPE" & _
        "_IDENTITY())"

    Master:
                Me._adapter.InsertCommand.CommandText = "INSERT INTO [dbo].[Master] ([MasterName]) VALUES (@MasterName);"&Global.Microsoft.VisualBasic.ChrW(13)&Global.Microsoft.VisualBasic.ChrW(10)&"SELECT MasterID,"& _ 
                    " MasterName FROM Master WHERE (MasterID = SCOPE_IDENTITY())"

    Thursday, October 22, 2015 12:47 PM
  • I will, thanks.



    Thursday, October 22, 2015 12:57 PM
  • I have found that with the DataSet and Relation Configured as shown below, hierarchical update works. Setting Enforce Contraints = False is counterintuitive, however. It is not mentioned in the walkthrough - nor is any discussion of configuring the DataSet, other than being reminded to set the property Hierarchical Update = True.

    I am not sure what precise effect setting Enforce Constraints to False (or True) has. Maybe this is a bug, or maybe the documentation needs to be updated.

    Thursday, October 22, 2015 1:19 PM
  • I don't recall ever seeing a carriage return/line feed combination embedded between the SQL statements before so I don't know if this is causing an issue. It would seem easier to me to specify the statements in the command properties for the TableAdapter using the Data Designer, rather than assigning them in code. You might want to give that a try.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, October 22, 2015 1:48 PM
  • Paul,

    Thank you. Those statements are generated by the "Refresh the data table" CheckBox in the advanced options of the TableAdapter Configuration Wizard and they're written to the DataSet's designer.vb file. I agree - they're not pretty. They do work, however. Without those statements, Hierarchical Update does not work.

    At any rate, setting Enforce Contraints to False in the DataSet properties seems to do the trick. The decremented foreign keys (-1, -2, etc) pre-assigned in child tables for new rows are correctly replaced with the values returned by SCOPE_IDENTITY when this property is set.

    Thursday, October 22, 2015 2:06 PM
  • Hmm...didn't see this before I replied. I wonder if this happens because the related column in both tables is an Identity column? Perhaps the constraint is being checked before the Master table rows are populated with the Identity column value after the INSERT.

    Paul ~~~~ Microsoft MVP (Visual Basic)


    Thursday, October 22, 2015 2:08 PM
  • Paul, the related columns are an Identity PK column on table Master (MasterID), and a foreign key on table Detail. There is no index on Detail.MasterID. Only the one side of the relationship is an Identity column. If I find time, I may one day dig further into the generated UpdateAll method.

    I think there may be bugs in the code that Hierarchical Update = True generates, and/or in other generated DataSet code. With Enforce Contraints = False and any radio button except for "Relation Only" checked, exceptions are thrown when either manipulating data in the form, or when simply initializing the form (even opening it in design view).

    It is also possible that the way I've configured it is the intended way. No matter, really. If DataSet is defective, I know it'll never be fixed. I find a few posts around the web reporting this behavior, but no further effort to explore further.

    Thank you for taking a look.


    • Edited by KorLibrary Thursday, October 22, 2015 3:21 PM
    Thursday, October 22, 2015 3:19 PM
  • No solution for this issue since?

    Wondering whether this method work with Microsoft Access Database?

    Friday, February 9, 2018 4:25 AM
  • No solution for this issue since?

    Wondering whether this method work with Microsoft Access Database?

    Found a solution for this Master Detail  Hierarchical Update solution.

    http://www.vbforums.com/showthread.php?659052-Retrieve-Access-AutoNumber-Value-After-Insert&highlight=

    Friday, February 9, 2018 7:27 AM