Data is Null. This method or property cannot be called on Null values in Asp.Net and MySql
Dung Do Tien
May 13 2022
588
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.
-
N0
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 tblbooksSolution 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.
* Type maximum 2000 characters.
* All comments have to wait approved before display.
* Please polite comment and respect questions and answers of others.