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

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:

C#
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());
                sqlCmd.ExecuteNonQuery();
            }
Posted
Updated 20-Feb-23 12:52pm
v2
Comments
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.

[Edit0]
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
 
v6
Comments
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

https://www.codeproject.com/Tips/208281/Copy-Paste-in-Datagridview-Control 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)
SQL
"DELETE from Articol WHERE Serial = @Serial"


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

The syntax of delete is
SQL
delete from <table-name> where
 
Share this answer
 
v2
Comments
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