How to enable VB to delete rows in a relational database?

[lithium1976]

Hello, I am trying to learn VB.net for simple programming and database functionality.

I have been working through some online tutorials at www.homeandlearn.co.uk and have used their visual basic code for adding, updating and deleting rows from their example database.

The homeandlearn.co.uk example is a single-table database.

The code for their "delete" button is -

If MessageBox.Show("Do you really want to Delete this Record?", "Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = DialogResult.No Then
MsgBox("Operation Cancelled")
Exit Sub

End If

Dim cb As New OleDb.OleDbCommandBuilder(da)

ds.Tables("AddressBook").Rows(inc).Delete()
MaxRows = MaxRows - 1

inc = 0
NavigateRecords()
da.Update(ds, "AddressBook")

My question is - if the database was relational, ie had another table related to the table "addressbook", how do I alter the above code to execute a delete of related data in both of the tables?

If for example I had another table called "User" which was related to "addressbook"...

[Tom Sawyer]

The simplest way to do this would be through the database.

When you set up the foreign key relationships, click the box that says "Cascade Delete Related Records". That way when you delete the record from the main table, all the records in the other tables that are linked to that key will be deleted as well.

[GaryMazzone]

I would never allow cascade delete (or updates) on the database side (sorry I'm a DBA and never allow them in a database I work on). You need display a message to the user that there are records relating to this item in the database and ask if they want to delete all records associated with this item. If they answer Yes then you code the delete for all releated records in any and all tables that it applies to.

[lithium1976]

The database will be designed in SQL Server.

I have used cascading deletes in Access before, but I am new to using SQL Server, so where do I go to select cascading deletes in sql server?

How do I alter the vb code I posted in the original message to delete from related tables say one table called "AddressBook" and the related rows in a table called "Users"?

[Tom Sawyer]

To set it up in SQL Server, go to Design Table and select Manage Relationships. It's a checkbox there.

If you want to follow Gary's advice and not use them, you're best to use a stored procedure for your delete and put in all the related rows manually.