0

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
    {
    }
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
maximdj
  • 315
  • 1
  • 12
  • 1
    You should see the details of the exception to see what's wrong. – clinomaniac Feb 09 '18 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 Feb 09 '18 at 20:40
  • 1
    Change this part to print details of the exception... catch (Exception ex) { Response.Redirect("somwhere"); } – clinomaniac Feb 09 '18 at 20:42
  • yes I know but how to a show the exception? I tried ex.toString(); That didn't work. – maximdj Feb 09 '18 at 20:52
  • What's the output if you put this: `Console.WriteLine(ex.ToString());`? – clinomaniac Feb 09 '18 at 20:54
  • Also..edit the question to include the correct tags. – clinomaniac Feb 09 '18 at 20:55
  • doesn't do anything. The strange part is it worked once but never again. – maximdj Feb 09 '18 at 20:59
  • How do you know it catches the exception if that line doesn't print anything? Might be a stupid question but does the id still exist? If you say it worked once then probably the id got deleted and doesn't have anything to delete again. Seems trivial but need to confirm since it is a possible scenario. – clinomaniac Feb 09 '18 at 21:02
  • [SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection - check out [Little Bobby Tables](https://xkcd.com/327/) – marc_s Feb 09 '18 at 21:34
  • hi yes I know and will add parameters however I am stuck, I cannot understand why this isn't working. It makes no sense. If I put a completely incorrect SQL statement with a fictional table it still does nothing, even if I remove the try statement.. It is like it doesn't even execute the query. I know it is running the code because if I put a redirect after the m_dbConnection.Open it redirects. I am very confused, I have done this many times before but never have had an issue quite like this. – maximdj Feb 09 '18 at 22:06
  • Can you access the database to run the query? Are their constraint restrictions preventing the DELETE? If the query executes, it may be that the ConnString is not correct in any number of ways. This may not solve your problem but maybe it can help you isolate it. – user7396598 Feb 09 '18 at 22:12
  • I can access SQL Managment studio as the user and I can delete a record in there. I wondered if it were permissions however I can insert and update via the .net page. As I mentioned also for some reason it worked once, I deleted one record but then never worked after that. – maximdj Feb 09 '18 at 22:17
  • ok I might be getting somwhere, when I ran the query manually in SQL Studio it says: The DELETE statement conflicted with the REFERENCE constraint "FK_tblConversations_tblStudent". The conflict occurred in database "bps", table "dbo.tblConversations", column 'studentID'. – maximdj Feb 09 '18 at 22:33
  • I dont know but I guess the time it worked was because there were no conversations for the user. – maximdj Feb 09 '18 at 22:38

1 Answers1

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

    }
}
maximdj
  • 315
  • 1
  • 12
  • 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: https://stackoverflow.com/questions/6260688/how-do-i-use-cascade-delete-with-sql-server – user7396598 Feb 09 '18 at 23:29