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

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

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

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:

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

Try this.

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

Leave a Reply

Your email address will not be published.