error Object cannot be cast from DBNull to other types.

Question

Hi guys i have problem
Firstly this is all my code
Function MaxTotalQuantity
public int MaxTotalQuantity(string ConnectionString)
{

SqlConnection con = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select max(TotalQuantity) from dbo.EndWork";
con.Open();
int commit = Convert.ToInt32(cmd.ExecuteScalar());
con.Close();
return commit;
}
Calling function
Sales.SalesClass SalesClass4 = new Sales.SalesClass();
int TotalQuantity = SalesClass4.MaxTotalQuantity("Data Source=192.168.1.3;Initial Catalog=yamo;User ID=admin;Password=2233;Connection Lifetime=3;Max Pool Size=3;Connection Timeout=30");
label10.Text = TotalQuantity.ToString();

End work table
ID intUnchecked
QunatityEndintChecked
Positionnvarchar(50)Checked
IssentintChecked
TotalQuantityintChecked
Unchecked
But error show to me when run code it give me
error Object cannot be cast from DBNull to other types.
Some times value found in TotalQuantity is null
What i need is to convert null to 0 when is null is found show as 0 and exception not show
How to solve this problem

Karthik Elumalai · Answer

How to Resolve the error Object cannot be cast from DBNull to other types. Reason for the error: In an object-oriented programming language null means the absence of a reference to an object. DBNull represents an uninitialized variant or nonexistent database column. Source:MSDN Actual Code which I faced error: Before changed the code: if ( ds. Tables [ 0 ]. Rows [ 0 ][ 0 ] == null ) // Which is not working { seqno = 1 ; } else { seqno = Convert . ToInt16 (ds. Tables [ 0 ]. Rows [ 0 ][ 0 ]) + 1 ; } After changed the code: if ( ds. Tables [ 0 ]. Rows [ 0 ][ 0 ] == DBNull . Value ) //which is working properly { seqno = 1 ; } else { seqno = Convert . ToInt16 (ds. Tables [ 0 ]. Rows [ 0 ][ 0 ]) + 1 ; } Conclusion: when the database value return the null value we recommend to use the DBNull class instead of just specifying as a null like in C# language. Please let me know your feedback Thanks

Gowtham Rajamanickam · Answer

var queryResult = cmd.ExecuteScalar(); int commit = 0; if (queryResult != DBNull.Value) { commit = Convert.ToInt32(cmd.ExecuteScalar()); }

Manoj Bhoir · Answer

Hi You can handle Null value either in your code or in your SQL Query. If you want to check null values in code then in your code replace int commit = Convert.ToInt32(cmd.ExecuteScalar()); With var queryResult = cmd.ExecuteScalar(); int commit = 0; if (queryResult != DBNull .Value) { commit = Convert .ToInt32(cmd.ExecuteScalar()); } OR you can use ISNULL in your SQL Query Select ISNULL(MAX(TotalQuantity)0) From dbo.EndWork

Khargesh Rajput · Answer

use cmd.CommandText = select isnull(MAX(TotalQuantity)0) from dbo.EndWork;

Ranjit Powar · Answer

Try this SqlDataAdapter da=new SqlDataAdapter( select max(TotalQuantity) from dbo.EndWork con); DataTable dt=new DataTable(); da.Fill(dt); int commit=0; if(!string.IsNullOrEmpty(dt.Rows[0][0].ToString())) { commit=Convert.ToInt32(dt.Rows[0][0].ToString()); }

ahmed sa · Answer

Exception error show in this line int commit = Convert.ToInt32(cmd.ExecuteScalar()); How to solve