Click here to Skip to main content
15,746,248 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:

my project has a datagridview with 3 columns that are completed manually by the user and a database table with 100 records

i need to delete from database table the records that are like the first column(Serial) of the datagridview

Serial | Lot  | Date
89985  | T2_4 | 2023-02-03
89993  | T2_4 | 2023-02-03
90009  | T2_4 | 2023-02-03
90017  | T2_4 | 2023-02-03
90025  | T2_4 | 2023-02-03

with the code below I managed to delete only the last (90025 | T2_4 | 2023-02-03) record

What I have tried:

for (int i = 0; i < dvgSeriiNoi.Rows.Count; i++)
                SqlCommand sqlCmd = new SqlCommand("DELETE Articol WHERE Serial = @Serial", con);
                sqlCmd.Parameters.AddWithValue("@Serial", dvgSeriiNoi.Rows[i].Cells["Column1"].Value.ToString());
Updated 20-Feb-23 12:52pm
0x01AA 20-Feb-23 16:10pm    
Apart from that your delete sql is wrong I see no problem. But it is curious that you have success deleting the last row ;)

From my point of view it should be, notice 'FROM' DELETE FROM Articol WHERE Serial = @Serial"
card_maruis 20-Feb-23 16:26pm    
I added FROM and had the same result, only the last line was deleted
0x01AA 20-Feb-23 16:30pm    
MSSQL or MySql or what? Anyway strange that a SQL engine acceppt your delete statement.
0x01AA 20-Feb-23 16:35pm    
Is Serial of type integer or is it char(xyz)? If char, maybe some blanks in the database and the grid does trimm them?
card_maruis 20-Feb-23 16:39pm    
MSSQL, i try to delete from textbox and 1 by 1 is ok when i try from datagridview is deleting only the last row

Apart from, that your delete sql is wrong I see no problem. But it is curious that you have success deleting the last row ;)

The delete statement should be as below, notice 'FROM'
DELETE FROM Articol WHERE Serial = @Serial"

I hope it helps.

Just verified and surprised. At least MSSQL does accept a delete statement like
DELETE <tableName> WHERE field = @param

At least also docs confirm that FROM is optional:DELETE (Transact-SQL) - SQL Server | Microsoft Learn[^]
Share this answer
raddevus 20-Feb-23 16:25pm    
We posted at the same time. :) Took me a minute to see that the from was missing.
0x01AA 20-Feb-23 16:27pm    
have my five ;)
but still strange that the last record has been deleted :-)
card_maruis 21-Feb-23 5:24am    
last night I found the problem to transfer the data to the datagridview, I used copy/paste with the following code and I think it does not validate the cells, i insert in my code var result = sqlCmd.ExecuteNonQuery(); and every time I try to deleted multiple data only for the last one i get value 1 for the rest i get value 0. If i paste 1 by 1 is ok. I need to find a better solution for inserting data in datagridview pls help me if you have any suggestion
I believe your delete syntax is incorrect.
Not sure how it even deleted the one record.
Should be :
(You forgot the FROM)
"DELETE from Articol WHERE Serial = @Serial"

And, oh yeah, I'm assuming the table name is Articol.

The syntax of delete is
delete from <table-name> where
Share this answer
0x01AA 20-Feb-23 17:08pm    
Just visited the docs, see my edited answer. At least for MSSQL FROM is optional. Surprise, surprise for me :-)

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