none
TableAdapter referential integrity error on MDB database RRS feed

  • Question

  • I’m using VS2010 and I’m new to ADO.Net so please bear with me. I’m trying to convert a VB6 app to .Net that uses a MDB as a backend. When I try to update the auto generated TableAdapter of the “Master” table of my winform with records that have a related record in the “Detail” table I’m getting a referential integrity error “The record cannot be deleted or changed because table 'Detail' includes related records.” even though I’m only changing data on the “master” table and not the Detail table. I don’t get the error if there are no related records and I can Add details and Delete Details without getting the error. The issue only happens when the Key field of the Master table is not an AutoNumber type this is because the Key (ID) is generated by the app, if I make the Key field AutoNumber all works as expected. I would like to know why this is the case since I’m not adding a record, I’m only changing it on the master side. I just want to be able to use the MDB without having to change it and just install the new .Net app on top. The Referential integrity of both tables is set correctly (1 to many and Enforce Referential Integrity) and works fine under the VB6 app. I’ve changed the relationship parameters on the “XSD” layout with no change. Why can’t VS2010 handle this scenario? What do I need to do to resolve this?

    Thanks

    • Moved by Val MazurModerator Wednesday, July 25, 2012 12:17 PM (From:ADO.NET Managed Providers)
    Wednesday, July 25, 2012 12:05 AM

Answers

All replies

  • Hi j-c-m,

    Seems like that you should specify the Update Rule/Delete Rule in the relation configure dialog.

    Please refer to the following thread which might help with you:

    Best Regards,


    Tony Xiao [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by j-c-m Friday, July 27, 2012 3:57 PM
    Wednesday, July 25, 2012 10:16 AM
    Moderator
  • Tony thanks for you reply,

    I read that article and tried that and no change in the behavior. If there is NO change in the related detail table why is there a constraint being applied. I up-sized the table to SQLExpress to test and there was no problem but that defeats what I'm trying to do.  And like I said in my original post if I make the Master table's key an AutoNumber field it works fine but that entails updating the Foreign Key on all related tables again defeating what I'm trying to do. There has to be some simple solution that I'm missing or is it the fact that the primary key is NOT AutoNumber in the master table of the MDB the reason since it works fine in SQLExpress having a non auto-incremental key?

    Regards

    UPDATE:

    The Stackoverflow article DOES resolve the issue by selecting “Cascade Update Related Fields” in the “Edit Relationship” window of the MDB since VS attempts to change the PK of the master table when you make a change triggering the referential integrity error. My problem was that my changes to the MDB in the BIN directory were getting overwritten by VS since it copies a new MDB every time you run the project unless you change the “Copy to Output Directory” to “DO NOT COPY”.

    Can anybody tell me why would VS want to change the PK of the Master table when you make a change when it does not need to?  Specially when in the end it does not change it but it does try triggering the violation if you don’t have the “cascade update related fields”.

    Anyway credit to Tony…

    • Edited by j-c-m Friday, July 27, 2012 3:57 PM
    Wednesday, July 25, 2012 3:46 PM