Deleting Coresponding Row(s)

[justin91980]

Hi if anyone could help I would appreciate it. I have a database with a
Movies Table, Casting Table, and Actor Table.

Movie Table has MovieID
Casting Table has MovieID , and ActorID
Actor Table has ActorID

This code deletes a row from the movies table(using a datagrid in vb.net), I need all the rows in the Casting table with the same MovieID to also be deleted.
Any help would be appreciated!!



Dim result As String
Dim i As Integer
i = MovieDataGridView.CurrentRow.Index
result = MsgBox("Are you sure you want to delete this movie?", MsgBoxStyle.OkCancel)
If result = vbOK Then

If MovieDataGridView.Rows.Count = 1 Then
MsgBox("You must leave one movie in database!!")
Else
Me.MovieDataGridView.Rows.Remove(Me.MovieDataGridV iew.SelectedRows(0))
End If
Else

End If
Me.Validate()
Me.MovieBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.MovieProjectDa taSet)

 

[cjard]

Use a DataRelation set to ForeignKey constraint mode, and have the deletes cascaded. Open your dataset, choose New.. Data Relation (or something like that. Actually I always create them by click-drag from the grey area left of the column name to the destimation table)

 

[MattP]

It should be noted that this sets up a relation between the tables but you need to right click on that relation and choose edit relation. From there you can change it from relation only to relation and foreign key constraint. Once you've done this you can change your deletes to cascading via the dropdownlist.