Why does C# keep giving me -1 from a query when the console gives me the correct number from a count(*)?

Question

MySqlCommand checkUsername = conn.CreateCommand();
checkUsername.CommandText = "SELECT COUNT(*) FROM users WHERE username='admin'";
MessageBox.Show("The count is " + checkUsername.ExecuteNonQuery());

There is more code where this "count" is actually being used, but it was not working correctly so I made this little message box pop up to show what number I was actually receiving. When I go phpmyadmin and do a direct SQL word for word (I copy/pasted) it has no issues and gives the correct number of times the username exists. However, every time in my program (C#, using VS2010) it gives me -1, whether the name doesn't exist, or I have it in their 5 times.

I'm thinking it must be something to do with the way C# formats the number it is getting back, but I'm not really sure how to correct it.

I've also read up on using EXISTS for this instead of COUNT(*), but I couldn't get that to work at all, it gave me syntax errors every time.

Answer 1

Why are you calling ExecuteNonQuery when it is a query?

Use ExecuteScalar to execute a query which will give a single result.

It's quite right for ExecuteNonQuery to return -1. From the docs of DbCommand.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.