Deleting a row from DataGridView and SQL Server database

Question

I am trying to delete a row of data in DataGridView and SQL Server database. On the first click on the delete button it shows

MessageBox.Show("Record Deleted Successfully!");

but the selected row was not deleted in DataGridView and in the database.

This is my code:

private void PayratesDisplay()
{
    con.Open();

    DataTable dt = new DataTable();
    adap = new SqlDataAdapter("select Membership AS [Membership Type], PerSession AS [Per Session], Discounted from tbl_payrates", con);

    adap.Fill(dt);

    dataGridView4.DataSource = dt;

    con.Close();
}

// Clear Data  
private void ClearData()
{
    tbMemship.Text = "";
    tbPerses.Text = "";
    tbDisc.Text = "";
    ID = 0;
}

private void btn_PRdel_Click(object sender, EventArgs e)
{
    if (ID != 0)
    {
        con.Open();

        cmd = new SqlCommand("DELETE FROM tbl_payrates WHERE Membership = @Membership", con);
        cmd.Parameters.AddWithValue("@Membership", ID);

        cmd.ExecuteNonQuery();

        con.Close();

        MessageBox.Show("Record Deleted Successfully!");

        PayratesDisplay();
        ClearData();
    }
}

i declared the in the partial class Load : Form int ID = 1; — Josh
– Josh, Commented Oct 1, 2015 at 3:38
So you're always deleting values where Membership = 1? When you click the delete button, does the ID have the correct value? — Felix Pamittan
– Felix Pamittan, Commented Oct 1, 2015 at 3:39
What integer value do you get in return from cmd.ExecuteNonQuery()? If you are getting 0 that means there are no rows affected by your delete query. — Nikhil Vartak
– Nikhil Vartak, Commented Oct 1, 2015 at 3:40
@FelixPamittan i want to delete the selected row with Membership=any value do i need to change int ID =1; to ID=0? — Josh
– Josh, Commented Oct 1, 2015 at 3:43

Ivan Stoev · Accepted Answer · 2015-10-01 09:59:09Z

1

If I understand correctly, you are trying to delete the current row of the grid. Then the ID member is not needed and you can use something like this

private void btn_PRdel_Click(object sender, EventArgs e)
{
    var row = dataGridView4.CurrentRow;
    if (row == null) return;
    var ID = row.Cells["Membership Type"].Value;
    // The rest of the code (w/o the "if ID != 0")
}

edited Oct 1, 2015 at 9:59

answered Oct 1, 2015 at 6:45

Ivan Stoev

208k17 gold badges356 silver badges376 bronze badges

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

5 Comments

Josh Over a year ago

int ID = (int)row.Cells["Membership Type"].Value; this line has an error which is InvalidCastException

Ivan Stoev Over a year ago

@Josh Hmm, what it is then - string? Let me update the answer. But that's what you are trying to do, right?

Josh Over a year ago

I am deleting a row with integers in it

Ivan Stoev Over a year ago

@Josh Hmm, that couldn't be true - both select and delete sql statements operate on tbl_payrates and the delete is filtering by Membership column, which also is included in the grid (just with alias), so its either an int and the original cast should succeed or not an int.

Josh Over a year ago

the column Discounted is in varchar(50)

Mainak · Accepted Answer · 2015-10-01 13:17:50Z

0

Change your code like this

int r=cmd.ExecuteNonQuery();
con.Close();
if(r>0){MessageBox.Show("Record Deleted Successfully!");}

answered Oct 1, 2015 at 13:17

Mainak

4683 gold badges10 silver badges33 bronze badges

Collectives™ on Stack Overflow

Deleting a row from DataGridView and SQL Server database

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related