none
Issue with InsertUpdateDelete UpdateOrder in TableAdapterManager RRS feed

  • Question

  • Hi all!

    I am finding that my database is throwing a Unique Constraint Error when I save out a dataset.  The TableAdapterManager issues Inserts, Updates, then Deletes; or Updates, Inserts, and then Deletes.  If I could make the TableAdapterManager issue the Delete first I would not have the problem.  Here is the scenario:

    Database Table:

    Table1
    =============
    PrimaryKeyValue
    ForeignKeyValue
    Code
    Value

    **Unique Constraint on Code and ForeignKeyValue

    Table Data:

    PrimaryKeyValue  ForeignKeyValue  Code     Value
    =================================
    1                          1                       'AAA'    12
    2                          1                       'BBB'    16

    User Actions on Table1 in a DataGridView in the Windows Form:

    1. Delete Row with PrimaryKeyValue = 1
    2. Add a Row with PrimaryKeyValue = 3, ForeignKeyValue= 1, Code = 'AAA', and Value = 17

    TableAdapter.UpdateAll executes the following on the database in this order:

    insert into table1
    values(3, 1, 'AAA', 17)

    delete from table1
    where PrimaryKeyvalue = 1

    You can guess what happens.  Yes, a Unique Constraint Error is thrown by the database on the Insert.

    I must be missing something simple here.  Does anyone have any suggestions?  I do not wish to drop the Unique Constraint on the database.

    Thanks!
    Koby

    Thursday, October 21, 2010 10:52 PM

Answers

  • Hi Koby,

    I think one workaround is that you first call the update to delete rows from the database, then do the insert or update operation. This will not violate the unique constraint. And if you are not dealing with related tables, but only one table, then you can also use the DataAdapter to update the table and customize the update order freely, you can use the GetChanges method to filter the type of changes, such as the deleted rows.   

    For detailed information about GetChanges method please refer to
    http://msdn.microsoft.com/en-us/library/0f8054fy.aspx

    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by koby16 Friday, October 22, 2010 3:21 PM
    Friday, October 22, 2010 5:40 AM
    Moderator

All replies

  • Hi Koby,

    I think one workaround is that you first call the update to delete rows from the database, then do the insert or update operation. This will not violate the unique constraint. And if you are not dealing with related tables, but only one table, then you can also use the DataAdapter to update the table and customize the update order freely, you can use the GetChanges method to filter the type of changes, such as the deleted rows.   

    For detailed information about GetChanges method please refer to
    http://msdn.microsoft.com/en-us/library/0f8054fy.aspx

    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by koby16 Friday, October 22, 2010 3:21 PM
    Friday, October 22, 2010 5:40 AM
    Moderator
  • Hi Alex,

    Thank you for you assistance!  I implemented the following:

     

    'Process the deletes first on some tables
    Me.Sectionmultiplier_appTableAdapter.Update(Me.AppraisalDataSet.sectionmultiplier_app.Select(Nothing, Nothing, DataViewRowState.Deleted))

     

    'Process the remaining operations
    Me.TableAdapterManager.UpdateAll(Me.AppraisalDataSet)

    Cheers,
    Koby

    Friday, October 22, 2010 3:21 PM