Having trouble with SQL Delete on SQL Server using C#

Question

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.

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
    {
    }
}

You should see the details of the exception to see what's wrong. — clinomaniac
– clinomaniac, Commented Feb 9, 2018 at 19:32
im editing a file on the internet using notepad and uploading it via FTP so I do not know how to show the exception. :( — maximdj
– maximdj, Commented Feb 9, 2018 at 20:40
Change this part to print details of the exception... catch (Exception ex) { Response.Redirect("somwhere"); } — clinomaniac
– clinomaniac, Commented Feb 9, 2018 at 20:42
yes I know but how to a show the exception? I tried ex.toString(); That didn't work. — maximdj
– maximdj, Commented Feb 9, 2018 at 20:52
What's the output if you put this: Console.WriteLine(ex.ToString());? — clinomaniac
– clinomaniac, Commented Feb 9, 2018 at 20:54

maximdj · Accepted Answer · 2018-02-11 19:02:50Z

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:

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

    }
}

edited Feb 11, 2018 at 19:02

answered Feb 9, 2018 at 23:04

maximdj

3552 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user7396598 Over a year ago

You're welcome. You can either locate the dependent entries, and delete them manually, OR look into CASCADE on delete. Check out this answer for both SSMS and script ways to utilize it: stackoverflow.com/questions/6260688/…

Collectives™ on Stack Overflow

Having trouble with SQL Delete on SQL Server using C#

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related