Why am I getting "Specified Cast is Not Valid" casting an int return value to int?

Question

I've got this code:

private int GetCombinedRankRank(int combinedRankValue)
{
    try
    {
        conn = new MySqlConnection(connstr);
        conn.Open();
        try
        {
            DataTable dtStates = new DataTable();
            MySqlCommand comm = conn.CreateCommand();
            comm.CommandText = "SELECT COUNT(states_id) "+
                               "FROM states "+
                               "WHERE combinedRank < @combinedRankValue";
            comm.Parameters.AddWithValue("@combinedRankValue", combinedRankValue);
            return (int)comm.ExecuteScalar();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
            throw;
        }
    }
    finally
    {
        conn.Close();
    }
}

...which fails on the line:

return (int)comm.ExecuteScalar();

...with "Specified cast is not valid" -- Yet the same SQL works in Database Workbench:

The SQL returns an int. The data type of the combinedRank column in the Table is Integer. I am casting that value to an int. How can that be an invalid cast?

Answer 1

This is something I had encountered earlier so I can shed my two cents on it:

The comm.ExecuteScalar() will return long in case of MySqlCommand. You can check if this is true by printing the typeof cmd.ExecuteScalar(). It should return long. If so, correct way to typecast would be the following:

return (long)comm.ExecuteScalar();

This should avoid any data loss. However, when I look at the examples given by mysql connector (https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-sql-command.html), they are using the following:

string sql = "SELECT COUNT(*) FROM Country";
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            object result = cmd.ExecuteScalar();
            if (result != null)
            {
                int r = Convert.ToInt32(result);
                Console.WriteLine("Number of countries in the world database is: " + r);

            }

They use Convert.ToInt32 and also check if the object is null which I feel is correct way to handle the situation. Maybe change it to long if the returntype is long:

Answer 2

MySQL COUNT() will return BIGINT (The return type of the COUNT() function is BIGINT.) will C# translate it to Int64 data type but it will return by the ExecuteScalar() as an object.

If ExecuteScalar() return it as Int64 then (int)comm.ExecuteScalar() will works. A good answer posted way way back can explain it why, Better way to cast object to int.

To solve the problem the following options will work.

• return (int)(Int64)comm.ExecuteScalar();
• return int.Parse(comm.ExecuteScalar().ToString());
• return Convert.ToInt32(comm.ExecuteScalar());

Answer 3

Try this.

return Convert.ToInt32(cmd.ExecuteScalar());