Object cannot be assigned to other types from DBNull

Question

I get minimum number in my database. But when no data in my database I get this error.

System.InvalidCastException: 'The object cannot be assigned to other types from DBNull.'

Code:

SqlCommand cmd = new SqlCommand("SELECT MAX(GidenEvrakSira) FROM GidenEvrak", con);    
SqlCommand smd = new SqlCommand("Select Min(GidenEvrakSira) FROM GidenEvrak Where UserID is null", con);    

con.Open();    

maxnum = Convert.ToInt32(cmd.ExecuteScalar());
minum = Convert.ToInt32(smd.ExecuteScalar());    

con.Close();

Sorry. What is the error message you are getting? Please edit your question and add the exact text of the error message. — RyanNerd, Sep 08 '19 at 10:52
If there is no data, `Min(...)` and `Max(...) ` return the value DBNull. You have to check for such NULL values before handing the result to `Convert.ToInt32` — Klaus Gütter, Sep 08 '19 at 10:53
Cast DBNull to null. So try something like this : var results = (cmd.ExecuteScalar == DBNull.Value) ? null : cmd.ExecuteScalar — jdweng, Sep 08 '19 at 12:45
Yes it work. Thanks @jdweng var results = (cmd.ExecuteScalar() == DBNull.Value) ? null : cmd.ExecuteScalar(); — kozyalcin, Sep 09 '19 at 13:00

score 1 · Accepted Answer · edited Oct 04 '19 at 19:44

1

jdweng suggestion in comment is working:

var results = (cmd.ExecuteScalar() == DBNull.Value) ? null : cmd.ExecuteScalar();

edited Oct 04 '19 at 19:44

Jean-François Fabre

137,073
23
153
219

answered Sep 09 '19 at 13:02

kozyalcin

83
10

This executes the command twice, not a good practice, spacially if the command writes data. Consider placing the output of `cmd.ExecuteScalar()` in a variable. – Jonathan Larouche Sep 25 '19 at 22:02

score 0 · Answer 2 · answered Sep 08 '19 at 11:13

At run-time (tested under ODP.NET but should be the same under any ADO.NET provider), it behaves like this:

If the row does not exist, the result of cmd.ExecuteScalar() is null, which is then casted to a null string and assigned to getusername. If the row exists, but has NULL in username (is this even possible in your DB?), the result of cmd.ExecuteScalar() is DBNull.Value, resulting in an InvalidCastException. In any case, the NullReferenceException should not be possible, so your problem probably lies elsewhere.

No. I'm sure the problem is here. It just needs to be in an if loop. I couldn't find this way. :( — kozyalcin, Sep 08 '19 at 11:37

Jonathan Larouche · Answer 3 · 2019-10-02T14:57:45.230

Change your query to always return a value by surrounding the max/min with ISNULL function.

SqlCommand cmd = new SqlCommand("SELECT isnull(MAX(GidenEvrakSira),0) FROM GidenEvrak", con);    
SqlCommand smd = new SqlCommand("Select isnull(Min(GidenEvrakSira),0) FROM GidenEvrak Where UserID is null", con);    

con.Open();    

maxnum = Convert.ToInt32(cmd.ExecuteScalar());
minum = Convert.ToInt32(smd.ExecuteScalar());    

con.Close();

Object cannot be assigned to other types from DBNull

3 Answers3

Linked

Related