delete record with foreign key constraint to another table

Med Amin

When I tried to delete row from datagridview refer to table Famille, I get this error is :

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll Additional information: The DELETE statement conflicted with the REFERENCE constraint "FK_NatureCharge_Famille". The conflict occurred in database "Tresorerie", table "dbo.NatureCharge", column 'IdFam'. The statement has been terminated.

Table Famille

IdFam int
NomFam varchar(50)

Table NatureCharge

IdNat int
NomNat varchar(50)

IdFam int

By the way, the Famille part can delete multiple row in datagridview. I tried to add delete the NaturCharge 1st (it begins with comment delete Nature)
The code in the rar file :

an error shows with line (it is for Famille part) `cmd.ExecuteNonQuery();``

Additional information: The DELETE statement conflicted with the REFERENCE constraint "FK_NatureCharge_Famille". The conflict occurred in database "Tresorerie", table "dbo.NatureCharge", column 'IdFam'.

It looks The DELETE statement doesn't seem to be correct

and the same error...

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Francis Susaimichael11y ago
Accepted answer
Your table have foreign key relationship. So in order to delete the row from the child table then you need to delete the respective record in "Master" table then you can able to delete in the child table.

In your case table "NatureCharge" contains the column "IdFam" where it has a foreign key reference with the table "Familee" with the column "IdFam".

In order to solve this:
First delete from the record from the table "NatureCharge".
Second delete the record from the table "Familee".

If you didn't follow the above sequence, you will get the error!

Hope this helps!
+1
Francis Susaimichael11y ago
So your code working fine. If so, accept the reply as answer that helps you. :)
0
Med Amin11y ago
no, :)
0
Francis Susaimichael11y ago
Have you got any error while deleting?
0
Med Amin11y ago
yes, that is the solution : and my code is :

//delete Nature de Charge before delete Famille
foreach (DataGridViewRow row in selectedRows)
{
int IdFamDeletedValue = Convert.ToInt32(row.Cells["IdFam"].Value);
conn = new SqlConnection(connstring);
conn.Open();
comm = new SqlCommand("DELETE FROM NAtureCharge WHERE IdFam ='" + IdFamDeletedValue + "'", conn);
try
{
comm.ExecuteNonQuery();
//MessageBox.Show("Deleted...");
}
catch (Exception)
{
MessageBox.Show("Not Deleted");
}
finally
{
conn.Close();
}
}
0