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.