Delete a row from a MySQL database only if certain conditions are met

Question

I'm currently working on a simple windows form bug reporter for university and am having trouble. I'm trying to create a query where the user can only delete the bug if:

The bug name exists
The user logged in matches the user that reported the bug.

Currently no matter which use is logged in, the query always returns 'Incorrect User Logged In!' and doesn't delete the bug.

I am a novice at both C# and MySQL, so I'm sure my code isn't the best way of writing it. I apologize if it is hard to read.

EDIT

Here is my current code based on the below answer which still doesnt work. I currently get could not find specified colum in results: bug_submitted_by

 connection = new MySqlConnection(connectionString);
                string check = "SELECT COUNT(*) FROM bugs WHERE bug_name ='" + this.txt_bug_name.Text + "'AND bug_submitted_by='" + this.lbl_user.Text + "';";
                MySqlCommand cmd = new MySqlCommand(check, connection);
                MySqlDataReader reader;
                connection.Open();
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {

                    if (reader.GetString("bug_submitted_by").Equals(this.lbl_user.Text))
                    {
                        reader.Close();
                        cmd.Dispose();
                        string query = "DELETE from bugs WHERE bug_name='" + this.txt_bug_name.Text + "';";
                        MySqlCommand cmd2 = new MySqlCommand(query, connection);
                        MySqlDataReader reader2;
                        reader2 = cmd2.ExecuteReader();
                        lbl_message.Text = "Bug Deleted!";
                        reader2.Close();
                        cmd2.Dispose();
                        connection.Close();
                        load_table();
                        Combo_selection();
                        reset(this);

                    }
                    else
                    {
                        lbl_message.Text = "Incorrect user!";
                        reader.Close();
                        cmd.Dispose();
                        connection.Close();
                        cb_names.SelectedIndex = -1;
                    }
                }
                else
                {
                    lbl_message.Text = "Bug Doesn't Exist!";
                }

I am not C# programmer. But have you tried to check your second query directly in your database? Just to ensure if the record is exist or no duplicate record there. — alisa, Jan 04 '15 at 18:17
Yes I'm certain. Previously deleting checking only the bug name worked so i know its not a database issue — Calv, Jan 04 '15 at 18:22
I think your logic might be slightly wrong in the top query. You're checking if there's a row with that bug name, and then checking if there's a row submitted_by the current user, but you're not checking the two of them are the same row, are you? — AdamMc331, Jan 04 '15 at 18:37
@adam don't edit your question to remove the actual question once you get an answer — Taryn, Jan 04 '15 at 21:57

score 1 · Accepted Answer · edited May 23 '17 at 12:05

As a side note first, I recommend you look into parameterized queries in C#. I will use that sort of syntax in my answer, but I believe it will be easy enough for you to understand. Given that you are trying to do multiple things (check if bug exists, check if bug was written by that user, delete that bug if condition is met) I recommend you break it up into pieces and put them all back together.

To start, we can just write a query to see if a bug exists. We can be smart and select both the bug name, and who it was submitted by:

using(var conn = new MySqlConnection(ConnectionString)
{
    conn.Open();
    using(var cmd = new MySqlCommand())
    {
        cmd.Connection = conn;
        cmd.CommandText = "SELECT bug_name, bug_submitted_by FROM bugs WHERE bug_name = @name";
        cmd.Parameters.AddWithValue("@name", this.txt_bug_name.Text);
        using(var reader = cmd.ExecuteReader())
        {
            if(reader.Read())
            {
                // Will fill in next
            }
        }
    }
}

Side note: If you're unfamiliar with using, see this question for some insight. This is so you don't have to worry about disposing the objects, they will be disposed once you leave scope of the using block.

So, the above query pulls the row from the table where the bug name matches the input. The inner if statement is used to make sure a row was returned. If it was, check the username and react accordingly. If it doesn't, react accordingly like this:

using(var conn = new MySqlConnection(ConnectionString)
{
    conn.Open();
    using(var cmd = new MySqlCommand())
    {
        cmd.Connection = conn;
        cmd.CommandText = "SELECT bug_name, bug_submitted_by FROM bugs WHERE bug_name = @name";
        cmd.Parameters.AddWithValue("@name", this.txt_bug_name.Text);
        using(var reader = cmd.ExecuteReader())
        {
            if(reader.Read())
            {
                // We got a row
                if(reader.GetString("bug_submitted_by").Equals(this.lbl_user.Text))
                {
                    // If the username matches delete it
                    using(var cmd2 = new MySqlCommand())
                    {
                        cmd2.Connection = conn;
                        cmd2.CommandText = "DELETE FROM bugs WHERE bug_name = @name";
                        cmd2.Parameters.AddWithValue("@name", this.txt_bug_name.Text);
                        cmd2.ExecuteNonQuery();
                        lbl_message.Text = "Bug Deleted!";
                    }
                }
                else
                {
                    // Username doesn't match
                    lbl_message.Text = "Incorrect user!";
                }
            }
            else
            {
                // We didn't get a row
                lbl_message = "Bug Doesn't Exist!";
            }
        }
    }
}

What this query does not do is protect against the situation where a specific bug name appears twice. If your database restricts that, then you're good. If it doesn't, you'll need to implement some check to make sure you only got one row back.

Sorry for the late reply. Thank you for the advice and Il certainly look in to parameterized queries and using. At the minute though I've tried adapting your answer to fit my code instead of just using yours even though yours is better. I don't want to outright copy yours then my tutor asking me to explain it — Calv, Jan 04 '15 at 18:57
Of course, I understand. However, if you find yourself with some extra time and you can research it and if you *are* able to explain it, I think it would be worth your time. — AdamMc331, Jan 04 '15 at 19:50

score 1 · Answer 2 · answered Jan 04 '15 at 20:22

Please don't hit database with subsequent requests. Not only this unnecessary but may also be misleading because a lot may happen between two calls that you make in a multiuser environment. Another session may already updated or deletes this row.

You can do it all in one go

DELETE 
  FROM bugs
 WHERE bug_name = 'bug1'
   AND bug_submitted_by = 'John';

Here is a SQLFiddle demo

Delete a row from a MySQL database only if certain conditions are met

2 Answers2