SqlDataReader no data, but HasRow is true

Question

I read data from SQL database by ExecuteReader(). There are no data fulfill conditions, but HasRow returns true. When I try to read data from reader I got exception: 'Data is Null. This method or property cannot be called on Null values.'

SqlDataReader reader = command.ExecuteReader();     
if (reader.HasRows)
            try
            {
                if (reader.Read())
                {
                        string aa = reader.GetFieldType(0).Name; // aa returns 'DateTime'
                        dateStart.MinDate = reader.GetDateTime(0); //exception on this line
                        dateEnd.MinDate = reader.GetDateTime(0);
                        dateStart.Value = reader.GetDateTime(0);
                }
            }
            finally
            {
               reader.Close();
            }

Thanks in advance Usjwo

Have you checked the value with `if (reader.IsDBNull(0))` or ternary operator? You can't use `GetDateTime` if the corresponding field value is null. — Tetsuya Yamamoto, Aug 23 '17 at 03:03
This could help you ----> [Data is Null. This method or property cannot be called on null values](https://stackoverflow.com/questions/24581305/data-is-null-this-method-or-property-cannot-be-called-on-null-values-using-com) — Jixone, Aug 23 '17 at 03:07

score 0 · Answer 1 · answered Aug 23 '17 at 03:22

If the data inside zero index of SqlDataReader contains DBNull, you can't use GetDateTime method directly from it since DBNull.Value can't be directly cast to DateTime. You can check it using IsDBNull with ternary operator & a Nullable<DateTime> variable to store GetDateTime result before assigning to other properties (see also this example):

using (SqlDataReader reader = command.ExecuteReader())
{
    if (reader.HasRows)
    {
        try
        {
            while (reader.Read())
            {
                DateTime? minDate = reader.IsDBNull(0) ? (DateTime?)null : reader.GetDateTime(0);
                if (minDate != null)
                {
                    dateStart.MinDate = minDate.Value;
                    dateEnd.MinDate = minDate.Value;
                    dateStart.Value = minDate.Value;
                }
            }
        }
        finally
        {
            reader.Close();
        }
    }
}

SqlDataReader no data, but HasRow is true

1 Answers1