Masoud Masoud - 1 month ago 19
C# Question

ExecuteScalar() return error "Specified cast is not valid." when there is no record

i have used below code for calculating (total invoice) each customer, for customers there is no invoice , it return error , i tried to handle error with null but it does not work.

public static decimal GetInvoiceTotal(int customerID)
{
SqlConnection connection = MMABooksDB.GetConnection();
string selectStatement
= "SELECT SUM(InvoiceTotal) "
+ "FROM Invoices "
+ "WHERE CustomerID = @CustomerID";
SqlCommand selectCommand =
new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@CustomerID", customerID);




try
{
connection.Open();

if (selectCommand.ExecuteScalar()!=null)
{
decimal invoiceTotal = (decimal)selectCommand.ExecuteScalar();
return invoiceTotal;
}


else
{
return 0;
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}

Answer

You don't have to call the ExecuteScalar twice.

var value = selectCommand.ExecuteScalar();
if(value != DBNull.Value)
{
    return (decimal)value;
}
Comments