Delete Button SQL Database Windows Form Application

Question

How can I modify my delete button code to delete a selected row from my SQL database? Currently when I select one row and click the delete button all the rows are deleted.

private void delete_button1_Click_1(object sender, EventArgs e)
    {
        if (dataGridView1.SelectedRows.Count > 0)
        {
            int selectedIndex = dataGridView1.SelectedRows[0].Index;
            string sqlquery;
            string ConString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
            MySqlConnection con = new MySqlConnection(ConString);
            con.Open();
            int rowID = int.Parse(dataGridView1[0, selectedIndex].Value.ToString());
            sqlquery = "DELETE FROM hotel_booking WHERE BookingID = BookingID";

            try
            {
                MySqlCommand command = new MySqlCommand(sqlquery, con);
                command.ExecuteNonQuery();
                string CmdString = "SELECT * FROM hotel_booking";
                MySqlDataAdapter sda = new MySqlDataAdapter(CmdString, con);
                DataSet ds = new DataSet();
                sda.Fill(ds);
                dataGridView1.DataSource = ds.Tables[0].DefaultView;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
    }

Your problem is here:sqlquery = "DELETE FROM hotel_booking WHERE BookingID = BookingID".That where part is always true. — Pikoh
– Pikoh, Commented Jun 28, 2016 at 14:12

Hynek Bernard · Accepted Answer · 2016-06-28 14:14:27Z

2

Do not try to run this query, it will delete all of your rows

DELETE FROM hotel_booking WHERE BookingID = BookingID

BookingID = BookingID means it is always true

I think you are trying to use

int rowID = int.Parse(dataGridView1[0, selectedIndex].Value.ToString());
sqlquery = "DELETE FROM hotel_booking WHERE BookingID = "+ rowID;

answered Jun 28, 2016 at 14:14

Hynek Bernard

7461 gold badge12 silver badges32 bronze badges

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

8 Comments

Pikoh Over a year ago

Sorry but this is not acceptable. You MUST use parametrized queries to avoid Sql Injection

Hynek Bernard Over a year ago

I think there is no possible SQL injection, because you parse index of selected item and it is saved in integer. And numerical index can't be used to inject any Queries, can it ? If I am mistaken, then I am terribly sorry, but please prove me wrong by some facts. @Pikoh

Pikoh Over a year ago

Maybe in this particular case a SQL Injection is not very likely, but we all must get used to always use parametrized queries. This is even more important when dealing with newbies that doesn't understand when SQL Injection is likely, so we must teach them to use parametrized queries in al cases.

Hynek Bernard Over a year ago

No problem :), but as @Pikoh said, you should not use this example. In this case it is ok to make the query look like this, but in other cases you should use his solution with parametrized query.

Pikoh Over a year ago

@RossH yes,it works but be very careful, you should use Parametrized queries. BTW, in my answer there was another typo, that's why it doesn't worked for you.

|

René Vogt · Accepted Answer · 2016-06-28 14:14:46Z

You are currently deleting all rows because of a mistake in your WHERE clause:

DELETE FROM hotel_booking WHERE BookingID = BookingID

deletes all rows because in each row BookingID equals BookingID.

You want to take rowID as criteria, so add it as a parameter:

int rowID = int.Parse(dataGridView1[0, selectedIndex].Value.ToString());
sqlquery = "DELETE FROM hotel_booking WHERE BookingID = @rid";   
try
{
    MySqlCommand command = new MySqlCommand(sqlquery, con);
    command.Parameters.Add("@rid", SqlDbType.Int).Value = rowID;
    command.ExecuteNonQuery();

Note that inserting user generated values directly into your query (like "WHERE BookingID = " + rowID...) is vulnerable to SQL Injection. Although for numeric types this is not such a big danger, you should generally use parameterized queries as above.

Pikoh · Accepted Answer · 2016-06-28 14:33:30Z

1

Your problem is here:

sqlquery = "DELETE FROM hotel_booking WHERE BookingID = BookingID"

That where part is always true.

Change it to this:

sqlquery = "DELETE FROM hotel_booking WHERE BookingID = @BookingID"
try
        {
            MySqlCommand command = new MySqlCommand(sqlquery, con);
            command.Parameters.AddWithValue("@BookingID", rowID);
            command.ExecuteNonQuery();
            ...

edited Jun 28, 2016 at 14:33

answered Jun 28, 2016 at 14:16

Pikoh

7,72331 silver badges56 bronze badges

4 Comments

RossH Over a year ago

It's now saying rowId does not exist in the current context.

Pikoh Over a year ago

Sorry, a typo. It's rowID, so it should be command.Parameters.AddWithValue("@BookingId", rowID);, i've edited my answer

RossH Over a year ago

Still not working, when I click delete nothing happens at all. But no errors.

Pikoh Over a year ago

Debug your code and inspect what value of rowID is. Maybe is not a value present in the database

Collectives™ on Stack Overflow

Delete Button SQL Database Windows Form Application

3 Answers 3

8 Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related