none
InvalidCastException when SqlReader get data RRS feed

  • Question

  • Hi,

    I am uisng this code to get data from the server:

    while (sql_reader.Read())
    {  
        var wowModel = new HomeAnimals
        {
            category_name = sql_reader.GetString(sql_reader.GetOrdinal("category_name")),
            animal_price = sql_reader.GetDecimal(sql_reader.GetOrdinal("animal_price")),
            is_sold = sql_reader.GetBoolean(sql_reader.GetOrdinal("is_sold"))
        };
    
        wowllist.Add(wowModel);
    }
    

    but sometimes the data is NULL in the server and therefore I am getting below error:

    System.InvalidCastException: Object cannot be cast from DBNull to other types.

    How can I avoid that to make sure data will be retrieved in all case, if null and if not..

    Thanks,

    Jassim

    Monday, August 20, 2018 1:11 AM

Answers

  • Well, two things here.

    First, strings are reference types so can already be null. This is why they cannot be made nullable by saying "string?"; it would be redundant. That's what that error is telling you.

    Second, the code you supplied used "GetBoolean" to get the value of the sub_category_name (which I have to admit looked a bit wrong given the column name, but I didn't want to assume).

    If this is in fact a string column then change to:

    sub_category_name = !sql_reader.IsDBNull(sql_reader.GetOrdinal("sub_category_name")) ? sql_reader.GetString(sql_reader.GetOrdinal("sub_category_name")) : null

    (And change your sub_category_name back to a string type).

    • Marked as answer by Jassim Rahma Monday, August 20, 2018 2:36 PM
    Monday, August 20, 2018 1:07 PM

All replies

  • Use .IsDBNull() to guard against null values.

    while (sql_reader.Read())
    {  
        var wowModel = new HomeAnimals
        {
            category_name = sql_reader.GetString(sql_reader.GetOrdinal("category_name")),
            animal_price = !sql_reader.IsDBNull(sql_reader.GetOrdinal("animal_price")) ? sql_reader.GetDecimal(sql_reader.GetOrdinal("animal_price")) : 0,
            is_sold = !sql_reader.IsDBNull(sql_reader.GetOrdinal("is_sold")) ? sql_reader.GetBoolean(sql_reader.GetOrdinal("is_sold")) : false
        };
    
        wowllist.Add(wowModel);
    }


    Monday, August 20, 2018 2:48 AM
    Answerer
  • but how can I set to null if value is null?

    I tried this:

    sub_category_name = !sql_reader.IsDBNull(sql_reader.GetOrdinal("sub_category_name")) ? sql_reader.GetBoolean(sql_reader.GetOrdinal("sub_category_name")) : null
    

    but getting

    /Users/jassim/Projects/Manoos/Manoos/Home.xaml.cs(41,41): Error CS0173: Type of conditional expression cannot be determined because there is no implicit conversion between 'bool' and '<null>' (CS0173) (Manoos)

    Monday, August 20, 2018 11:51 AM
  • If you have a column that is potentially null, then it is up to your application to decide how to deal with that.

    For example, you could declare sub_category_name as a nullable boolean instead of a boolean:

    bool? sub_category_name;

    Bear in mind that if you do this then you will have to deal with the possibility of your variable sub_category_name being null in later code.

    Or, you could default it to false instead of null (i.e. if the value in the database is null or false, it will just set sub_category_name to false in either case).

    sub_category_name = !sql_reader.IsDBNull(sql_reader.GetOrdinal("sub_category_name")) 
    ? sql_reader.GetBoolean(sql_reader.GetOrdinal("sub_category_name"))
    : false


    Or...something else entirely. It really depends on what a null value in the database means. If it doesn't mean anything, and you are able to do so, then I would recommend that you update the column to set any existing nulls to false and then make the column non-nullable. Which would avoid this problem completely!


    • Edited by RJP1973 Monday, August 20, 2018 12:06 PM
    Monday, August 20, 2018 12:05 PM
  • but I already set the sub_category_name value like this:

    public class HomeAnimals
    {
        public int animal_id { get; set; }
        public string animal_guid { get; set; }
        public string category_name { get; set; }
        public string category_name_ar { get; set; }
        public string? sub_category_name { get; set; }
        public string sub_category_name_ar { get; set; }
        public decimal animal_price { get; set; }
        public bool? is_sold { get; set; }
    }

    which was not accepted with error:

    /Users/jassim/Projects/Manoos/Manoos/Home.xaml.cs(28,28): Error CS0453: The type 'string' must be a non-nullable value type in order to use it as parameter 'T' in the generic type or method 'Nullable<T>' (CS0453) (Manoos)

    I want the app to receive the  field as it..

    iif it has value then sub_category_nme should have the value but if the field has null then sub_category_name should be set to null too


    Monday, August 20, 2018 12:14 PM
  • Well, two things here.

    First, strings are reference types so can already be null. This is why they cannot be made nullable by saying "string?"; it would be redundant. That's what that error is telling you.

    Second, the code you supplied used "GetBoolean" to get the value of the sub_category_name (which I have to admit looked a bit wrong given the column name, but I didn't want to assume).

    If this is in fact a string column then change to:

    sub_category_name = !sql_reader.IsDBNull(sql_reader.GetOrdinal("sub_category_name")) ? sql_reader.GetString(sql_reader.GetOrdinal("sub_category_name")) : null

    (And change your sub_category_name back to a string type).

    • Marked as answer by Jassim Rahma Monday, August 20, 2018 2:36 PM
    Monday, August 20, 2018 1:07 PM