Specified cast is not valid converting INT to ENUM with SqlDataReader

Question

EDIT

Removing the object initializer on assetItem appears to have resolved my issue, but why?

END EDIT

I don't seem to be able to figure out a way to read an INT from my database as cast it to emun.

I've tried the suggestions in both Cast int to enum in C# and How to (efficiently) convert (cast?) a SqlDataReader field to its corresponding c# type? without success.

using (var connection = new SqlConnection(_sqlstring))
{
    using (var command = new SqlCommand("EXEC GetAllEncodedMedia", connection))
    {
        try
        {
            connection.Open();
            var reader = command.ExecuteReader();
            while (reader.Read())
            {
                if (reader.HasRows)
                {
                    var assetItem = new MediaServices.EncodedAssets
                    {
                        Id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0),
                        PublishedName = reader.IsDBNull(1) ? null : reader.GetString(1),
                        PublishUri = reader.IsDBNull(2) ? new Uri("http://www.null.com") : new Uri(reader.GetString(2)),
                        EncodePreset = reader.IsDBNull(3) ? 0 : (MediaServices.EncodePresetsForSmoothStreaming)reader.GetInt32(3),
                        AssetId = reader.IsDBNull(4) ? null : reader.GetString(4),
                        EncoderJobId = reader.IsDBNull(5) ? 0 : reader.GetInt32(5),
                        //EncoderState = reader.IsDBNull(6) ? 0 : (JobState)reader.GetInt32(6),
                        //AssetState = reader.IsDBNull(7) ? 0 : (MediaServices.InternalAssetState)reader.GetInt32(7),
                        GroupId = reader.IsDBNull(8) ? 0 : reader.GetInt32(8),
                        Published = !reader.IsDBNull(2)
                    };
                    listReturn.Add(assetItem);
                }
            }
        }
        catch (Exception ex)
        {
            //error
        }

    }
}



[DataContract]
public enum EncodePresetsForSmoothStreaming
{
    [EnumMember]
    [Description("H264 Smooth Streaming 1080p")]
    H264SmoothStreaming1080P,
    [EnumMember]
    [Description("H264 Smooth Streaming 720p")]
    H264SmoothStreaming720P,
    [EnumMember]
    [Description("H264 Smooth Streaming 720p for 3G or 4G")]
    H264SmoothStreaming720Pfor3Gor4G,
    [EnumMember]
    [Description("H264 Smooth Streaming SD 16x9")]
    H264SmoothStreamingSd16X9,
    [EnumMember]
    [Description("H264 Smooth Streaming SD 4x3")]
    H264SmoothStreamingSd4X3

}

How do I cast ordinal 3 (int) as EncodePresetsForSmoothStreaming (enum)?

I don't think you need to check `if (reader.HasRows)` inside the while loop because you've already called `reader.Read()` which means that you've already read a row (which means that there are rows). — Dan, May 02 '13 at 22:21
You'll get that if you have any `null` values in the result set column 3. — Dan, May 02 '13 at 22:25
the contents of my database (currently) has no null values. Previous versions of this code where I check for nulls produces the same error — Damo, May 02 '13 at 22:26
Does the integer actually represent an enumeration? E.g. you have `enum Test { A, B }` and the integer value you're reading is 9999. — ta.speot.is, May 02 '13 at 22:34
the value is written to the database by referencing the same emum, checking the DB, the values are in rage. However I have discovered that removing the database read and strongly typing the enum itself produces the same casting error. So I'm very confused now — Damo, May 02 '13 at 22:42
Seems that this issue is related to using the object initializer for assetItem. If I don't use the initializer, it works fine... how odd. — Damo, May 02 '13 at 22:47

score 2 · Answer 1 · answered May 02 '13 at 22:37

2

Try declaring your enum as follows, this should allow it to correctly cast from an integer to the correct enum value.

public enum Test : int 
{ 
   TestValue1 = 1, 
   TestValue2 = 2, 
   TestValue3 = 3
}

answered May 02 '13 at 22:37

Peter

1,776
13
20

score 1 · Answer 2 · edited May 23 '17 at 11:47

1

You were missing reader.IsDBNull(3) there, so it could be trying to cast DbNull to int, which is invalid.

If you're sure there are no nulls in that column, then make sure that what you're getting from the DB is an int, you can do that by either getting contents as string; or leave it as object, set a break point, and using immediate window in your IDE call value.GetType() which will tell you what exactly you're dealing with.

If you're storing enums as strings in db, then you should use Enum.Parse()

Also this is similar: Exception: Specified cast is not valid

edited May 23 '17 at 11:47

Community

1
1

answered May 02 '13 at 22:26

Mike Trusov

1,958
1
15
23

the contents of my database (currently) has no null values. Previous versions of this code where I check for nulls produces the same error. I'll update my example back – Damo May 02 '13 at 22:27
+1 for *You're missing reader.IsDBNull(3) there* even though OP just snuck that code in there. – ta.speot.is May 02 '13 at 22:34
there was nothing to 'sneek' in. You try different things before you post on SO. One of many things to try was remove the null check (novice actions, of course) – Damo May 02 '13 at 22:37
@Damo try using the immediate window to determine the type of object you're getting from DB – Mike Trusov May 02 '13 at 22:46
@Damo Yes, there is. You post the *actual code*, not some hacked up version of it. – ta.speot.is May 02 '13 at 23:23
@ta.speot.is I take your point, how can I expect people to help me when I haven't posted the true reprosentation of what I've done. – Damo May 03 '13 at 17:40

score 1 · Answer 3 · answered May 02 '13 at 22:50

Change the type of the command to stored procedure, and remove the "EXEC" from the command text so the command text is just the stored procedure name:

using (var connection = new SqlConnection(_sqlstring))
{
    using (var command = new SqlCommand("GetAllEncodedMedia", connection))
    {
        try
        {
            connection.Open();
            command.CommandType = CommandType.StoredProcedure;
            var reader = command.ExecuteReader();
            while (reader.Read())
            {
                if (reader.HasRows)
                {
                    var assetItem = new MediaServices.EncodedAssets
                    {
                        Id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0),
                        PublishedName = reader.IsDBNull(1) ? null : reader.GetString(1),
                        PublishUri = reader.IsDBNull(2) ? new Uri("http://www.null.com") : new Uri(reader.GetString(2)),
                        EncodePreset = reader.IsDBNull(3) ? 0 : (MediaServices.EncodePresetsForSmoothStreaming)reader.GetInt32(3),
                        AssetId = reader.IsDBNull(4) ? null : reader.GetString(4),
                        EncoderJobId = reader.IsDBNull(5) ? 0 : reader.GetInt32(5),
                        //EncoderState = reader.IsDBNull(6) ? 0 : (JobState)reader.GetInt32(6),
                        //AssetState = reader.IsDBNull(7) ? 0 : (MediaServices.InternalAssetState)reader.GetInt32(7),
                        GroupId = reader.IsDBNull(8) ? 0 : reader.GetInt32(8),
                        Published = !reader.IsDBNull(2)
                    };
                    listReturn.Add(assetItem);
                }
            }
        }
        catch (Exception ex)
        {
            //error
        }
    }
}

score 0 · Answer 4 · edited May 23 '17 at 10:24

0

Double check the data type of the column, the enum may be stored as a tinyint. How can you convert "tinyint" of t-sql to integer in c#?

Attempt the read the value into a variable prior to casting to verify that the error is not the reading.

edited May 23 '17 at 10:24

Community

1
1

answered Jul 10 '14 at 20:05

Steve

1,995
2
16
25

Specified cast is not valid converting INT to ENUM with SqlDataReader

4 Answers4