Delete From Group

[ADO DOT NET]

Hi,

I have an Access database which has 2 tables named "Groups" and "Users".

 

The snap shot shows the contents of both!

 

I use this code to delete from Groups Table:

       Dim DB As OleDb.OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + My.Application.Info.DirectoryPath + "\AccessDB.mdb;Jet OLEDB:Database Password = MyPassword")
       DB.Open()
       Dim DeleteGroup As New OleDb.OleDbCommand("DELETE FROM Groups WHERE GroupName = '" + GroupTextBox.Text + "'", DB)
       DeleteGroup.ExecuteNonQuery()
       DB.Close()

That's OK and have no problem!

My problem is that:

 

As you see in the picture, in the Groups Table, each group has a unique ID which will be automatically assigned by the system.

When I add a user information to the Users Table, I copy that auto numbered ID from Groups table to Users Table under G-ID.

 

I want that when I run that code to delete a group all users assosicated with the same ID be deleted also (If Groups ID = Users G-ID)

 

Something like this:

       Dim DB As OleDb.OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + My.Application.Info.DirectoryPath + "\AccessDB.mdb;Jet OLEDB:Database Password = MyPassword")
       DB.Open()
       Dim DeleteGroup As New OleDb.OleDbCommand("DELETE FROM Groups WHERE GroupName = '" + GroupTextBox.Text + "'", DB)
       DeleteGroup.ExecuteNonQuery()
       Dim DeleteRecipient As New OleDb.OleDbCommand("DELETE FROM Users WHERE G-ID = '" + GroupID + "'", DB)
       DeleteRecipient.ExecuteNonQuery()
       DB.Close()

 

I cannot figure this because I am new in ADO DOT NET! :)

Can you please help me?!

[Nerseus]

Two options, depending on your preference:

1. Setup a relationship between your two tables - delete from the parent (Group) and it will cascade the delete down for you.

2. Manually delete from child tables first (User), then from the parent (Group).

 

Where I work, we use SQL server and set up our foreign keys to enforce parent/child relationships but we do not turn on cascading (for updates, deletes, etc.). We use option 2 as it makes us aware of what we're deleting when we want to physically delete.

 

For #1, go to Tools->Relationships to open the designer. Create the link there to turn on the cascading deletes.

 

For #2, just execute the "DELETE FROM Users ..." first.

 

Unsolicited Advice:

In either case, I would highly recommend deleting from Groups by GID instead of GroupName. You said you had AutoNumber turned on so you're guaranteed to get only one group. If you go in by name, there's a chance you may hit more than one. Besides, the ID seems more natural to me when referencing exact rows.

 

-ner

[amir100]

Two options, depending on your preference:

1. Setup a relationship between your two tables - delete from the parent (Group) and it will cascade the delete down for you.

2. Manually delete from child tables first (User), then from the parent (Group).

 

I'd go for 1. Less code, more integrity. :D