Deleting a single row from related tables

Deleting a single row from related tables

• nick5454

  Not a professional solution but I must agree that that's the only way I could find but not implemented yet. I'm waiting for a good answer from Microsoft, but I think I'm gonna wait forever...

  
  
  

• Alexander Olekhnovich

  Hi,

  If your using a datagridview object then you can trap it by using the UserDeletingRow event. It triggers before the row is actually marked as deleted in your datatable. You can then get what row the user is trying to delete and then use getchildrows to have the rows that you want to delete. And since you don't want to delete your parent row you can issue a cancel to the event so that the delete issued would not continue.

  cheers,

  Paul June A. Domag

  
  
  

• UnknownScripter

  Hi,

  Create a foreign key constraint for the columns.

  Dim parentColumn As DataColumn
  Dim childColumn As DataColumn
  Dim fkeyConstraint As ForeignKeyConstraint
  
  ' Set parent and child column variables.
  parentColumn = suppliersProducts.Tables("Suppliers").Columns("SupplierID")
  childColumn = suppliersProducts.Tables("Products").Columns("SupplierID")
  fkeyConstraint = New ForeignKeyConstraint( _
  "SupplierFKConstraint", parentColumn, childColumn)
  

  fkeyConstraint.DeleteRule = Rule.Cascade

  ' Add the constraint, and set EnforceConstraints to true.
  suppliersProducts.Tables("Products").Constraints.Add(fkeyConstraint)
  suppliersProducts.EnforceConstraints = True
  

  Now if you delete the parent record, it should automatically delete the child records.

  
  

  
  
  

• PedroSimao

  Why are you preforming deletes on a row by row bases If you use a strongly typed data adapter and preform the update comand on the tables in the appropriate order - You will be able to avoid this issue.
  
  
  

• VLB

  Nice answer but how can you know whether the user is going to delete all child rows When the user deletes rows from DataGridView they are marked as Deleted, there is nothing more you can do after then. The use may or may not delete all the child rows. If I use another list or array to keep a reference to these child rows, it works, but this is a child solution not a professional's. Any idea

  
  
  

• Zak1968

  Hello
  
  I have the same (or similar) problem. I need to get all child rows belonging to some parent row. But GetChildRows() does not return rows with status Deleted.
  
  I think, you could use SetModified() method on onyour deleted rows, so you will be able then compare keys and after that delete that row again. It's doews not look nice to me, but for now I see it as the only option.
  
  Stano
  
  

• pjmowbs

  No, you understood me wrong. My tables have already relationships whickh make them primary and child tables and when I delete a parent row, the children are automatikally deleted , that's not my problem. The problem is that:

  When I want to save only a single parent and its children, I do the steps recommended by the Microsoft.

  1- Update all deleted children

  2- Update the parent

  3- Update the modified or added children

  4- If all steps are ok, commit the transaction and do AcceptChanges for the updated rows.

  When I want to get the deleted rows by myChildTable.GetChanges(DataRowState.Deleted), it returns "ALL" the deleted children in that table not the ones which belong to my parent row. When I do a foreach loop to iterate through all the children and try to compare their foreign key to my parent row's primary key, I have that Exception : You can not access a deleted row.

  Now, how can I get the deleted children for a parent row

  
  
  

• Diamantregen

  Hi,

  Why not use the GetChildRows of your parent row prior to deleting the parent row In this case you would already know which child rows are going to be deleted before deleting the parent row. And you can then manually delete the childrows and not delete the parent row itself since you already know the affected child rows.

  cheers,

  Paul June A. Domag

  
  
  

• DragonC#

  I perfor deletes one by one because I do not want to deal with errors while updating tables which will need more code to get the NOT-updated rows, display them to the user again, correct any errors if exists, try updating again, or when the electricity goes off, all the records ( may be hundreds of them ) be deleted and the user must start over, or on a multi-client environment where the clients update a shared datatable and a real time information update is necessary... and so on. If you update a row one by one, information can be shared among clients at a much more convenient, easy and fast way and that's what I need. And preventing a DataRow being read only is a big faul of Microsoft. To keep a DataRow readonly would protect it from being modified, why do they prevent it from benig read Once deleted( not physically ) user must forget the DataRow, that does not make sense to me. Any idea

  
  
  

• Jason D. Camp

   

               This is a solutuion but I do not use DataGridView everywhere in my program. Mostly a Binding navigator of my own ( all delete, save and add methods are handled by me for user confirmation and some other processings ). So I can not use this solution everywhere. I hope Microsoft finds a solution for this simple and un-logical mechanism. A programmer must be able to at least read the deleted row because it is not physically deleted (we have not lost the patient :) ) yet and there must be a way to access it. Thank for your help Paul, this is a good solution where a DataGridView is used but I do not even want to use this component because of it's unsolved BUGs and tons of other mysterious problems. I use my own components and ListView, CheckedListBox like components when there is not much code to be written.

  
  
  
• Deleting a single row from related tables