Problem with SQL delete row (C#)

Question

1.00/5 (1 vote)

See more:

I have weir problem with my database, because I cant delete last row, but when I run that same instuoction in SQL it works fine my code:

public void DeleteLastRowClovekData()
        {
            SqlConnection cnn;
            string databaseName = "[MydatabaseName]";
            string connection_string = Helper.GetInfo("[MYlinkForConnectionString]");
            string del;
            del = "DELETE FROM " + databaseName + " WHERE Id = (SELECT MAX(Id) FROM "       + databaseName + " )";
            cnn = new SqlConnection(connection_string);
            SqlCommand cursor = new SqlCommand(del, cnn);
            cnn.Open();
            cursor.ExecuteNonQuery();
            cnn.Close();
        }

BTW: There are 10 columns.

What I have tried:

I have tried this code, recreating table and I have tried Reader query but nothing is seems to be working. And this code:

SqlConnection cnn;
            string databaseName = "[MYdatabaseName]";
            string connection_string = Helper.GetInfo("[MylinkForConnectionString]");
            string del;
            del = "DELETE FROM " + databaseName + " WHERE Id = (SELECT MAX(Id) FROM " + databaseName + " )";
            cnn = new SqlConnection(connection_string);
            SqlCommand cursor = new SqlCommand(del, cnn);
            SqlDataReader Reader;
            cnn.Open();
            Reader = cursor.ExecuteReader();
            while (Reader.Read())
            {
            }
            cnn.Close();

Posted 2-Feb-20 6:26am

BrogP

Updated 3-Feb-20 1:06am

v2

Add a Solution

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

OriginalGriff · Accepted Answer · 2020-02-02T06:33:00

Solution 1

Why are you trying to use a Reader? There is nothing to read - a DELETE command doesn't return any rows so it's probably throwing an exception.

Use ExecuteNonQuery and your problem should just go away.

Posted 2-Feb-20 6:33am

OriginalGriff

Comments

BrogP 2-Feb-20 12:47pm

I had not used reader in first case and it also dont work.

OriginalGriff 2-Feb-20 13:08pm

"it dont work" is probably the most useless problem report we get - and we get it a lot. It tells us nothing about what is happening, or when it happens.
So tell us what it is doing that you didn't expect, or not doing that you did.
Tell us what you did to get it to happen.
Tell us any error messages.
Tell us what you looked at to find out why!

BrogP 2-Feb-20 13:24pm

I fixed it already. Thanks for trying to help tho :)

MadMyche 2-Feb-20 15:07pm

What was the solution?

Maciej Los 2-Feb-20 14:42pm

5ed!

Maciej Los · Accepted Answer · 2020-02-02T08:43:00

In addition to solution #1...

This command:

C#

del = "DELETE FROM " + databaseName + " WHERE Id = (SELECT MAX(Id) FROM " + databaseName + " )";

is very dangerous!
You may loose all your data! Please, read about SQL Injection - SQL Server | Microsoft Docs[^]

You shouldn't use concatenated strings. You have to use parameterized queries, see: Using Parameters for SQL Server Queries and Stored Procedures[^]

Simon_Whale · Accepted Answer · 2020-02-03T01:06:00

First thing I would suggest

Quote:
del = "DELETE FROM " + databaseName + " WHERE Id = (SELECT MAX(Id) FROM " + databaseName + " )";

put a breakpoint on this line and inspect what value del has assigned to it.

I am going to guess that you are trying to delete incorrectly as you are inputting the database name rather than the table that you need delete from.

Problem with SQL delete row (C#)

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0