如何删除具有外键约束的记录到另一个表？ [英] How to delete a record with a foreign key constraint to another table?
问题描述
当我试图从datagridview删除行参考表Famille，我得到这个错误是：
未处理的异常类型' System.Data.SqlClient.SqlException'
发生在System.Data.dll中附加信息：DELETE
语句与REFERENCE约束
FK_NatureCharge_Famille冲突。冲突发生在数据库
Tresorerie，表dbo.NatureCharge，列'IdFam'。语句
已终止。
这个表（Famille和NatureCahrge）
顺便说一下，Famille部分可以删除datagridview中的多行。
我试图添加删除NaturCharge 1（它从注释删除自然开始）
代码：
private void DeleteFamBtn_Click（object sender，EventArgs e）
{
List< DataGridViewRow> selectedRows =（来自DataGridViewFamille.Rows.Cast< DataGridViewRow>中的行）
其中Convert.ToBoolean（row.Cells [checkBoxColumn]。Value）== true
select row） ;
if（MessageBox.Show（string.Format（Do you want to delete {0} rows？，selectedRows.Count），Confirmation，MessageBoxButtons.YesNo）== DialogResult.Yes）
{
//删除自然de delete before delete Famille
foreach（selectedRows中的DataGridViewRow行）
{
try
{
using（SqlConnection con = new SqlConnection（connstring））
{
con.Open（）;
using（SqlCommand command = new SqlCommand（DELETE NatureCharge FROM NatureCharge n INNER JOIN Famille f on n.IdFam = f.IdFam WHERE IdNat = @IdNat，con））
{
命令.Parameters.AddWithValue（@ IdFam，row.Cells [IdFam]。Value）;
command.ExecuteNonQuery（）;
}
con.Close（）;
}
}
catch（SystemException ex）
{
MessageBox.Show（string.Format（An error occurred：{0}，ex.Message） ）;
}
}
//删除Famille
foreach（selectedRows中的DataGridViewRow行）
{
using（SqlConnection con = new SqlConnection（connstring））
{
using（SqlCommand cmd = new SqlCommand（DELETE FROM Famille WHERE IdFam = @IdFam，con））
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue（@ IdFam，row.Cells [IdFam]。Value）;
con.Open（）;
cmd.ExecuteNonQuery（）;
con.Close（）;
}
}
}
//更新datagridview
this.BindGrid（）;
}
}
一行显示错误）
`cmd.ExecuteNonQuery（）;``
其他信息：DELETE语句与
REFERENCE冲突约束FK_NatureCharge_Famille。数据库Tresorerie，表dbo.NatureCharge，列IdFam中发生冲突
。
DELETE语句似乎不正确
更新代码
//删除自然de charge before delete Famille
foreach（selectedRows中的DataGridViewRow行）
{
try
{
使用con = new SqlConnection（connstring））
{
con.Open（）;
using（SqlCommand command = new SqlCommand（DELETE NatureCharge FROM NatureCharge n INNER JOIN Famille f on n.IdFam = f.IdFam WHERE IdNat = @IdNat，con））
{
命令.ExecuteNonQuery（）;
}
con.Close（）;
}
}
catch（SystemException ex）
{
MessageBox.Show（string.Format（An error occurred：{0}，ex.Message） ）;
}
}
和相同的错误...
在这种情况下，我建议您在外键操作中使用级联删除，您可以更简化比自己删除相关寄存器
但是如果你仍然想自己做，你可以使用我已经动机的这段代码
< pre class =lang-cs prettyprint-override>
private void DeleteFamBtn_Click（object sender，EventArgs e）
{
List< DataGridViewRow> selectedRows =（来自DataGridViewFamille.Rows.Cast< DataGridViewRow>（）中的行
其中Convert.ToBoolean（row.Cells [checkBoxColumn]。Value）
select row）.ToList（）;
if（MessageBox.Show（string.Format（Do you want to delete {0} rows？，selectedRows.Count），Confirmation，MessageBoxButtons.YesNo）== DialogResult.Yes）
{
//删除自然de删除前Famille
var idsToDelete = selectedRows.Select（row => row.Cells [IdFam]。Value）.ToList （）;
try
{
using（var con = new SqlConnection（connstring））
{
var ids = string.Join（，，idsToDelete）;
使用（var deleteNatures = new SqlCommand（DELETE FROM NatureCharge IdFam IN+ ids，con））
using（var deleteFamilles = new SqlCommand（DELETE FROM Famille WHERE Id IN+ ids，con） ）
{
deleteNatures.Parameters.AddWithValue（@ IdFam，row.Cells [IdFam]。Value）;
con.Open（）;
deleteNatures.ExecuteNonQuery（）;
deleteFamilles.ExecuteNonQuery（）;
}
con.Close（）;
}
}
catch（SystemException ex）
{
MessageBox.Show（string.Format（An error occurred：{0}，ex.Message） ）;
}
//更新datagridview
this.BindGrid（）;
}
}
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.
this the tables (Famille and NatureCahrge)
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 :
private void DeleteFamBtn_Click(object sender, EventArgs e)
{
List<DataGridViewRow> selectedRows = (from row in DataGridViewFamille.Rows.Cast<DataGridViewRow>()
where Convert.ToBoolean(row.Cells["checkBoxColumn"].Value) == true
select row).ToList();
if (MessageBox.Show(string.Format("Do you want to delete {0} rows?", selectedRows.Count), "Confirmation", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
//delete Nature de Charge before delete Famille
foreach (DataGridViewRow row in selectedRows)
{
try
{
using (SqlConnection con = new SqlConnection(connstring))
{
con.Open();
using (SqlCommand command = new SqlCommand("DELETE NatureCharge FROM NatureCharge n INNER JOIN Famille f on n.IdFam = f.IdFam WHERE IdNat = @IdNat", con))
{
command.Parameters.AddWithValue("@IdFam", row.Cells["IdFam"].Value);
command.ExecuteNonQuery();
}
con.Close();
}
}
catch (SystemException ex)
{
MessageBox.Show(string.Format("An error occurred: {0}", ex.Message));
}
}
//Delete Famille
foreach (DataGridViewRow row in selectedRows)
{
using (SqlConnection con = new SqlConnection(connstring))
{
using (SqlCommand cmd = new SqlCommand("DELETE FROM Famille WHERE IdFam = @IdFam", con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@IdFam", row.Cells["IdFam"].Value);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
//Update the datagridview
this.BindGrid();
}
}
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
Update Code
//delete Nature de Charge before delete Famille
foreach (DataGridViewRow row in selectedRows)
{
try
{
using (SqlConnection con = new SqlConnection(connstring))
{
con.Open();
using (SqlCommand command = new SqlCommand("DELETE NatureCharge FROM NatureCharge n INNER JOIN Famille f on n.IdFam = f.IdFam WHERE IdNat = @IdNat", con))
{
command.ExecuteNonQuery();
}
con.Close();
}
}
catch (SystemException ex)
{
MessageBox.Show(string.Format("An error occurred: {0}", ex.Message));
}
}
and the same error...
I this case I recomend you to use cascade delete in foreign key action, you can matain your code base more simplified than deleting related registers by yourself.
But if you still want to do it yourself, you can use this piece of code that I have motified
private void DeleteFamBtn_Click(object sender, EventArgs e)
{
List<DataGridViewRow> selectedRows = (from row in DataGridViewFamille.Rows.Cast<DataGridViewRow>()
where Convert.ToBoolean(row.Cells["checkBoxColumn"].Value)
select row).ToList();
if (MessageBox.Show(string.Format("Do you want to delete {0} rows?", selectedRows.Count), "Confirmation", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
//delete Nature de Charge before delete Famille
var idsToDelete = selectedRows.Select(row => row.Cells["IdFam"].Value).ToList();
try
{
using (var con = new SqlConnection(connstring))
{
var ids = string.Join(",", idsToDelete);
using (var deleteNatures = new SqlCommand("DELETE FROM NatureCharge IdFam IN " + ids, con))
using (var deleteFamilles = new SqlCommand("DELETE FROM Famille WHERE Id IN " + ids, con))
{
deleteNatures.Parameters.AddWithValue("@IdFam", row.Cells["IdFam"].Value);
con.Open();
deleteNatures.ExecuteNonQuery();
deleteFamilles.ExecuteNonQuery();
}
con.Close();
}
}
catch (SystemException ex)
{
MessageBox.Show(string.Format("An error occurred: {0}", ex.Message));
}
//Update the datagridview
this.BindGrid();
}
}
这篇关于如何删除具有外键约束的记录到另一个表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
