Object cannot be cast from DBNull to other types ? C#

Question

I have a database query and when I run the query in MySql, it returns me the value. But I am getting Object cannot be cast from DBNull to other types exception when I run it in code. How can I retrieve the data from code?

public int getLatestReading(string connstr, string gId, string mId, DateTime date)
    {

        int active = 0;
        MySqlConnection myconn = new MySqlConnection(connstr);
        String sqlstr = "Select max(ar.ActualReading) From actualmeterreading ar Inner Join meterreadingrecords mr On mr.ActualMeterReadingID = ar.ID Inner join machinemeterreadinglist mml on mml.ID = ar.MachineMeterReadingId Inner join grouping g on g.ID = mml.Grouping inner join machines m on m.ID = mml.MachineID where g.ID = '" + gId + "' and m.ID = '" + mId + "' and mr.rDate > '" + date + "'";
        MySqlCommand cmd = new MySqlCommand(sqlstr, myconn);
        myconn.Open();
        active = Convert.ToInt32(cmd.ExecuteScalar());
        myconn.Close();
        myconn.Dispose();
        return active;
    }

https://stackoverflow.com/questions/870697/unable-to-cast-object-of-type-system-dbnull-to-type-system-string — Sujith, Jul 27 '17 at 03:19

score 3 · Accepted Answer · edited Jul 30 '18 at 19:49

Use the following piece of code:

public int getLatestReading(string connstr, string gId, string mId, DateTime date)
    {

        int active = 0;
        MySqlConnection myconn = new MySqlConnection(connstr);
        String sqlstr = "Select max(ar.ActualReading) From actualmeterreading ar Inner Join meterreadingrecords mr On mr.ActualMeterReadingID = ar.ID Inner join machinemeterreadinglist mml on mml.ID = ar.MachineMeterReadingId Inner join grouping g on g.ID = mml.Grouping inner join machines m on m.ID = mml.MachineID where g.ID = '" + gId + "' and m.ID = '" + mId + "' and mr.rDate > '" + date + "'";
        MySqlCommand cmd = new MySqlCommand(sqlstr, myconn);
        myconn.Open();
        active = !Convert.IsDBNull(cmd.ExecuteScalar())?Convert.ToInt32(cmd.ExecuteScalar()):0;
        myconn.Close();
        myconn.Dispose();
        return active;
    }

score 0 · Answer 2 · answered Jul 27 '17 at 03:51

0

One way to fix the issue is to ensure the query never returns null. You can do this using the MySql ifnull function by changing your query like this:

Select ifnull(max(ar.ActualReading), 0) ...

answered Jul 27 '17 at 03:51

CodingYoshi

25,467
4
62
64

Object cannot be cast from DBNull to other types ? C#

2 Answers2