casting ExecuteScalar() result c#

Question

why would this work

int collectionCharge = (int)cmdCheck.ExecuteScalar();

but this produces an exception

double collectionCharge = (double)cmdCheck.ExecuteScalar();

System.InvalidCastException: Specified cast is not valid.

why would it not be valid?

EDIT I am trying to simply return a single result from a query, that gets the price of some freight. So I can't turn this into an int because it must have decimals, hence trying to cast to a double. I am still learning asp.net so if there's a better way to achieve this, please do point me in the right direction :)

EDIT 2 the full code with SQL...

using (SqlCommand cmdCheck = new SqlCommand("SELECT FREIGHT_PRICE FROM FREIGHT_QUOTER_BELNL_NEW WHERE CUSTOMER_NO = @CUSTOMER_NO AND COUNTRY = @COUNTRY AND ROUND(WEIGHT_FROM,0) < @WEIGHT AND ROUND(WEIGHT_TO,0) >= @WEIGHT AND SHIPVIA = '48';", connection))
                {
                    double collectionCharge = (double)cmdCheck.ExecuteScalar();
                    FreightAmount = collectionCharge;
                }

Walburga

Because it's returning an int, not returning a double? – Breunig

I don't know, but a double cast might get you past this: (double)(int)cmdCheck.ExecuteScalar(). – Pb

-1 ExecuteScalar does not return a double MSDN Execute Scalar – Featherweight

I thought the cast "trusted" you? the return is a number from a sql database, I thought you could cast a number into any number datatype? – Walburga

You can cast Stuart but why would you need to double cast a value perhaps you need to read up of Type Casting Boxing and UnBoxing as well – Featherweight

@DJKRAZE, it doesn't return an int directly, either. Your comment and vote indicates you do not understand his question. – Southernly

I'm pretty sure ExecuteScalar can return null. I'd suggest following the advice in this answer: #871197 – Whoop

I totally understand his question Anthony also makes no sense why someone would cast a return type as a double when Executing Scalar function.. come on now... I am also quite familiar with Boxing and UnBoxing as well as Implicit Casting and Explicit Casting.. – Featherweight

i've edited my question with an explanation of what I'm trying to do... – Walburga

perhaps I should be using DataReader() here instead? As I understood it, if you only need one result from a query an ExecuteScalar() method did this with less overhead? – Walburga

ExecuteScalar is just fine - you'd have the same issue with DataReader. The double-casting is the right approach. If you're expecting the value to be a floating-point from the database, then double-check your query, since it's apparently returning an integer. – Locule

Stuart can you show what your SQL Command looks like you you could append to your SQL Command the following "SELECT CAST(scope_identity() AS double)" then in your code declare the following double collectionCharge = (double)cmdCheck.ExecuteScalar(); – Featherweight

OK, so at least my thought track is right, just the data is not what I'm expecting it to be. Makes me feel a little better :) And I did not know you could double cast, I'll give it a go, thanks! – Walburga

@Walburga casting is an expression. That's like saying you knew you can do 1+1, but where unaware that (1+1)+1 also works. – Whoop

@DJKRAZE, again, it is evident you have a misunderstanding. ExecuteScalar is going to return an object that contains the value of the first column of the first row of the result. Unless you want to deal with object in your code, you are going to convert the result to the more applicable type. It makes complete sense to cast it to the type you wish to use. His question is why doesn't the direct cast to double work. ExecuteScalar is only tangentially involved here. – Southernly

@DJ - updated with more code – Walburga

That was an example, Anyway Anthony I will leave this question for you to completely answer as well as provide the OP a solution..it's Friday and I am not going to get all worked up over different approaches to solving the same problem...Cheers – Featherweight

@DJKRAZE, I agree, no need to get worked up. I do not dispute multiple approaches. I took issue with your initial comment and vote. Again, in light of the code (what worked, what didn't) casting to int succeeded and casting to double did not. ExecuteScalar does not return either type. So why vote down for the cast to double? That makes no sense. ExecuteScalar could return a boxed double, as you later help demonstrate. So the issue never was that ExecuteScalar does not return double. Make sense? – Southernly

I agree also I believe the initial question was totally misleading but whatever the case I totally agree with the what you are saying after reading the edited question. I appreciate your feedback as well Anthony – Featherweight

I apologize for unintentionally misleading anybody! I'll take better care in future with my questions :) – Walburga

Answer 1

With thanks to @DJKRAZE.

I updated my query to SELECT CASE(FREIGHT_PRICE AS FLOAT) which now works with the (double) cast.

double collectionCharge = (double)cmdCheck.ExecuteScalar();

Walburga

This is not safe from exceptions. See my comment on JaredPar's answer. Please look into this solution: #871197 – Whoop

Stuart I apologize for not providing you an exact example but giving you the example using the Scope_Identity as an example led you to the proper solution.. +1 for being able to get this to work based on the suggestion – Featherweight

@asawyer, I agree with you, however in my case, it is safe from null values because we have an NVL() on this field in the view being queried so it will never be null (but 0). – Walburga

Answer 2

The problem here is that ExecuteScalar is returning an int which is boxed into an object. In order to convert to a double you must first unbox to an int then convert to a double

double collectionCharge = (double)(int)cmdCheck.ExecuteScalar();

Simplicidentate

if Execute returns DBNull => InvalidCastException: Specified cast is not valid. If it returns null -> NullReferenceException: Object reference not set to an instance of an object. – Whoop

Answer 3

Use the Convert.ToXXX to avoid invalid cast exceptions.

I.E

collectionCharge=Convert.ToDouble(cmdCheck.ExecuteScalar());

As it appears that ExecuteScalar returns an Object so the code:

double collectionCharge = (double)cmdCheck.ExecuteScalar();

Could still fail

Egress

Great solution, especially because different database types return different number types in similar situations. For example SELECT @@IDENTITY in MsSql returns decimal, while in MsAccess-SQL it returns an int. – Imperium

Answer 4

After reading all answers, I had a case of receiving the Decimal values indeed, and the solution was easy! I just declared the function as string and received the Decimal value as string!

public static string Sals_AccountExpensesGetSums(int accountID)

{

SqlParameterHelper sph = new 
SqlParameterHelper(ConnectionString.GetWriteConnectionString(), 
"sals_AccountExpenses_GetAllSums", 1);

sph.DefineSqlParameter("@AccountID", SqlDbType.Int, ParameterDirection.Input, accountID);


string res = sph.ExecuteScalar().ToString();

return res;
}

and in the business layer i changed the result to double!

public static decimal GetAccountExpensesSums(int accountId)
{
string res = "";
decimal sums = 0;

res = DBAccount.Sals_AccountExpensesGetSums(accountId);

// check so we will not have exception 
if ( res != "")
sums = Convert.ToDecimal(res);

return sums;

}

and the result was perfect as needed: 889678.70

Mines

Answer 5

I would recommend using this code:

object o = c.ExecuteScalar();
     if (o != null)
     {
         int x = Int32.Parse(o.ToString());      
     }

This does two things. First it makes sure that your c.ExecuteScalar() isn't returning null If it did so and you tried to cast, you'd have problems.

Second, it makes casting much simpler because it can be applied to pretty much all cases when reading from a query.

The object becomes a string. If you want it as a string, you're done. If you want it as a boolean, check to see if that string.Equals("true") If you want it as an int, Int32.Parse(string); if you want it as a long, Int64.Parse(string);

Basically, you won't have to worry about fully understanding overloading/explicit conversion.

Willemstad

Convert something to a string so you can convert it back into a number? That seems very clumsy and slow. It seems to me from my experience (PostgreSQL smallint can be cast to short but not to integer) and from reading this thread that one of the Convert methods is the best solution. – Helsa