How do i delete the last inserted record on database?

Question

2.00/5 (1 vote)

See more:

Hello,

I have this SQL syntax to delete the last record, but it seems to refuse to work...

Code Snippet:

C#

private void cmdAnular_Click(object sender, EventArgs e)
{
    string SQL = "DELETE FROM entradas WHERE id_entrada = @entrada ORDER BY id_entrada DESC LIMIT 1";

    using (var cn = new MySqlConnection("server = localhost; user id = root; password = 12345; persistsecurityinfo = True; database = portaria; allowuservariables = True"))
    {
        using (var cmd = new MySqlCommand(SQL, cn))
        {
            int i = 0;
          cmd.Parameters.Clear();
          cmd.Parameters.AddWithValue("@entrada", Grid1.Rows[i].Cells["#"].Value);
          MySqlDataAdapter sda = new MySqlDataAdapter();
          DataTable dt = new DataTable();
          sda.SelectCommand = cmd;
          sda.Fill(dt);
          Grid1.Update();
          Grid1.Refresh();

          cn.Open();
        }
        cn.Close();
        cmdAnular.Enabled = false;
    }
}

SQLyog LOG:

SQL

1 queries executed, 1 success, 0 errors, 0 warnings

Query: DELETE FROM entradas WHERE id_entrada = @entrada ORDER BY id_entrada DESC LIMIT 1

0 row(s) affected

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0.001 sec

What I have tried:

Tried to use record id to check the highest number shown on "#" and then delete it. But this query is fine on SQLyog, but not doing anything on the actual program.

Posted 9-Mar-16 4:06am

Scribling Doodle

Updated 9-Mar-16 23:53pm

v3

Add a Solution

Comments

CHill60 9-Mar-16 10:30am

"apparently doesn't work" ... what happens?
Try running that query directly in MySQL and see what happens.
Here is the reference material for what you are doing wrong - MySQL :: MySQL 5.7 Reference Manual :: 13.2.2 DELETE Syntax[^]

Scribling Doodle 9-Mar-16 10:46am

I did run the SQLyog and forgot to insert it, its already there. Check it.

CHill60 9-Mar-16 10:52am

Look at the query closely. You are searching for a specific vehicle in the table but ordering by the vehicle id. If you want to remove a specific row for that vehicle you will need to identify it - e.g. order by the row number or time inserted or however else you decide the row was the "last one entered".
I presume it is just deleting a row that you did not expect to be deleted? Just saying "apparently doesn't work..." does not help us to help you

Scribling Doodle 9-Mar-16 10:54am

i'm ordering by highest record id, not the vehicle id. The Vehicle id is called "id_veiculo". Two separate things

Scribling Doodle 9-Mar-16 10:56am

i simply want the record to delete the last record known by the program. Data wont work because it doesn't store the seconds... So, it wont be useful. For example, if i have 2 values with 2:30 PM it will delete both, so i wanted it to delete it by record id, which is called "id_entrada"

CHill60 9-Mar-16 12:01pm

Then get rid of WHERE id_entrada = @entrada - it implies that you already know the highest record.
Either that or use DELETE from FROM entradas WHERE id_entrada = @entrada I can't see why you would need both

Michael_Davies 9-Mar-16 14:59pm

The cn.Open appears AFTER the execution of the commands, shouldn't it be first?

You create a new DataTable after you have deleted the record and then just refresh Grid1, how is the new DataTable linked to Grid1?

Neat Tip for MySQL, look at the MySQLHelpers, simplifies code.

1 solution

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)

vipin.kv · Accepted Answer · 2016-03-09T23:53:00

Solution 1

C#

You can add an auto increment/IDENTITY column, it will keep the track of inserted data. You can use the IDENTITY column in where condition of DELETE
Example:
[ID]           INT           IDENTITY (1, 1) NOT NULL

DELETE FROM Employee WHERE ID in(SELECT MAX(ID) FROM Employee)

Posted 9-Mar-16 23:53pm

vipin.kv

Comments

CHill60 10-Mar-16 6:00am

OP tagged post as MySQL. This solution is MSSQL
Oh - and the OP already has a record id on the table.

Michael_Davies 10-Mar-16 6:07am

Odd as OP is using MySQL methods like MySqlConnection etc.

CHill60 10-Mar-16 6:14am

What's odd?
vipin.kv posted an MSSQL solution to a MySQL question is all I was saying

vipin.kv 10-Mar-16 6:17am

I agree the Syntax have slight difference between MSSQL and MYSQL but the solution will work right ?

CHill60 10-Mar-16 6:26am

The principle will work, yes.
So does DELETE FROM Employee ORDER BY [ID] DESC LIMIT 1 - which is remarkably similar to what the OP already has.

Michael_Davies 10-Mar-16 6:29am

Apologise, misread it.