如何将具有外键约束的记录删除到另一个表中？

问题描述：

当我试图删除datagridview的行见表彩，我得到这个错误是：如何将具有外键约束的记录删除到另一个表中？

类型“System.Data.SqlClient.SqlException” 未处理的异常出现在system.data.dll附加信息：DELETE 声明与参考约束 “FK_NatureCharge_Famille”冲突。冲突发生在数据库 “Tresorerie”，表“dbo.NatureCharge”，列'IdFam'。陈述已被终止。

此表（彩和NatureCahrge）
顺便说一句，在彩的部分可以在DataGridView中删除多行。我尝试添加删除NaturCharge第一（它开始于评论删除自然）
的代码：

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(); 
     } 
    }

错误示出了具有线（它是用于彩部分） `cmd.ExecuteNonQuery（）;` '

附加信息：DELETE语句与参考约束“FK_NatureCharge_Famille”冲突。数据库“Tresorerie”中的发生冲突，表“dbo.NatureCharge”，列'IdFam'。

它看起来DELETE语句似乎不正确

更新代码

//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)); 
       } 
      }

和同样的错误......

您使用了错误的参数，在你第一次删除查询。您的查询文本要求@ IdNat，但您将@ IdFam添加到参数集合.... command.Parameters.AddWithValue（“@ IdFam”，row.Cells [“IdFam”]。Value）; – dbugger

好的，我修改了NatureCharge表中删除记录的代码，参见update – Amin

答

我这种情况下，我建议你使用级联删除外键操作，您可以使您的代码库比您自己删除相关的寄存器更简单。

但是如果你还是想自己做，你可以使用这段代码，我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(); 
    } 
}

答

可以删除所有外通过使用级联在原始表中记录关键记录。

在某些DBMS中，它的外键约束级别设置了一些，而在删除本身的级别上。

答

我的解决办法：之前删除的表，我必须删除所有相关的表与相关。所以我OCDE很简单，我获得选择的DataGridView的IdFam比我删除与同一ID的行中NatureCharge的表

我的代码：

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) 
      { 
       int aa = Convert.ToInt32(row.Cells["IdFam"].Value); 
       conn = new SqlConnection(connstring); 
       conn.Open(); 
       comm = new SqlCommand("DELETE FROM NAtureCharge WHERE IdFam ='" + aa + "'", conn); 
       try 
       { 
        comm.ExecuteNonQuery(); 
        //MessageBox.Show("Deleted..."); 
       } 
       catch (Exception) 
       { 
        MessageBox.Show("Not Deleted"); 
       } 
       finally 
       { 
        conn.Close(); 
       } 
      } 

      //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(); 
     } 
    }

如何将具有外键约束的记录删除到另一个表中？

相关推荐