Data is Null. This method or property cannot be called on Null values in Asp.Net and MySql

Dung Do Tien May 13 2022 247

Hello, I have a final project at my school. I created a project to help manage a book store.  I'm using .Net 6 and the MySql database to help store data.

I need to get a list of books, I code it like this:

 public async Task<List<BookInfoEntities>> GetBoooks(int top)
{
    string connectionString = MySqlConnectorHelper.GetConnectionString(ConnectionEntity.DBPosition.Default);
    List<BookInfoEntities> result = new List<BookInfoEntities>();
    StringBuilder sqlQueries = new StringBuilder();
    sqlQueries.Append("SELECT bookname, author, publishdate, timepublish, addresspublish, status FROM tblbooks ");
    sqlQueries.Append("LIMIT  " + top);

    using (MySql.Data.MySqlClient.MySqlDataReader reader = await MySql.Data.MySqlClient.MySqlHelper.ExecuteReaderAsync(connectionString, sqlQueries.ToString()))
    {
        if (reader.HasRows)
        {
            while (reader.Read())
            {
                BookInfoEntities book = new BookInfoEntities();
                book.BookName = reader.GetString("bookname").ToString();
                book.Author = reader.GetString("author").ToString();
                book.PublishDate = reader.GetDateTime("publishdate").ToString();
                book.TimePublish = reader.GetInt32("timepublish");
                book.AddressPublish = reader.GetString("addresspublish"); 
                book.Status = reader.GetInt32("status"); 
                result.Add(book);
            }
        }
    }
    return result;
}

But when calling this method, I got an exception throw System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.

 System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.
   at MySql.Data.MySqlClient.MySqlDataReader.GetFieldValue(Int32 index, Boolean checkNull)
   at MySql.Data.MySqlClient.MySqlDataReader.GetString(Int32 i)
   at MySql.Data.MySqlClient.MySqlDataReader.GetString(String column)
  --- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()

Is data in my database null? why MySqlHelper not auto-cast it?

Thanks for any solution.

Have 1 answer(s) found.
  • N

    Nora Luu May 13 2022

    The Books table has the null value. but your property in c# is not null,  I provide three solutions for you:

    Solution 1: Use IFNULL() method in MySQL to return a default value if the column value is null.

     SELECT IFNULL(bookname, '') AS bookname FROM tblbooks

    Solution 2: Use IsDBNull() method in C# to check

     BookInfoEntities book = new BookInfoEntities();
    book.BookName = reader.IsDBNull(0) ? "" : reader.GetInt32(0);
    book.Author = reader.IsDBNull(1) ? "" : reader.GetInt32(1);

    Solution 3: Change the properties of your model to nullable

     public class BookInfoEntities
    {
        public string BookName { get; set; }
        public string Author { get; set; }
        public DateTime? PublishDate { get; set; } // add more ? to help set this property is nullable
    }

    I prefer solutions 1 & 3.

Leave An Answer
* NOTE: You need Login before leave an answer

* Type maximum 2000 characters.

* All comments have to wait approved before display.

* Please polite comment and respect questions and answers of others.

Popular Tips

X Close