How can i delete data from a datagridview c#?

Question

When i use the code below,it does delete a row from a datagridview but when i refresh the page it does not..

    private void DeleteData_Click(object sender, EventArgs e)
    {

        foreach (DataGridViewRow row in dataGridView1.Rows)
        {
            if (row.Selected)
            {
                dataGridView1.Rows.RemoveAt(row.Index);
                break; 
        }
        using (SqlConnection sqcon = new SqlConnection(@"MY CONNECTION STRING")) 
        {
            SqlCommand Scmd = new SqlCommand();

            for (int i = 0; i < dataGridView1.Rows.Count; i++)
            {
                DataGridViewRow delrow = dataGridView1.Rows[i];
                if (delrow.Selected == true)
                {
                    dataGridView1.Rows.RemoveAt(i);
                    try
                    {
                        Scmd.CommandText = "DELETE FROM Technican WHERE ID=" + (i++) + "";
                        sqcon.Open(); //ADDED
                        int count = Scmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.ToString());
                    }
                }
            }
        }

    }

What should i use to delete row from both local database and datagridview?

Depending on how you are binding DataGridView to data source the solution can differ. Simple logic would be to delete data from the database and rebind the DataGridView to the datasource. — Chetan, Apr 28 '18 at 13:27
Looks like the DGV is not databound? So you need to debug the sql; is the name really a number?? What do get for count? And why do hide the exception??? Never do that! Add a Console.WrtieLine(ex.Message) or similar in the catch!!! — TaW, Apr 28 '18 at 14:47
thanks,now i realized that name in my case is not a nr. my mistake. how to start the count from the "Name",bcs my datatable does not have an numerical ID? any suggestion for my appropriate c# code? — blscorp, Apr 28 '18 at 15:45
@blscorp If you don't have an identity column in each of your tables on your data base, maybe you should consider a redesign on your DB. If they do have an identity column, change your query so it contains the ID of the row — Ivan García Topete, Apr 28 '18 at 16:05
@blscorp You should also take a look at [parameterized queries](https://stackoverflow.com/q/7505808/9453080) — Ivan García Topete, Apr 28 '18 at 16:11
Then show us the resulting sql! And do you get a message? You don't really need a numerical ID but you do need a unique key column. - And why oh why do I have to ask these questions? Why don't provide us with a helpful description of the current state of affairs?? _still not working_ is not helpful at all!! — TaW, Apr 28 '18 at 18:41

score 0 · Answer 1 · edited Apr 28 '18 at 20:25

0

Try this, Delete from database use the unique column or the Id column.

//Make datagridview cell[0] the unique column in your table.
try
{
    Scmd.CommandText = "DELETE FROM Technican WHERE ID='" + datagridview.rows[i].cell[0].value + "'";
    sqcon.Open(); //ADDED
    int count = Scmd.ExecuteNonQuery();
}
catch (Exception ex)
{
    MessageBox.Show(ex.ToString());
}

edited Apr 28 '18 at 20:25

ferc

560
6
21

answered Apr 28 '18 at 20:16

Peter

111
1
1
7

Baskar John · Accepted Answer · 2018-04-30T06:50:52.037

i think the connection not be assigned to the command object.

SqlCommand Scmd = new SqlCommand();
Scmd.Connection = sqcon;

also i preferred to use DataGridView.SelectedRows instead of looping all records in grid.

full code

    private void DeleteData_Click(object sender, EventArgs e)
    {
        var rowsToDelete = dataGridView1.SelectedRows;
        using (SqlConnection sqcon = new SqlConnection(@"MY CONNECTION STRING"))
        {
            SqlCommand Scmd = new SqlCommand();
            Scmd.Connection = sqcon;
            sqcon.Open(); //ADDED   
            foreach (DataGridViewRow row in rowsToDelete)
            {
                try
                {
                    Scmd.CommandText = "DELETE FROM Technican WHERE ID=" + row.Cells["Id"].Value.ToString() + "";
                    int count = Scmd.ExecuteNonQuery();

                    dataGridView1.Rows.Remove(row);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }

            }
            sqcon.Close();
        }
    }

How can i delete data from a datagridview c#?

2 Answers2