Object cannot be assigned to other types from DBNull

2
votes

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();
3
c#sql-serverasp.net-mvcdbnullexecutescalar
Sorry. What is the error message you are getting? Please edit your question and add the exact text of the error message. - RyanNerd
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
Cast DBNull to null. So try something like this : var results = (cmd.ExecuteScalar == DBNull.Value) ? null : cmd.ExecuteScalar - jdweng
Yes it work. Thanks @jdweng var results = (cmd.ExecuteScalar() == DBNull.Value) ? null : cmd.ExecuteScalar(); - Kubilay Özyalçın

3 Answers

1
votes

jdweng suggestion in comment is working:

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

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.

0
votes

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();