none
Delete Rule SetNull not cascading RRS feed

  • Question

  • I have a strongly type dataset with two tables - parent and child. I have a FK & relation set between the two where a delete in the parent table should set the FK field in the child table to null. 

    The parent table is binded to a control, so I do something along the lines of:

    parentBindingSource.RemoveCurrent();
    parentTableAdapter.Update(dataSetInstance.ParentTable);

    The result is that the entry in the parent table is deleted, but the field in the child is not set to NULL. What do you think might be the problem? The field is declared to allow nulls by the way.

    Tuesday, January 22, 2013 5:29 AM

Answers

  • Be aware that in Ado Net there is a difference between Remove and Delete in the way it acts.

    Remove is removing an item from a table (and then updates will not be done)

    Delete is setting a DataRow to "Deleted" the latter makes it possible to do the deleting at update time using a table adapter as well.

    The last thing a table adapter does is doing acceptchanges which removes items from the datatable.


    Success
    Cor

    • Marked as answer by Alexander Sun Wednesday, January 30, 2013 8:42 AM
    Tuesday, January 22, 2013 8:30 AM
  • I remove the row like this:

    parentBindingSource.RemoveCurrent();

    parentTableAdapter.Update(dataset.ParentTable);

    Are you saying that this will not cascade?

    The way the delete works is in a data bound control, the user right clicks the on an item then selects the delete option from the context menu. So it seemed the easiest way is to remove the current.

    What would be you suggest? How to delete the row so that the delete cascades??

    No you have removed the row so there is nothing to update. Keep in mind that working with .Net means always disconnected handling. So to delete a row in a database you need a row which has a row state which is set to "deleted"


    Success
    Cor

    • Marked as answer by Alexander Sun Wednesday, January 30, 2013 8:42 AM
    Wednesday, January 23, 2013 10:11 AM
  • It turns out I did not set the cascading delete in the dabatase even though it was set in the dataset. When I set the property, everything worked fine.

    By the way bindingSource.RemoveCurrent does work just fine. It cascades and everything. 

    • Marked as answer by VentsyV Thursday, January 31, 2013 2:03 AM
    Thursday, January 31, 2013 2:02 AM

All replies

  • Be aware that in Ado Net there is a difference between Remove and Delete in the way it acts.

    Remove is removing an item from a table (and then updates will not be done)

    Delete is setting a DataRow to "Deleted" the latter makes it possible to do the deleting at update time using a table adapter as well.

    The last thing a table adapter does is doing acceptchanges which removes items from the datatable.


    Success
    Cor

    • Marked as answer by Alexander Sun Wednesday, January 30, 2013 8:42 AM
    Tuesday, January 22, 2013 8:30 AM
  • I remove the row like this:

    parentBindingSource.RemoveCurrent();

    parentTableAdapter.Update(dataset.ParentTable);

    Are you saying that this will not cascade?

    The way the delete works is in a data bound control, the user right clicks the on an item then selects the delete option from the context menu. So it seemed the easiest way is to remove the current.

    What would be you suggest? How to delete the row so that the delete cascades??

    Wednesday, January 23, 2013 3:39 AM
  • I remove the row like this:

    parentBindingSource.RemoveCurrent();

    parentTableAdapter.Update(dataset.ParentTable);

    Are you saying that this will not cascade?

    The way the delete works is in a data bound control, the user right clicks the on an item then selects the delete option from the context menu. So it seemed the easiest way is to remove the current.

    What would be you suggest? How to delete the row so that the delete cascades??

    No you have removed the row so there is nothing to update. Keep in mind that working with .Net means always disconnected handling. So to delete a row in a database you need a row which has a row state which is set to "deleted"


    Success
    Cor

    • Marked as answer by Alexander Sun Wednesday, January 30, 2013 8:42 AM
    Wednesday, January 23, 2013 10:11 AM
  • You totally lost me there. I remove the row from the binding source and when I call update on the table adapter that will call insert/update/delete and the row will be deleted correct? As a matter of fact, the row is being deleted from the database.

    Are you suggesting I have to call tableAdapter.Delete for it to cascade? Is RemoveCurrent not going to mark the row as deleted?

    Thursday, January 24, 2013 5:33 PM
  • You totally lost me there. I remove the row from the binding source and when I call update on the table adapter that will call insert/update/delete and the row will be deleted correct? As a matter of fact, the row is being deleted from the database.

    Are you suggesting I have to call tableAdapter.Delete for it to cascade? Is RemoveCurrent not going to mark the row as deleted?

    Yes

    Success
    Cor

    Friday, January 25, 2013 1:51 PM
  • It turns out I did not set the cascading delete in the dabatase even though it was set in the dataset. When I set the property, everything worked fine.

    By the way bindingSource.RemoveCurrent does work just fine. It cascades and everything. 

    • Marked as answer by VentsyV Thursday, January 31, 2013 2:03 AM
    Thursday, January 31, 2013 2:02 AM