C# MySQL Order By Returns -1

Question

I'm trying to get the last row of a table using C# but it doesn't seem to be working, this is my code:

MySqlConnection cnnGetID = new MySqlConnection(Global.connectionString);

cmd = "SELECT ContactID FROM Contacten ORDER BY ContactID DESC LIMIT 1";
MySqlCommand cmdGetID = new MySqlCommand(cmd, cnnGetID);

cnnGetID.Open();
string contactID = cmdGetID.ExecuteNonQuery().ToString();
MessageBox.Show(contactID);

cnnGetID.Close();

The value this returns is -1 while it should be returning 59.

The strange thing is is that when I run this command in phpmyadmin I DO get 59.

Any ideas on why C# is not returning the correct value but phpmyadmin is?

EDIT: problem solved, should've uses ExecuteScalar(). Looks like I've been staring at my monitor for a bit too long...

Lesson to learn here: read the names of the methods you're calling carefully, and read their documentation carefully. — Jon Skeet, Apr 08 '17 at 09:01
http://stackoverflow.com/questions/5349114/executenonquery - Using ExecuteNonQuery is intended for UPDATE, INSERT and DELETE queries. — Palindromer, Apr 08 '17 at 09:06

Usman · Answer 1 · 2017-04-08T10:32:19.820

1

you should use ExecuteScalar because you are returning value ExecuteNonQuery returns the number of rows affected by update delete or insert opeation

you can check this for more info

ExecuteNonQuery

Returns the number of rows affected.

ExecuteScalar

Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

for more information you can check this The MySqlCommand Object

edited Apr 08 '17 at 10:32

answered Apr 08 '17 at 08:59

Usman

4,615
2
17
33

You've linked to `SqlCommand` documentation - this is `MySqlCommand`. That's why I linked to `IDbCommand.ExecuteNonQuery`. – Jon Skeet Apr 08 '17 at 09:30
@JonSkeet thanks for the correction sir i have updated the link – Usman Apr 08 '17 at 10:32

score 1 · Answer 2 · answered Apr 08 '17 at 08:59

The value this returns is -1 while it should be returning 59.

No, it's behaving exactly as documented by IDbCommand.ExecuteNonQuery:

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1.

You're using a SELECT statement - a query. So instead of executing ExecuteNonQuery, you should be using ExecuteQuery and iterating over the results, or ExecuteScalar, given that you know you'll have a single result:

string contactID = cmdGetID.ExecuteScalar().ToString();

score 1 · Accepted Answer · answered Apr 08 '17 at 09:00

1

You need to use ExecuteScalar instead of ExecuteNonQuery.

MySqlConnection cnnGetID = new MySqlConnection(Global.connectionString);

cmd = "SELECT ContactID FROM Contacten ORDER BY ContactID DESC LIMIT 1";
MySqlCommand cmdGetID = new MySqlCommand(cmd, cnnGetID);

cnnGetID.Open();
string contactID = cmdGetID.ExecuteScalar().ToString();
MessageBox.Show(contactID);
cnnGetID.Close();

This should resolve your issue.

answered Apr 08 '17 at 09:00

Chetan

6,711
3
22
32

I have no clue how I didn't see that haha, looks like I've been staring at my monitor for a bit too long! – user265889 Apr 08 '17 at 09:01

score 0 · Answer 4 · answered Apr 08 '17 at 10:53

you can use query like this

MySqlConnection cnnGetID = new MySqlConnection(Global.connectionString);

cmd = "SELECT TOP 1 ContactID FROM Contacten ORDER BY ContactID";
MySqlCommand cmdGetID = new MySqlCommand(cmd, cnnGetID);

cnnGetID.Open();
string contactID = cmdGetID.ExecuteNonQuery().ToString();
MessageBox.Show(contactID);

cnnGetID.Close();

C# MySQL Order By Returns -1

4 Answers4