CODEWITHSUNDEEP
c#sql-serverasp.net-mvc-5
reakt
reakt

Reputation: 540

Specified cast is not valid - System.InvalidCastException

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++;
        }

Upvotes: 2

Views: 10449

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

according to your comment

If you call ExecuteScalar, you might get null or DBNull in return, and you should check both. You can first fill it into a variable of type object, then check for DBNull.Value and null and react on this:

int amt=0;
object amtUnchecked = cmd.ExecuteScalar();
if(amtUnchecked != DBNull.Value && amtUnchecked!=null)
    amt=(int)amtUnchecked;

Or you change the SELECT to return a sure zero: SELECT ISNULL(SUM(AMOUNT),0) ...

Upvotes: 1

derpirscher
derpirscher

Reputation: 17372

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'";

Upvotes: 3

Related Questions