使用C#在SQL Server上进行SQL删除时遇到问题

时间:2021-02-17 03:39:29

I am running this code but it throws an exception and I am not sure why. Any help would be appreciated. I checked the ID and it was the right ID for the record.

我正在运行此代码,但它抛出异常,我不知道为什么。任何帮助,将不胜感激。我检查了ID,它是记录的正确ID。

protected void DeleteSQLDB(int id)
{
    String ConnString = GetConnectAccess();

    try       
    {
        using (SqlConnection m_dbConnection = new SqlConnection(ConnString))
        {
            String sql = "DELETE FROM tblStudent WHERE ID=" + id;

            using (SqlCommand cmd = new SqlCommand(sql, m_dbConnection))
            {
                m_dbConnection.Open();
                cmd.ExecuteNonQuery();
                m_dbConnection.Close();
            }
        }
    }
    catch (Exception ex)
    {
        Response.Redirect("somwhere");
    }
    finally
    {
    }
}

1 个解决方案

#1


1  

I solved the problem. The reason was that the record was referenced in other tables so I had to remove the references before I could remove the record. Thanks user7396598 for advice about running query manually. This is the code which removes the conversations first and then the student record:

我解决了这个问题。原因是该记录在其他表中被引用,所以我必须删除引用才能删除记录。感谢user7396598获取有关手动运行查询的建议。这是首先删除对话然后删除学生记录的代码:

 //This deletes the archived student, First any conversations need to be deleted before the record can be removed.
protected void DeleteSQLDB(object id,String studentID)
{
  //  Response.Redirect(studentID);

    String ConnString = GetConnectAccess();
    try
    {
        using (SqlConnection m_dbConnection = new SqlConnection(ConnString))
        {

            String sql = "DELETE FROM tblConversations WHERE StudentID=@studentID";

            using (SqlCommand cmd = new SqlCommand(sql, m_dbConnection))
            {
                cmd.Parameters.AddWithValue("@studentID", studentID);
                m_dbConnection.Open();
                cmd.ExecuteNonQuery();



            }


        }


    }
    catch (Exception ex)
    {

    }

    finally
    {
        DeleteSQLDB2(id);




    }
}

protected void DeleteSQLDB2(object id)
{
    //  Response.Redirect(studentID);

    String ConnString = GetConnectAccess();
    try
    {
        using (SqlConnection m_dbConnection = new SqlConnection(ConnString))
        {
            String sql = "DELETE FROM tblStudent WHERE ID=@ID";


            using (SqlCommand cmd = new SqlCommand(sql, m_dbConnection))
            {
                cmd.Parameters.AddWithValue("@ID", id);
                m_dbConnection.Open();
                cmd.ExecuteNonQuery();



            }


        }


    }
    catch (Exception ex)
    {

    }

    finally
    {


        Response.Redirect("studentgrid.aspx");

    }
}

#1


1  

I solved the problem. The reason was that the record was referenced in other tables so I had to remove the references before I could remove the record. Thanks user7396598 for advice about running query manually. This is the code which removes the conversations first and then the student record:

我解决了这个问题。原因是该记录在其他表中被引用,所以我必须删除引用才能删除记录。感谢user7396598获取有关手动运行查询的建议。这是首先删除对话然后删除学生记录的代码:

 //This deletes the archived student, First any conversations need to be deleted before the record can be removed.
protected void DeleteSQLDB(object id,String studentID)
{
  //  Response.Redirect(studentID);

    String ConnString = GetConnectAccess();
    try
    {
        using (SqlConnection m_dbConnection = new SqlConnection(ConnString))
        {

            String sql = "DELETE FROM tblConversations WHERE StudentID=@studentID";

            using (SqlCommand cmd = new SqlCommand(sql, m_dbConnection))
            {
                cmd.Parameters.AddWithValue("@studentID", studentID);
                m_dbConnection.Open();
                cmd.ExecuteNonQuery();



            }


        }


    }
    catch (Exception ex)
    {

    }

    finally
    {
        DeleteSQLDB2(id);




    }
}

protected void DeleteSQLDB2(object id)
{
    //  Response.Redirect(studentID);

    String ConnString = GetConnectAccess();
    try
    {
        using (SqlConnection m_dbConnection = new SqlConnection(ConnString))
        {
            String sql = "DELETE FROM tblStudent WHERE ID=@ID";


            using (SqlCommand cmd = new SqlCommand(sql, m_dbConnection))
            {
                cmd.Parameters.AddWithValue("@ID", id);
                m_dbConnection.Open();
                cmd.ExecuteNonQuery();



            }


        }


    }
    catch (Exception ex)
    {

    }

    finally
    {


        Response.Redirect("studentgrid.aspx");

    }
}