TableAdapter cannot delete record because of related records

I have an application that uses an Access database and the database has a PATIENTS table and a DOCTORS table. Of course, the PATIENTS table has a foreign key relationship with the DOCTORS table to identify who the patients doctor is.

Now, when my application starts I'm pulling in the DOCTORS table using a TableAdapter and BindingSource (TableAdapter.Fill(BindingSource)) and I'm also pulling in the PATIENTS data but the patients data is coming in as a query that joins the doctor. This all works pretty good since I can view, add, and edit data.

Now, my problem is with deleting. If I add a brand new record to DOCTORS and then try to delete this record I get a message: "The record cannot be deleted or changed because table 'PATIENTS' includes related records."

Why do I get this message? I understand there is a relationship with these tables but there shouldn't be any related patient data for a brand new doctor.

Also, I am actually able to update a doctor record (change his name) but it is not reflected in the patient data unless I refill the binding source; is there a way for this to update automatically?
Who is Participating?
Hi! In your access database, modify the relationship of those tables.  Remove the cascade effect.
> "The record cannot be deleted or changed because table 'PATIENTS' includes related records."

Have you looked in your database which patients are related?
hmstechsupportAuthor Commented:
Thanks for the replies.

drypz: There is no cascading effect in the database relationship. However, there is a relationship with "Enforce Referential Integrity", relationship type "One-To-Many". Looks good to me.

sybe: There are no patient records related to the record I'm trying to delete becaue I just added the doctor record. I did check the database just to make sure and there is absolutely no related records.
hmstechsupportAuthor Commented:
So I've managed to move past this issue, the problem was that I was using the wrong BindingSource so it wasn't trying to remove the right record, DUH on my part.

Thanks for the replies, partial rewards for everyone!
hmstechsupportAuthor Commented:
Responses were good but in the end the solution was to fix my bad coding.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.