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?

If possible, you could try storing the result as an `object` before casting to an `int` and then inspect the result while debugging. It's possible that it's not returning exactly what you expect — devNull, Sep 06 '20 at 00:14
The only explanation I could think of is that the `COUNT` function in MySQL returns a 64-bit numeric type (AKA, `BIGINT`). Try casting it to `long` or `Int64`. You can either change the method return type to `long` and then `return (long)comm.ExecuteScalar();` or you can use `return (int)(long)...`. — 41686d6564 stands w. Palestine, Sep 06 '20 at 00:34

score 2 · Answer 1 · edited Sep 09 '20 at 15:28

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:

score 2 · Answer 2 · answered Sep 08 '20 at 07:55

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());

mikbal can · Answer 3 · 2020-09-06T06:48:08.903

1

Try this.

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

edited Sep 06 '20 at 06:48

answered Sep 06 '20 at 00:10

mikbal can

64
4

1

Could you explain why? – devNull Sep 06 '20 at 00:15
Object to int casting is getting error. This answer is explains detailed why : https://stackoverflow.com/a/3076525/10082186 – mikbal can Sep 06 '20 at 00:22
@mikbalcan I'm curious how that specifically applies to the error as a result from the MySQL query in this question. That answer doesn't seem related to that – devNull Sep 06 '20 at 00:30
3

The second call to `Convert.ToInt32` provides no value whatsoever. Remove it. The `COUNT` function returns `BIGINT` in MySQL, so the return value is a boxed `long`. You have to unbox to `long` before you can cast it to `int`. `(int)(long)cmd.ExecuteScalar()` would've been a more direct route. `Convert.ToInt32` is essentially doing the same thing but with added overhead of a type check and a virtual (interface) method call. That's why it works. – madreflection Sep 06 '20 at 00:31
2

"Try this" is almost always a signal that "the next is magic and I'm not sure how it works". That's why we ask answerers to explain code that they present, so that the OP, and others, can learn from it. – Heretic Monkey Sep 06 '20 at 00:34
@HereticMonkey: Yeah, I'm well aware. My main goal was to get the OP's attention because it looked like this was about to be accepted before the curtain could be drawn back. I flagged a duplicate, FWIW. – madreflection Sep 06 '20 at 00:39
`This answer is explains detailed why :` I can't see how that link is relevant to this issue. – mjwills Sep 06 '20 at 00:42
2

@madreflection My comment was directed at the OP, not you, just so you know. Hoping they'll see the wisdom of adding more commentary than "Try this" to later answers. I appreciate your explanation of why I took one look at two `Convert.ToInt32`s in a row and (quietly) yelled No! :) – Heretic Monkey Sep 06 '20 at 00:43
1

@HereticMonkey: Thanks for clarifying. I got a little paranoid on second reading and couldn't shake it. Anyway, I think kittens might have been harmed by the two `Convert.ToInt32` calls. – madreflection Sep 06 '20 at 00:48
Woah, why not add another 3 nested `Convert.ToInt32`? why did you stop at 2? – TheGeneral Sep 06 '20 at 02:44
Sorry, 2nd Convert is clerical error.. – mikbal can Sep 06 '20 at 06:49

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

3 Answers3