Click here to Skip to main content
15,867,330 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I am trying to delete rows in a datagridview and remove them from the Table. I select the rows to delete and they delete. when I look at the Table they are still there what am I doing wrong?

C#
private void btn_Delete_Table_Info_Click(object sender, EventArgs e)
        {
            SQLiteCommand cmd = new SQLiteCommand("DELETE FROM Import_Data where Item_Number=" + dataGridView1.Rows[0].Cells[0].Value.ToString() + "", con);
            cmd.ExecuteNonQuery();

            foreach (DataGridViewCell oneCell in dataGridView1.SelectedCells)
            {
                if (oneCell.Selected)
                    dataGridView1.Rows.RemoveAt(oneCell.RowIndex);
            }

            con.Close();
            MessageBox.Show("THE RECORDS HAS BEEN DELETED");


What I have tried:

C#
con.Open();
           SQLiteCommand cmd = new SQLiteCommand("DELETE FROM Import_Data where Item_Number=" + dataGridView1.Rows[0].Cells[0].Value.ToString() + "", con);
           cmd.ExecuteNonQuery();
           dataGridView1.Rows.RemoveAt(dataGridView1.Rows[0].Index);
           con.Close();
           MessageBox.Show("THE RECORDS HAS BEEN DELETED");
Posted
Updated 19-Jun-21 4:47am
Comments
gggustafson 16-Jun-21 14:36pm    
It appears that you updated the datagrid but not the database.

There are two things wrong with that code:
1) The problem you have noticed, which is simple: you delete one row - the top one in your DataGridView - from the DB, and then loop to delete the selected rows from the DGV without affecting the DB at all.

2) The serious problem, which is that users can take complete control of your database and do exactly what they want with it. Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
SQL
DROP TABLE MyTable;
A perfectly valid "delete the table" command
SQL
--'
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?
 
Share this answer
 
Comments
Member 12349103 16-Jun-21 16:38pm    
OriginalGriff
The table will be deleted everyday and replaced with new data. I am using Dictionary to input new data in another form.
OriginalGriff 16-Jun-21 16:41pm    
So? you code still only deletes one line from the DB ...
And SQL injection has to do with concatenating strings, it doesn't matter what the source of the strings is.
OriginalGriff

That good information I saw another post you made How to delete a SQL table record using C# & SQL WHERE clause[^] That lead me to below.

Thank You

This was my solution

C#
private void btn_Delete_Table_Info_Click(object sender, EventArgs e)
       {
           SQLiteConnection con = new SQLiteConnection("Data Source = Document.db; Version=3");

           string sqlStatement = "DELETE FROM Import_Data";

           try
           {
               con.Open();
               SQLiteCommand cmd = new SQLiteCommand(sqlStatement, con);
               cmd.CommandType = CommandType.Text;
               cmd.ExecuteNonQuery();

           }
           finally
           {
               con.Close();
               MessageBox.Show("Successful Deleted", "Data Delete", MessageBoxButtons.OK, MessageBoxIcon.Information);
           }
 
Share this answer
 

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