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();

Marc Gravell · Accepted Answer · 2012-11-07 08:26:02Z

6

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);
}

answered Nov 7, 2012 at 8:26

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

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

Comments

codingbiz · Accepted Answer · 2012-11-09 12:07:17Z

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 9, 2012 at 12:07

answered Nov 7, 2012 at 8:26

codingbiz

26.4k9 gold badges62 silver badges99 bronze badges

1 Comment

LCJ Over a year ago

Thanks. But I ran into another datatype problem stackoverflow.com/questions/13306275/…

Collectives™ on Stack Overflow

Conversion error from database smallint into C# nullable int

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related