How do I delete from database table data that is identical to the column of a datagridview

Question

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 20-Feb-23 9:20am

card_maruis

Updated 20-Feb-23 12:52pm

PIEBALDconsult

v2

Add a Solution

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

0x01AA 20-Feb-23 16:43pm

Only big speculation, try to trim the string you get from the grid. I mean .ToString().Trim(' ')
And yes, can't be the reason because you explained, last record will be deleted.... headache ;)

card_maruis 20-Feb-23 16:41pm

Serial is nvarchar(MAX) and i fill the column from datagridviw with copy past from table to be the exact data

card_maruis 20-Feb-23 16:47pm

I try .Trim(' ') same result only the last row was deleted

0x01AA 20-Feb-23 17:01pm

Sorry, I'm out of ideas :(
I trust you, that the grid does not show only the last and only one column :-)

card_maruis 20-Feb-23 17:12pm

i have a database table named Articol in this table i have 100 records like "Serial | Lot | Date"
and i need to delete records from table where "Serial" is like the first Column of a datagridwiew
every time i try to delete 3 or more records is deleting only the last Serial in datagridview

2 solutions

Add a Solution

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

0x01AA · Answer 1 · 2023-02-20T10:21:00

Solution 1

~~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[^]

Posted 20-Feb-23 10:21am

0x01AA

Updated 20-Feb-23 11:13am

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

raddevus · Answer 2 · 2023-02-20T10:22:00

Solution 2

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

Posted 20-Feb-23 10:22am

raddevus

Updated 20-Feb-23 10:23am

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 :-)

How do I delete from database table data that is identical to the column of a datagridview

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

How do I delete from database table data that is identical to the column of a datagridview

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us