resetting child records when delete parent

[acorn]

I have two tables- one called territory and the other called contacts. Each contact can belong to a territory.
On my form, i allow the user to delete a territory. However, i don't want to actually delete the contacts associated with this territory from the database. I just want to set the territoryID on them to DBNULL.
I don't have any relationships set at the database level that will cause an automatic deletion of these child records.
Right now, when the user clicks on the delete button, the territory row is properly removed from the datagrid control. But now I'd like to set up my SAVE button so that I have a change to reset the territoryIDS on the child records... and then delete the territory from the database.
So far, in my save button I have
Dim TerritoryDeletions() As DataRow = TerritoryDS.Territory.Select("", "", DataViewRowState.Deleted)

I don't know how to loop through this territoryDeletions object and get all the territory IDs.

 

[vinnie881]

This is far easier if you were to use a database (i.e. sql,access,mysql,etc)

Dim TerritoryDeletions() As DataRow = TerritoryDS.Territory.Select("", "", DataViewRowState.Deleted)
dim x as string
for each dr as datarow in territoryDeletions()
   x = "TerritoryID = " & dr(TerritoryID)

   For each dr1 as datarow in dtContacts.Select(x)
     dr1(TerritoryID) = dbnull.value 
   next
next

 

[acorn]

dtcontacts

what is dtcontacts in this case?
is it my datagrid for contacts?
Thanks for the help. I am using a database... but I didn't want to set up a relationship that would cascade delete my records... Is there another way to do this that won't delete child records when I delete the parent?
I'm using a sql server express 2005 db.
thx.

 

[vinnie881]

Dtcontacts was supposed to represent your contacts datatable in .net.
If you are using sql, then just replace the bellow

Dim TerritoryDeletions() As DataRow = TerritoryDS.Territory.Select("", "", DataViewRowState.Deleted)
dim x as string
for each dr as datarow in territoryDeletions()
   x = "TerritoryID = " & dr(TerritoryID)

   For each dr1 as datarow in dtContacts.Select(x)
     dr1(TerritoryID) = dbnull.value 
   next
next

with a query to update your datatable in sql

Dim TerritoryDeletions() As DataRow = TerritoryDS.Territory.Select("", "", DataViewRowState.Deleted)
dim x as string
for each dr as datarow in territoryDeletions()
   x = "Update MyContactTable " _
 & " set TerritoryID = null " _
& "where territoryID = " & dr("TerritoryID")

'now just run your function to send that query in the x variable to sql.
next

 

[acorn]

Getting an error

Hi there. I'm trying the code but i'm getting an error on the line that reads:
x = "TerritoryID = " & dr("TerritoryID")

The error is
Run-time exception thrown : System.Data.DeletedRowInaccessibleException - Deleted row information cannot be accessed through the row.

Any suggestions?

 

[MattP]

... & dr(TerritoryID, DataRowVersion.Original)

 

[acorn]

Thanks... but I think I'm still missing something

Thanks MattP. That got rid of the error message... but the system still isn't removing the territory from the database and the contacts' territoryid are still set to the same value instead of NULLS.
My code looks like this:

Private Sub TerritoryBindingNavigatorSaveItem_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TerritoryBindingNavigatorSaveItem.Click
'research EndEdit more- only required for Datagrids
Me.TerritoryBindingSource.EndEdit()
Me.ContactsBindingSource.EndEdit()
'ADDITIONS & CHANGES - just go ahead and save.
Dim TerritoryUpdates() As DataRow = TerritoryDS.Territory.Select("", "", DataViewRowState.Added Or DataViewRowState.ModifiedCurrent)
Dim ContactUpdates() As DataRow = TerritoryDS.Contacts.Select("", "", DataViewRowState.Added Or DataViewRowState.ModifiedCurrent)
Me.TerritoryTableAdapter.Update(TerritoryUpdates)
Me.ContactsTableAdapter.Update(ContactUpdates)

'DELETIONS
'2008 March 19 Start
Dim TerritoryDeletions() As DataRow = TerritoryDS.Territory.Select("", "", DataViewRowState.Deleted)
Dim x As String
For Each dr As DataRow In TerritoryDeletions
x = "TerritoryID = " & dr("TerritoryID", DataRowVersion.Original)
For Each dr1 As DataRow In TerritoryDS.HouseHolder.Select(x)
dr1("TerritoryID") = DBNull.Value
Next
Next
'Me.TerritoryTableAdapter.Update(TerritoryDeletions)
'2008 March 19 Stop
End Sub

 

[cjard]

DataRelaiton can do this..

Set to be ForeignKey type and change the On Parent Delete action to be Set Child Null

 

[cjard]

Oh and please, acorn, learn what a code tag is:

http://www.vbdotnetforums.com/misc.php?do=bbcode

 

[acorn]

after adding parent/child relationship

cjcard,
i've added a relationship between the parent and child table by using the data designer, and creating a relation & foreign key constraint.

The update rule is set to NONE
The delete rule is set to "SET NULL"
The accept/reject rule is set to "NONE"

I now have two problems:

1. when i try to delete a territory, it removes the related contact records from the contacts datagrid... and also the territory from the territory datagrid...but it's not really deleting the territory from the database because when i log back in to the system, the territory reappears in the territory list. It does however properly reset the territoryIDs on the child records to NULL, per the defined relationship.

2. When i now try to refresh the territory dataset using the refresh button created by the wizard on the vcr, it crashes with an error:

System.Data.InvalidConstraintException was unhandled
Message="Cannot clear table Territory because ForeignKeyConstraint FK_Contacts_Territory enforces constraints and there are child rows in Contacts."

The code behind the refresh button looks like this:

    Private Sub ToolStripButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton2.Click
        Me.TerritoryTableAdapter.FillBySortedName(TerritoryDS.Territory)
        Me.ContactsTableAdapter.Fill(TerritoryDS.Contacts)
        Me.TerritoryBindingSource.EndEdit()
        Me.ContactsBindingSource.EndEdit()
    End Sub

 

[acorn]

more on parent/child relationship

one other point,

the code behind my save button looks like this:

Private Sub TerritoryBindingNavigatorSaveItem_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TerritoryBindingNavigatorSaveItem.Click
        'research EndEdit more- only required for Datagrids
        Me.TerritoryBindingSource.EndEdit()
        Me.ContactsBindingSource.EndEdit()

        Dim TerritoryUpdates() As DataRow = TerritoryDS.Territory.Select("", "", DataViewRowState.Added Or DataViewRowState.ModifiedCurrent Or [B]DataViewRowState.Deleted)[/B]
        Dim CUpdates() As DataRow = TerritoryDS.Contacts.Select("", "", DataViewRowState.Added Or DataViewRowState.ModifiedCurrent)
        Me.TerritoryTableAdapter.Update(TerritoryUpdates)
        Me.ContactsTableAdapter.Update(CUpdates)
End Sub

When the DataviewRowState.Deleted is not there, i get no errors trying to delete - the app behaves as described in the previous post. When i add the DataviewRowState.Deleted, I get another error message when saving the data that says:

System.Data.SqlClient.SqlException was unhandled
Class=16
ErrorCode=-2146232060
LineNumber=1
Message="The DELETE statement conflicted with the REFERENCE constraint "FK_Contacts_Territory". The conflict occurred in database "territory", table "dbo.Contacts", column 'TerritoryID'.
The statement has been terminated."

 

[acorn]

figured out one problesm

Hi there. I found my mistake with the save button error message.
although i was looking for records that were deleted in the territory dataset and including them in the call to update my table, i wasn't include the child records that were deleted by doing the following:

Dim CUpdates() As DataRow = TerritoryDS.Contacts.Select("", "", DataViewRowState.Added Or DataViewRowState.ModifiedCurrent Or [B]DataViewRowState.Deleted[/B])

So now, it does actually remove the territory when i delete it, and sets the child records to null.
however, my refresh button still doesn't work. crashing with the same error message as posted 2 posts ago
any help would be appreciate. It's probably something really simple that i'm missing. sorry.

 

[cjard]

i dont know which your child and parent are, but in your refresh button your logic must be:

clear child
clear parent
fil parent
fill child

order is important

REMEMBER: when tableAdapter.ClearBeforeFill = True, the ta will clear the datatable before it fills it

 

[acorn]

no go...

hi again.

this thing just doesn't want to work for me. I've taken your advice and ensured that i'm clearing in the right order and filling in the right order...
but i'm still getting the error message.

Cannot clear table Territory because ForeignKeyConstraint FK_Contacts_Territory enforces constraints and there are child rows in Contacts.

 

[cjard]

And, are there? Is it lying to you?