DataReader: Specified cast is not valid (Int32)

.net sqldatareader datareader
moldovanu picture moldovanu · Apr 12, 2012 · Viewed 22.8k times · Source

Why does SqlDataReader throw an exception when converting 0 to integer?

?dataReader(3)
0 {Short}
    Short: 0
?dataReader.GetInt16(3)
0
?dataReader.GetInt32(3)
{"Specified cast is not valid."}
    _HResult: -2147467262
    _message: "Specified cast is not valid."
    Data: {System.Collections.ListDictionaryInternal}
    HelpLink: Nothing
    HResult: -2147467262
    InnerException: Nothing
    IsTransient: False
    Message: "Specified cast is not valid."
    Source: "System.Data"
    StackTrace: "   at System.Data.SqlClient.SqlBuffer.get_Int32()     
                    at System.Data.SqlClient.SqlDataReader.GetInt32(Int32 i)"
    TargetSite: {Int32 get_Int32()}

Answer

Marc Gravell picture Marc Gravell · Apr 12, 2012

It isn't a convert - it is a cast. The same as:

short x = 0;
object y = x;
int z = (int)y; // BOOM! InvalidCastException Specified cast is not valid.

In both cases, a short is not an int.

If unsure of the type, you might try:

int i = Convert.ToInt32(dataReader.GetValue(3));

Related questions

Multiples Table in DataReader

I normally use DataSet because It is very flexible. Recently I am assigned code optimization task , To reduce hits to the database I am changing two queries in a procedure. one Query returns the count and the other returns the …

Read More
Enforce only single row returned from DataReader

I seem to write this quite a lot in my code: using (var reader = cmd.ExecuteReader()) { if (reader.Read()) { result = new User((int)reader["UserId"], reader["UserName"].ToString()); } if (reader.Read()) { throw new DataException("multiple rows returned from query"); } } Is …

Read More
Check for column name in a SqlDataReader object

How do I check to see if a column exists in a SqlDataReader object? In my data access layer, I have create a method that builds the same object for multiple stored procedures calls. One of the stored procedures has …

Read More