Object cannot be cast from DBNull to other types
Solution 1
I'm thinking that your output parameter is coming back with a DBNull value. Add a check for that like this
var outputParam = dataAccCom.GetParameterValue(IDbCmd, "op_Id");
if(!(outputParam is DBNull))
DataTO.Id = Convert.ToInt64(outputParam);
Solution 2
I suspect that the line
DataTO.Id = Convert.ToInt64(dataAccCom.GetParameterValue(IDbCmd, "op_Id"));
is causing the problem. Is it possible that the op_Id
value is being set to null by the stored procedure?
To Guard against it use the Convert.IsDBNull
method. For example:
if (!Convert.IsDBNull(dataAccCom.GetParameterValue(IDbCmd, "op_Id"))
{
DataTO.Id = Convert.ToInt64(dataAccCom.GetParameterValue(IDbCmd, "op_Id"));
}
else
{
DataTO.Id = ...some default value or perform some error case management
}
Solution 3
You need to check for DBNull
, not null
. Additionally, two of your three ReplaceNull
methods don't make sense. double
and DateTime
are non-nullable, so checking them for null
will always be false
...
Solution 4
TryParse is usually the most elegant way to handle this type of thing:
long temp = 0;
if (Int64.TryParse(dataAccCom.GetParameterValue(IDbCmd, "op_Id").ToString(), out temp))
{
DataTO.Id = temp;
}
Solution 5
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.
Jaison
Updated on July 09, 2022Comments
-
Jaison almost 2 years
Object cannot be cast from DBNull to other types.
I have a following function which throws the above error. I am handling all nulls in store procedure and in the C# code.
So where is it getting this error?
I can see the error in the catch block. But i am not understanding which line in the following create() getting the error.
public Boolean Create(DataTO DataTO) { IDbTrans transaction = null; IDbCmd IDbCmd; string EncryptedPassword = Encrypt(DataTO.txtPwd); Base dataAccCom = null; try { dataAccCom = Factory.Create(); dataAccCom.OpenConnection(); transaction = dataAccCom.BeginTransaction(); IDbCmd = dataAccCom.CreateCommand("sp_Register", true); dataAccCom.AddParameter(IDbCmd, "op_Id", DbType.Int64, 0, ParameterDirection.Output); dataAccCom.AddParameter(IDbCmd, "p_dlstTitle", DbType.String, ReplaceNull(DataTO.dlstTitle)); dataAccCom.AddParameter(IDbCmd, "p_txtFirstName", DbType.String, ReplaceNull(DataTO.txtFirstName)); dataAccCom.AddParameter(IDbCmd, "p_txtMiddleName", DbType.String, ReplaceNull(DataTO.txtMiddleName)); dataAccCom.AddParameter(IDbCmd, "p_txtLastName", DbType.String, ReplaceNull(DataTO.txtLastName)); dataAccCom.AddParameter(IDbCmd, "p_txtDob", DbType.DateTime, DataTO.txtDob); dataAccCom.AddParameter(IDbCmd, "p_txtDesig", DbType.String, ReplaceNull(DataTO.txtDesig)); dataAccCom.AddParameter(IDbCmd, "p_txtOFlatNo", DbType.String, ReplaceNull(DataTO.txtOFlatNo)); dataAccCom.AddParameter(IDbCmd, "p_txtOBuild", DbType.String, ReplaceNull(DataTO.txtOBuild)); dataAccCom.AddParameter(IDbCmd, "p_txtOPost", DbType.String, ReplaceNull(DataTO.txtOPost)); dataAccCom.AddParameter(IDbCmd, "p_txtOArea", DbType.String, ReplaceNull(DataTO.txtOArea)); dataAccCom.AddParameter(IDbCmd, "p_txtOCity", DbType.String, ReplaceNull(DataTO.txtOCity)); dataAccCom.AddParameter(IDbCmd, "p_txtRBuild", DbType.String, ReplaceNull(DataTO.txtRBuild)); dataAccCom.AddParameter(IDbCmd, "p_txtRPost", DbType.String, ReplaceNull(DataTO.txtRPost)); dataAccCom.AddParameter(IDbCmd, "p_txtUserID", DbType.String,ReplaceNull(DataTO.txtUserID)); dataAccCom.AddParameter(IDbCmd, "p_txtPwd", DbType.String, ReplaceNull(EncryptedPassword)); dataAccCom.ExecuteNonQuery(IDbCmd); DataTO.Id = Convert.ToInt64(dataAccCom.GetParameterValue(IDbCmd, "op_Id")); transaction.Commit(); return true; } catch (System.Exception ex) { if (transaction != null) { transaction.Rollback(); } throw ex; } finally { transaction = null; if (dataAccCom != null) { dataAccCom.CloseConnection(); } dataAccCom = null; IDbCmd = null; } } public string ReplaceNull(string value) { if (value == null) { return ""; } else { return value; } } public DateTime ReplaceNull(DateTime value) { if (value == null) { return DateTime.Now; } else { return value; } } public double ReplaceNull(double value) { if (value == null) { return 0.0; } else { return value; } }
-
abatishchev almost 13 yearsMaybe better
Convert.IsDBNull()
? -
Alexander about 6 yearsI have multiple integer values in my query. Can I somehow shorten the process or I have to do this for everyone?
-
computercarguy about 5 years@Alexander, I hope you found an answer before now, but it can get pretty ugly to get shorter than this, but using s ternary would work: DataTO.Id = dataAccCom.GetParameterValue(IDbCmd, "op_Id") == DBNull.Value ? null : Convert.ToInt64(dataAccCom.GetParameterValue(IDbCmd, "op_Id"));