Removing rows from two connected tables

[mick]

I have two tables Game and Disks

Game-
GameID (PrimaryKey)
GameName
...

Disks
DiskID
...
GameID (ForeignKey)

On my Form I have a listbox that shows the GameName field for each row.
When the Remove_button is pressed the selected listbox item is removed
from both the Game and Disk tables. The problem was, however when I
hit the

gameTableAdapter.Update(gamesArchiveDataSet);

I got the following error-

The DELETE statement conflicted with the REFERENCE constraint
"FK_Disks_Game". The conflict occurred in database
"C:\USERS\MICK\DOCUMENTS\VISUAL STUDIO 2008\PROJECTS\GAME ARCHIVES\GAME
ARCHIVES\BIN\DEBUG\GAMESARCHIVE.MDF", table "dbo.Disks", column 'GameID'.

The thing is, when I commented out this line and just did the Update() on
the "Disk" table it worked and the rows
from _both_ of the tables were removed from the Dbase. I`m pleased that it
works but I was wondering if
someone could give a little explanation why it works and why the updating of
the gameTableAdapter fails.

As I`ve said before I`m very new to this so simple(ton) would help.



private void Remove_button_Click(object sender, EventArgs e)
{

int i = listBox1.SelectedIndex;

// Delete row from Game table and Disks table

gamesArchiveDataSet.Game.Rows.Delete();
gamesArchiveDataSet.Disks.Rows.Delete();

//gameTableAdapter.Update(gamesArchiveDataSet);
disksTableAdapter.Update(gamesArchiveDataSet);

gamesArchiveDataSet.Disks.AcceptChanges();
gamesArchiveDataSet.Game.AcceptChanges();

// Get and display the number of rows in each table
int numGameRows = gamesArchiveDataSet.Game.Count;
int numDisksRows = gamesArchiveDataSet.Disks.Count;
MessageBox.Show("GameRows = " + numGameRows + "\n" + "DisksRows = " +
numDisksRows);

}



TIA mick

 

[Pavel Minaev]

I have two tables Game and Disks

Game-
 GameID     (PrimaryKey)
 GameName
 ...

Disks
 DiskID
 ...
 GameID    (ForeignKey)

On my Form I have a listbox that shows the GameName field for each row.
When the Remove_button is pressed the selected listbox item is removed
from both the Game and Disk tables. The problem was, however when  I
hit the

    gameTableAdapter.Update(gamesArchiveDataSet);

I got the following error-

The DELETE statement conflicted with the REFERENCE constraint
"FK_Disks_Game". The conflict occurred in database
"C:\USERS\MICK\DOCUMENTS\VISUAL STUDIO 2008\PROJECTS\GAME ARCHIVES\GAME
ARCHIVES\BIN\DEBUG\GAMESARCHIVE.MDF", table "dbo.Disks", column 'GameID'.

The thing is, when I commented out this line and just did the Update() on
the "Disk" table it worked and the rows
from _both_ of the tables were removed from the Dbase. I`m pleased that it
works but I was wondering if
someone could give a little explanation why it works and why the updatingof
the gameTableAdapter fails.

Most likely, when you do the Update() on games, it tries to delete
record from Games table first, and only then those from Disks. If you
have any records in Disks referencing the one you're trying to delete
from Games, then deletion would result in a foreign key constraint
violation (since you would get a bunch of Disks that reference a non-
existing Game).

 

[mick]

I have two tables Game and Disks

Game-
GameID (PrimaryKey)
GameName
...

Disks
DiskID
...
GameID (ForeignKey)

On my Form I have a listbox that shows the GameName field for each row.
When the Remove_button is pressed the selected listbox item is removed
from both the Game and Disk tables. The problem was, however when I
hit the

gameTableAdapter.Update(gamesArchiveDataSet);

I got the following error-

The DELETE statement conflicted with the REFERENCE constraint
"FK_Disks_Game". The conflict occurred in database
"C:\USERS\MICK\DOCUMENTS\VISUAL STUDIO 2008\PROJECTS\GAME ARCHIVES\GAME
ARCHIVES\BIN\DEBUG\GAMESARCHIVE.MDF", table "dbo.Disks", column 'GameID'.

The thing is, when I commented out this line and just did the Update() on
the "Disk" table it worked and the rows
from _both_ of the tables were removed from the Dbase. I`m pleased that it
works but I was wondering if
someone could give a little explanation why it works and why the updating
of
the gameTableAdapter fails.

Most likely, when you do the Update() on games, it tries to delete
record from Games table first, and only then those from Disks. If you
have any records in Disks referencing the one you're trying to delete
from Games, then deletion would result in a foreign key constraint
violation (since you would get a bunch of Disks that reference a non-
existing Game).

Yes, the thing is I actually had it working with the Update() to the Game
table followed by
Update() to Disk table - It compiled and ran but the two row counts at the
bottom of the
method showed that the Game row had been deleted but the Disk one hadn`t.
Odd.
Can anyone point to a difinitive article that shows how to delete rows from
two connected tables. Ive
had a google about but found nothing definate. It`s such a basic requirement
that I thought a
solution would be easy to find. I hate databases!!

mick

 

[Pavel Minaev]

Yes, the thing is I actually had it working with the Update() to the Game
table followed by
Update() to Disk table - It compiled and ran but the two row counts at the
bottom of the
method showed that the Game row had been deleted but the Disk one hadn`t.
Odd.
Can anyone point to a difinitive article that shows how to delete rows from
two connected tables. Ive
had a google about but found nothing definate. It`s such a basic requirement
that I thought a
solution would be easy to find. I hate databases!!

If the database schema is under your control, consider using ON DELETE
CASCADE in your table definition. This will cascade deletion of master
records to all children.