Invalid cast exception when reading result from SQLDataReader

Question

My stored procedure:

@UserName nvarchar(64),

AS

BEGIN
    SELECT MPU.UserName, SUM(TS.Monday) as Monday -- TS.Monday contains float value
    FROM dbo.MapTask MT JOIN dbo.MapPU MPU
        ON MPU.ID = MT.MPUID
    JOIN dbo.TimeSheet TS
        ON MT.TMSID = TS.ID
    WHERE MT.StartDate = @StartDate_int and MPU.UserName = @UserName
    GROUP BY MPU.UserName
END

In my C# code

SqlDataReader reader = command.ExecuteReader();
        
while (reader.Read())
{
    float monday = (float) reader["Monday"]; // Invalid cast exception
}

Can somebody tell me what I did wrong ? Thank you.

@Jethro: That's a pretty horrible way of doing it though. When a conversion fails, replacing it with two conversions (to and from string) is generally not a good idea. — Jon Skeet
– Jon Skeet, Commented Aug 22, 2011 at 10:30
@Jon Skeet, that does make sense, plus Convert.ToFloat doesn't exist. Need to be more carefull. If Convert.ToFloat did exist would it be better to do this then. Convert.ToFloat(reader["Monday"]); ?? — Jethro
– Jethro, Commented Aug 22, 2011 at 10:43
@Jethro: It would be better to call reader.GetFloat IMO. Give control of the conversion to the reader itself. — Jon Skeet
– Jon Skeet, Commented Aug 22, 2011 at 11:18

Jon Skeet · Accepted Answer · 2011-08-22 10:34:07Z

35

My guess is that the value is being returned as a boxed double instead of float. When you unbox the type has to be exactly right. So assuming I'm right and it's not decimal or something like that, you could use:

float monday = (float) (double) reader["Monday"];

and it would work. That's pretty ugly though. If you use SqlDataReader.GetFloat it should get it right if it's genuinely a single-precision value, and it's clearer (IMO) what's going on.

On the other hand, your data could actually be coming back from the database as a double, in which case you should (IMO) use:

float monday = (float) reader.GetDouble(column);

As an aside, are you sure that float is actually the most appropriate type here in the first place? Often decimal is more appropriate...

edited Aug 22, 2011 at 10:34

answered Aug 22, 2011 at 10:28

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Triynko Over a year ago

If the datatype in SQL Server is "float", then calling SqlDataReader.GetFloat will unfortunately throw an error, because the naming conventions are completely incompatible. An SQL "float" is actually a double-precision value and therefore maps to a .NET double. An SQL "real" is the single-precision that maps to a .NET float. .NET's names are internally inconsistent, instead of choosing "double" and "single" as keywords, it mixes "float" in, whereas in SQL it's "float" and "real" with no reference to double or single precision.

Paolo Falabella · Accepted Answer · 2011-08-22 10:31:49Z

16

A sql float is a .NET Double, see on the msdn. Try casting to a double.

answered Aug 22, 2011 at 10:31

Paolo Falabella

26k3 gold badges79 silver badges91 bronze badges

2 Comments

TheGateKeeper Over a year ago

You can try casting to double and then to float, that is what I do.

Triynko Over a year ago

This is the correct answer. The cast exception occurs because SQL server uses "float" and "real" to refer to double and single-precision values respectively. I'd imagine it's a common mistake to try to call SqlDataReader.GetFloat on an SQL "float" field, because that would make sense, but alas, you must call SqlDataReader.GetDouble. And if you meant to store a single-precision value, it's back to the database to change the type from "float" to "real". They should have just stuck to "float32", "float64", "int32", int64" instead of all this "tiny" "small" "big" "real" "single" "double" nonsense.

Fred Mauroy · Accepted Answer · 2013-11-13 15:19:31Z

0

I also had a similar issue and ended up doing this:

if (!oDR.IsDBNull(0))
    Rating = (float)oDR.GetSqlDecimal(0).Value;

oDR.GetFloat(0) was returning and invalid cast exception when accessing the result of a SELECT AVG(...).

HTH

answered Nov 13, 2013 at 15:19

Fred Mauroy

1,2291 gold badge11 silver badges10 bronze badges

Comments

Alp Altunel · Accepted Answer · 2020-05-25 21:17:16Z

0

float monday = Convert.ToDouble(reader["Monday"]);

would be a nice approach. I am trying to find a solution for over an hour and it fails here. now it works fine.

answered May 25, 2020 at 21:17

Alp Altunel

3,4521 gold badge28 silver badges30 bronze badges

Collectives™ on Stack Overflow

Invalid cast exception when reading result from SQLDataReader

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related