Click here to Skip to main content
14,870,416 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
In my WinForms app I need to delete selected rows from data grid view and also from my database at the same time and save the database.

I have this below code where now it is only deleting from datagridview but not from database, kindly guide me where I am wrong.

What I have tried:

private void button1_Click(object sender, EventArgs e)
{
    try
    {
        String msg = "Confirm Delete?";
        String caption = "Delete Record";
        MessageBoxButtons buttons = MessageBoxButtons.YesNo;
        MessageBoxIcon ico = MessageBoxIcon.Question;
        DialogResult result;
        result = MessageBox.Show(this, msg, caption, buttons, ico);
        if (result == DialogResult.Yes)
        {
            foreach (DataGridViewRow item in this.iP_SpoolsDataGridView.SelectedRows)
            {
                using (SqlConnection con = new SqlConnection(cs))
                {
                    SqlCommand cmd = con.CreateCommand();
                    int id = Convert.ToInt32(iP_SpoolsDataGridView.SelectedRows[0].Cells[0].Value);
                    cmd.CommandText = "Delete from Lot_Numbers where ID='" + id + "'";

                    iP_SpoolsDataGridView.Rows.RemoveAt(this.iP_SpoolsDataGridView.SelectedRows[0].Index);
                    con.Open();
                    cmd.ExecuteNonQuery();

                }

            }
        }
        else
        {
            return;
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show("Deleting Failed:" + ex.Message.ToString(), "Delete",
            MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
Posted
Updated 16-Dec-20 18:42pm
Comments
Richard Deeming 17-Dec-20 3:51am
   
cmd.CommandText = "Delete from Lot_Numbers where ID='" + id + "'";

Don't do it like that!

Whilst in this specific case you're probably OK, since id is an int, using string concatenation to build a SQL query can and will leave you vulnerable to SQL Injection[^].

Always use parameters:
cmd.CommandText = "Delete from Lot_Numbers where ID = @id";
cmd.Parameters.AddWithValue("@id", id);


Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

1 solution

I would
1) debug the result of getting the 'id' to make sure it's valid
2) use a sql client and manually issue the delete command you're putting together, but I'd also change from Lot_Numbers to the fully qualified form eg db.table
3) If this doesnt work, verify id's, table name, permissions etc

then

you're doing a lot of work opening the connection every time in the loop (and you dont close it) .. why don't you add your id's from the loop to a list/array, then build a delete statement of the form

SQL
delete from  db.table where id in (1,2,3,4,6);


and execute the delete statement once at the end of the loop
   
Comments
Member 14898617 17-Dec-20 0:53am
   
Thank you so much for your suggestion ..i have closed the connection and inserted and refresh statement after delete ..now it is working

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900