Delete related record

[BirdByte]

I'm working on code that deletes a record in an Access database (Northwind). The delete works if the record isn't related, but doesn't if there are child records. I realize it's probably because the child records must be deleted before the parent record, but being new to ado.net, I'm not sure how to accomplish this and am looking for help.

This is what I've done so far. I'd really like to try to do this using the command object.

Private Sub DeleteCustomer(ByVal myRec as Cust)
Dim sql As String
Dim con As OleDbConnection = (New DataBase).GetConnection
Dim cmd As OleDb.OleDbCommand
 
sql = "delete from customers where customerid = ?"
cmd = con.CreateCommand
cmd.CommandText = sql
cmd.Parameters.Add("CustomerId", Data.OleDb.OleDbType.VarChar,
5).Value = Rec.CustomerId
cmd.Connection.Open()
cmd.ExecuteNonQuery()
cmd.Connection.Close()
End Sub

Somewhere in here I know I should check for and delete child tables, but I'm not sure how. Someone told me all I had to do was include the other tables (Oredrs; OrderDetails) in a select statement, but that didn't work (or make much sense to me).

Thanks for any help and ideas.

[Ammar Gaffar]

Hi BirdByte.
This is a database wise not code, you can do it in code but it is better to let the database itself do it.
Sure there is relation between Orders and OrdersDetail (one to many), so just open the Relationships window in Access database then right click the relation between Orders and OrderDetails then Check the Inforce Relation Integrity the tow options should be enabled Cascade Update Related Field and Cascade Delete Related Records the second Option if you checked the database will delete the child records when you perform delete procedure then you dont need any additional code.

It is a business logic matter , because if One Order has 20 Details , and the user press Delete in hurry without reading the confirmation message then the user will lose 21 records with no rollback and it is hard to re-enter it again, so it depends on your software.
Sometimes Cascade Delete is usfull, and also many times it will cause a confuse for the end user.

hope I answered your question

Good Luck