Unable to cast object of type ‘System.DBNull’ to type ‘System.DateTime’

Hi im trying to make a notification system. Basically when i add a new row, a notification will be shown on my notificationspage realtime (i use signalR with razor pages asp.net). But for some reason when i get on that page, i get these errors: Unable to cast object of type ‘System.DBNull’ to type ‘System.DateTime’.
at myWebApp.Controllers.SpeedListener.GetAlarmList() in myWebAppControllersSpeedListener.cs:line 83
at myWebApp.Controllers.SpeedListener.ListenForAlarmNotifications() in myWebAppControllersSpeedListener.cs:line 43

So apparently theres a problem at the controller.
Here is the code of the controller

namespace myWebApp.Controllers
{
    public class SpeedListener :Controller
    {
        private IHubContext<speedalarmhub> _hubContext;
        private IMemoryCache _cache;
        public SpeedListener(IHubContext<speedalarmhub> hubContext,IMemoryCache cache)
        {
            _hubContext = hubContext;
            _cache = cache; 
        }
        public static string  cs = Database.Database.Connector();
        public void ListenForAlarmNotifications()
        {
            NpgsqlConnection conn = new NpgsqlConnection(cs);
            conn.StateChange += conn_StateChange;
            conn.Open();
            var listenCommand = conn.CreateCommand();
            listenCommand.CommandText = $"listen notifytickets;";
            listenCommand.ExecuteNonQuery();
            conn.Notification += PostgresNotificationReceived;
            _hubContext.Clients.All.SendAsync(this.GetAlarmList());
            while (true)
            {
                conn.Wait();
            }
        }
        private void PostgresNotificationReceived(object sender, NpgsqlNotificationEventArgs e)
        {

            string actionName = e.Payload.ToString();
            string actionType = "";
            if (actionName.Contains("DELETE"))
            {
                actionType = "Delete";
            }
            if (actionName.Contains("UPDATE"))
            {
                actionType = "Update";
            }
            if (actionName.Contains("INSERT"))
            {
                actionType = "Insert";
            }
            _hubContext.Clients.All.SendAsync("ReceiveMessage", this.GetAlarmList());
        }
        public string GetAlarmList()
        {
            List<NotificationModel> not = new List<NotificationModel>();
            using var con = new NpgsqlConnection(cs);
            {
                string query = "Select datumnu, bericht FROM notification";
                using NpgsqlCommand cmd = new NpgsqlCommand(query, con);
                {
                    cmd.Connection = con;
                    con.Open();
                    using (NpgsqlDataReader dr = cmd.ExecuteReader())
                    {
                        
                        while (dr.Read())
                        {
                            not.Add(new NotificationModel { Datenow = ((DateTime) dr["datumnu"]).ToString("yyyy/MM/dd"), Bericht = dr["bericht"].ToString() });
                        }
                    }
                    
                    con.Close();
                }
            }
            _cache.Set("notification", SerializeObjectToJson(not));
            return _cache.Get("notification").ToString();
        }
        public String SerializeObjectToJson(Object notification)
        {
            try
            {
                
                return  Newtonsoft.Json.JsonConvert.SerializeObject(notification);
            }
            catch (Exception) { return null; }
        }
        private void conn_StateChange(object sender, System.Data.StateChangeEventArgs e)
        {

            _hubContext.Clients.All.SendAsync("Current State: " + e.CurrentState.ToString() + " Original State: " + e.OriginalState.ToString(), "connection state changed");
        }
    }
}

If needed here is my hub
namespace myWebApp.Hubs
{
     
    public class speedalarmhub : Hub
    {
        private IMemoryCache _cache;
        private IHubContext<speedalarmhub> _hubContext;
         public speedalarmhub(IMemoryCache cache, IHubContext<speedalarmhub> hubContext)
        {
            _cache = cache;
            _hubContext = hubContext; 
        }

        public async Task SendMessage()
        {
            if (!_cache.TryGetValue("notification", out string response))
            {
                SpeedListener speedlist = new SpeedListener(_hubContext,_cache);
                speedlist.ListenForAlarmNotifications();
                string jsonspeedalarm = speedlist.GetAlarmList();
                _cache.Set("notification", jsonspeedalarm);
                await Clients.All.SendAsync("ReceiveMessage", _cache.Get("notification").ToString());
            }
            else
            {
                await Clients.All.SendAsync("ReceiveMessage", _cache.Get("notification").ToString());
            }
        }

    }
}

the table name in postgresql is called ‘notification’, i have two column called ‘bericht’ with type varchar and ‘datumnu’ with type date.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Edit suggested by mjwills
DateTime don’t accept null value. Check if the value is null and assigne a default value

while (dr.Read())
{
    not.Add(new NotificationModel { Datenow = ((DateTime) dr["datumnu"]).ToString("yyyy/MM/dd"), Bericht = dr["bericht"].ToString() });
}

Become

while (dr.Read())
{
    DateTime defaultDateTime = DateTime.Now;
    if(dr.IsNull("datumnu")){
        defaultDateTime = (DateTime)dr["datumnu"];
    }

    not.Add(new NotificationModel { Datenow = defaultDateTime, Bericht = dr["bericht"].ToString() });
}

in single line

while (dr.Read())
{
    not.Add(new NotificationModel { Datenow = (dr.IsNull("datumnu") ? DateTime.Now : (DateTime)dr["datumnu"]), Bericht = dr["bericht"].ToString() });
}


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x