c# MySqlCommand.ExecuteNonQuery() return -1

Question

I'm trying to execute a SQL request in C# to know if a user is already registered on my data base. To do that, I'm using the following source code :

    public bool pseudoDispo(string pseudo)
    {
        // Ouverture de la connexion SQL
        this.OpenConnection();

        // Création d'une commande SQL en fonction de l'objet connection
        MySqlCommand cmd = this.connection.CreateCommand();
        cmd.CommandText = "SELECT COUNT(*) FROM `user` WHERE `pseudo` = '" + pseudo + "'";

        int test = cmd.ExecuteNonQuery();
        MessageBox.Show(test.ToString());

        // Exécution de la commande SQL
        if (cmd.ExecuteNonQuery() == 1)
        {
            this.connection.Close();
            MessageBox.Show("Registered");
            return true;
        }
        else
        {
            this.connection.Close();
            MessageBox.Show("Not Registered");
            return false;
        }
    }

But the problem is that MySqlCommand.ExecuteNonQuery() always returns -1 and I don't know why. My request seems work because it returns me the good result (1 if registered, 0 if not) on phpmyadmin.

Is there anyone to help me and explain me what I'm doing wrong please?

Thanks!

Edit: I've been trying to do my function on another way by using ExecuteScalar() but now I got some troubles with it because it always returns me 0. I clearly do not understand something but I don't even know what... Can I get more informations about ExecuteScalar() and that kind of functions? I tried to cast it into a string, and it seems that cmd requests an Int64. So I updated my code like that, but it still doesn't work. I'm pretty depressed with my lack of knowledge but anyway, here is the code :

    public int pseudoDispo(string pseudo)
    {
        Int64 dispo_pseudo = 0;
        string sql = "SELECT COUNT(*) FROM `user` WHERE `pseudo` = '[pseudo] = ?' ";

        MySqlCommand cmd = new MySqlCommand(sql);
        try
        {
            this.OpenConnection();

            OleDbCommand dbcommand = new OleDbCommand(sql);

            dbcommand.Parameters.AddWithValue("@p1", pseudo);
            dbcommand.CommandType = CommandType.Text; 

            dispo_pseudo = (Int64)dbcommand.ExecuteScalar();

        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        return (int)dispo_pseudo;
    }

`ExecuteNonQuery()` is not intended to be used with `SELECT` statements. You are probably looking for `ExecuteScalar()`. Look at the remarks section [here](https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery(v=vs.110).aspx). — Glorin Oakenfoot, Mar 10 '16 at 22:09

score 9 · Accepted Answer · answered Mar 10 '16 at 22:09

9

You're calling ExecuteNonQuery, despite trying to execute... a query. You should be using ExecuteScalar - or ExecuteQuery and check whether there are any results.

ExecuteNonQuery is specifically for insert/delete/update SQL statements, and the number returned is the number of rows affected.

From the documentation of 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.

(As an aside, you should almost certainly be creating a new connection each time, and use using statements for all the disposable object you're using, such as MySqlCommand.)

answered Mar 10 '16 at 22:09

Jon Skeet

1,421,763
867
9,128
9,194

Thanks but I still got a problem by casting ExecuteScalar() in int. Visual Studio is telling me 'invalid cast'. Here is the code : `int count = (int)cmd.ExecuteScalar(); if (count == 1) { this.connection.Close(); MessageBox.Show("Registered"); return true; } else { this.connection.Close(); MessageBox.Show("Not Registered"); return false; }` – Y.Paulet Mar 10 '16 at 22:49
Right, so look at the result in the debugger without casting to see what the *actual* type is, so you know what you should cast to. – Jon Skeet Mar 11 '16 at 04:07

score 1 · Answer 2 · answered Mar 11 '16 at 00:01

As mentioned already, you are trying to get results out of a Query. ExecuteNonQuery will only work for sql statements that do not return results.

Try something like this:

    string strResult;
    SqlDataReader sqlResult = cmd.ExecuteReader();
            while (sqlResult.Read())
            {
               strResult  = result[0].ToString();
            }

c# MySqlCommand.ExecuteNonQuery() return -1

2 Answers2