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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

raddevus · Answer 1 · 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 :-)

0x01AA · Answer 2 · 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