Specified cast is not valid - System.InvalidCastException

Question

I've read many of similar questions and tried several different things, but I do not understand why this is not working. The column that is being summed is of the int type. I have tried casting to uint and int32. I appreciate direction. Thank you.

        int i = 1;
        int total=0;
        while (i < 7)
        {

            string conString = @"Data Source=UATDB2\sqleuat;Initial Catalog=CVNee;User ID=appuser;Integrated Security=true";

            SqlConnection connection = new SqlConnection(conString);
            connection.Open();
            SqlCommand cmd = connection.CreateCommand();
            cmd.CommandText = "SELECT SUM(AMOUNT) FROM TRANSACTIONS_DETAIL2 WHERE TRANSACTIONS_DETAIL2.TRANS_TYPE =" + i + "AND TRANSACTIONS_DETAIL2.HOH_UPI = '185292000'";

           int amt = ((int)cmd.ExecuteScalar());
            //amt = cmd.ExecuteScalar();

            if (i == 1)
            {
                total = total + amt;
            }

            if (i == 2)
            {
                total = total - amt;
            }

            if (i == 3)
            {
                total = total - amt;
            }

            if (i == 4)
            {
                total = total + amt;
            }

            if (i == 5)
            {
                total = total + amt;
            }

            if (i == 6)
            {
                total = total + amt;
            }

            connection.Close();
            ViewBag.TotalBalance = total;

            i++;
        }

Yes, I just noticed that too. I'm now using Convert.ToInt32 and I'm getting a more detailed exception. What would be the best way to handle the potential null? — vmil
And as nearly always with SQL related questions: Don't create queries using string concatenation but use parametrized queries as they will prevent SQL injections. — derpirscher

derpirscher derpirscher · Accepted Answer · 2016-04-07T22:56:48

The illegal cast exception occurs, when ExecuteScalar returns null. You could either check if the result is null before converting to int,

 object o = cmd.ExecuteScalar();
 int result = o == null ? 0 : (int)o;

or you could use a nullable int int? which can also be checked if it has a value

 int? result = (int?)cmd.ExecuteScalar();
 if (!result.HasValue) result = 0;

or you could adjust your query to return 0 even if no matching rows are found. The TSQL function COALESCEchecks if the first parameter is NULL, if no it returns the value, if yes, it returns the value of the second parameter.

 string query = "SELECT COALESCE(SUM(AMOUNT),0) FROM TRANSACTIONS_DETAIL2 WHERE TRANSACTIONS_DETAIL2.TRANS_TYPE =" + i + "AND TRANSACTIONS_DETAIL2.HOH_UPI = '185292000'";

Specified cast is not valid - System.InvalidCastException

2 Answers

according to your comment