none
DataTable Foreign Key Relationship Not Always Working on Save to Database RRS feed

  • Question

  • I am having a problem with the insert on a Datatable that is a child of another Datatable.  The child table is not picking up the identity column value returned from the database of the parent table.  When this happens I get a foreign key constraint error.  This seems to happen randomly.  It works correctly 90% of the time. 

    I have a parent DataTable sectionv_app and a child DataTable basecostv_app that have a Relationship setup on a sectionid column.  The sectionid column is an identity column in the database and uses the autoincrementseed in the DataTable.  The relationship on these two tables is set to Both Relation and Foreign Key constraint and the Update and Delete rule are set to Cascade.  As you will see below the insert commands are procedures for both.  At the database level the relationships are setup the same way.

    I am programatically creating the rows:

    'Add a row to the parent table

    Dim newSectionRow As appraisalDataSet.sectionv_appRow = app.AppraisalDataSet.sectionv_app.NewRow()

    newSectionRow.name = "TEST"

    ...
    app.AppraisalDataSet.sectionv_app.Rows.Add(newSectionRow)

    newSectionRow.EndEdit()

    'Add a row to the child table
    Dim
    newBaseRow As appraisalDataSet.basecostv_appRow = app.AppraisalDataSet.basecostv_app.NewRow()
    newBaseRow.construction = 1
    newBaseRow.sectionid = newSectionRow.sectionid
    ...

    Database Trace:

    When I save using the tableadaptermanager save function this is what happens 10% of the time (Thows FK constraint Error):

    Sectionv_app Trace:
    declare @p2 int
    set @p2=15450
    exec app.sectionvinsert_app @inBuildingID=9577,@outSectionid=@p2 output
    select @p2

    Basecostv_app Trace:
    select @p2
    declare @p12 int
    set @p12=NULL
    declare @p13 int
    set @p13=NULL
    exec app.basecostvinsert_app @sectionid=-1, @segregatedcostid=4307,@constructionid=1131,@basecost=33983.46,@finalcost=9605.47,@costlessdepreciation=0,@amount=0,@pct=35,@quantity=16182.60,@qualitylevel=4,@unitcost=2.10,@componentid=@p12 output,@basecostid=@p13 output
    select @p12, @p13

    This is what happens the other 90% of the time (Works correctly:

    Sectionv_app Trace:
    declare @p2 int
    set @p2=15451
    exec app.sectionvinsert_app @inBuildingID=9577,@outSectionid=@p2 output
    select @p2

    Basecostv_app Trace:
    declare @p12 int
    set @p12=490102
    declare @p13 int
    set @p13=295358
    exec app.basecostvinsert_app @sectionid=15451,@segregatedcostid=4307,@constructionid=1131,@basecost=33983.46,@finalcost=9605.47,@costlessdepreciation=0,@amount=0,@pct=35,@quantity=16182.60,@qualitylevel=4,@unitcost=2.10,@componentid=@p12 output,@basecostid=@p13 output
    select @p12, @p13


    Thursday, September 1, 2011 3:57 PM

Answers

  • Hi all,

    I appreciate all of your assistance on this matter!  I have been contemplating the 'roll your own' save method for some time now as the tableadapter manager is not smart enough to deal with my database (and datatable) constraints.

    I found what was causing the problem and, in this case, the tableadpater was not to blame. 

    The problem was with a DataRepeater control bound to the basecostv_app datatable.  I recently enhanced the control to colour rows differently depending on some logic.  The logic and colouring is set to take place in the drawItem event.  Apparently the overhead of this newe logic was causing the system some stress and corrupting my data.

    The solution was to "unlink" the misbehaving DataRepeater control from its datasource during the insert and save operations, and then "re-link" it when the save is complete.

    Since I made this change the problem is solved.

    Thanks,
    Koby

    • Marked as answer by koby16 Monday, September 12, 2011 7:37 PM
    Monday, September 12, 2011 7:37 PM

All replies

  • Hi koby16,

    Welcome!

    I'm not sure if you have called DataRow.SetParentRow method. I found a good KB about Insert the related records here: Inserting relational data using DataSet and DataAdapter, I think it is helpful.

    If the links couldn't help you out, would you please feel free to send a demo to me.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, September 2, 2011 7:41 AM
    Moderator
  • Hi there,

    Thank you for the response!  I was not using the DataRow.SetParentRow method.

    I did make the change to my code and it has not made a difference.  The problem is so sporadic, I cannot reproduce it in my test environment so it will be almost impossible for me to give you some code that will replicate the problem.

    Any other suggestions would be greatly appreciated.

    The code that inserted these rows worked for about 7 months (thousands of executions) before it started misbehaving.

    Thanks,
    Koby

    Tuesday, September 6, 2011 5:13 PM
  • Hi,

    Thanks for your feedback.

    'Add a row to the child table
    Dim newBaseRow As appraisalDataSet.basecostv_appRow = app.AppraisalDataSet.basecostv_app.NewRow()
    newBaseRow.construction = 1
    'newBaseRow.sectionid = newSectionRow.sectionid
    newBaseRow.SetParentRow(newSectionRow)
    

    Please try to use SetParentRow method instead of setting the praentrow's Id.

    Have a nice day.

     


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, September 8, 2011 7:20 AM
    Moderator
  • Hi Alan,

    I appreciate your assistance on this matter!

    As I indicated in my previous post I had made the change you suggested and I am still encountering the same error condition.

    The new code is exactly as what is in your last post.

    Any other suggestions would be greatly appreciated.

    Thanks,
    Koby

    Thursday, September 8, 2011 2:05 PM
  • It seems that if your Id is set via the database engine, the you would benefit from commiting your parent row to the database and accepting your changes prior to creating the child rows in memory.
    Brent Spaulding | Access MVP
    Thursday, September 8, 2011 3:01 PM
  • Hi Brent,

    A good idea for a work around.  The problem is there could be an issue when I am inserting the rows into the child tables (34 child tables).  In that case, I do not want to save the parent record for data integrity reasons.

    Again.  Most of the time the save works and about 10% of the time it fails.

    This is a major issue in my production system so any assistance would be greatly appreciated.

    Thanks,
    Koby

    Friday, September 9, 2011 7:45 PM
  • Wrap up the whole update in a Transaction. That way if one of the child table updates fails, you can simply rollback (or not commit) the transaction.
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Saturday, September 10, 2011 1:28 AM
  • The thread starter indicated that the tableadaptermanager was being used to perform the save ... so ... If I recall correctly, I think the TAM automatically wraps everything in a transaction.

    I am wondering, though, if it would be best to NOT use the TableAdapterManager and 'roll your own' save method in order to control the order in which data is saved to the database.

    I personally don't use adapters much (I tend to 'roll my own' using Command objects), so my already novice experience is even more limited when it comes to the use of adapters.


    Brent Spaulding | Access MVP
    Saturday, September 10, 2011 4:54 PM
  • Brent, I don't use the TableAdapter for anything either, as I, too, roll my own database updates (you may have noticed links in these forums to my blog series about DataAccess). I try to avoid posting replies here that may be specific to TableAdapters, since I know little-to-nothing about them (except for the fact that I know that I don't like them)!
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Saturday, September 10, 2011 10:14 PM
  • Hi all,

    I appreciate all of your assistance on this matter!  I have been contemplating the 'roll your own' save method for some time now as the tableadapter manager is not smart enough to deal with my database (and datatable) constraints.

    I found what was causing the problem and, in this case, the tableadpater was not to blame. 

    The problem was with a DataRepeater control bound to the basecostv_app datatable.  I recently enhanced the control to colour rows differently depending on some logic.  The logic and colouring is set to take place in the drawItem event.  Apparently the overhead of this newe logic was causing the system some stress and corrupting my data.

    The solution was to "unlink" the misbehaving DataRepeater control from its datasource during the insert and save operations, and then "re-link" it when the save is complete.

    Since I made this change the problem is solved.

    Thanks,
    Koby

    • Marked as answer by koby16 Monday, September 12, 2011 7:37 PM
    Monday, September 12, 2011 7:37 PM