Conversion error from database smallint into C# nullable int

Question

I have a security_role_cd column in database of smallint datatype. I am selecting this column using following code into a nullable int variable.

I am getting following error:

Error 3 Type of conditional expression cannot be determined because there is no implicit conversion between 'null' and 'short'

What is the correct code to overcome this error?

SELECT R.security_role_cd  FROM Security_Role R WHERE security_role_name = 'Admin'

C#

        int? roleID = null;
        string commandText = "SELECT R.security_role_cd  FROM Security_Role R WHERE security_role_name = @roleName";
        SqlCommand command = new SqlCommand(commandText, connection);
        command.CommandType = System.Data.CommandType.Text;
        command.Parameters.AddWithValue("@roleName",roleName);
        SqlDataReader readerRole = command.ExecuteReader();
        if (readerRole.HasRows)
        {
            while (readerRole.Read())
            {
                roleID = readerRole.GetInt16(0) == 0 ? null : readerRole.GetInt16(0) ;

            }
        }
        readerRole.Close();

score 6 · Accepted Answer · answered Nov 07 '12 at 08:26

It just needs to know how to type the null:

roleID = readerRole.GetInt16(0) == 0 ? (int?)null : (int)readerRole.GetInt16(0);

Personally I'd cache the value though:

int tmp = readerRole.GetInt16(0); // implicit widening to int here
roleID = tmp == 0 ? (int?)null : tmp;

Although I'd also call into doubt the wisdom of turning a 0 into a null - better to use IsDBNull - something like:

if(reader.IsDBNull(0)) {
    roleID = null;
} else {
    roleID = (int)readerRole.GetInt16(0);
}

codingbiz · Answer 2 · 2012-11-09T12:07:17.367

1

Try this

roleID = readerRole.GetInt16(0) == 0 ? (int?) null : readerRole.GetInt16(0) ;

According to the documentation of ternary operator, the data type on either side of the colon (:) has to be the same. As you had it without casting, the type of null could not be determined (i.e. if nullable int, or null string, or null object)

Update

roleID = readerRole.GetInt16(0) == 0 ? (int?) null : readerRole.GetInt32(0) ;

edited Nov 09 '12 at 12:07

answered Nov 07 '12 at 08:26

codingbiz

26,179
8
59
96

Thanks. But I ran into another datatype problem http://stackoverflow.com/questions/13306275/datatype-returned-varies-based-on-data-in-table/13306322#comment18147151_13306322 – LCJ Nov 09 '12 at 11:12

Conversion error from database smallint into C# nullable int

2 Answers2

Linked