Object cannot be cast from DBNull to other types

Question

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;
    }
}

Note that your `ReplaceNull` overloads on `double` and `DateTime` are worthless. Values of these types can never be null and will therefore never pass the `if (value == null)` condition. — Anthony Pegram, May 23 '11 at 14:35
Unless they're made nullable of course, but that's another story. — BoltClock, May 23 '11 at 14:36
And have you stepped through the method? In Visual Studio, consider breaking on all exceptions (Ctrl+Alt+E) and see what line actually causes the problem. — Anthony Pegram, May 23 '11 at 14:36
Whoa, whoa! Don't "throw ex", just "throw" or you will lose some of your trace. That doesn't solve your problem but its a good practice to get in the habit of doing. — Jeff LaFay, May 23 '11 at 14:58
^^ I am always learning something new. Just when I think I knew what I needed to know about exceptions... — eaglei22, Dec 07 '16 at 13:39

score 81 · Accepted Answer · edited May 06 '16 at 06:55

81

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

edited May 06 '16 at 06:55

M.S.

4,283
1
19
42

answered May 23 '11 at 14:37

Bala R

107,317
23
199
210

21

Maybe better `Convert.IsDBNull()`? – abatishchev May 23 '11 at 14:50
I have multiple integer values in my query. Can I somehow shorten the process or I have to do this for everyone? – Alexander Apr 10 '18 at 16:25
1

@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")); – computercarguy May 09 '19 at 17:35

score 18 · Answer 2 · answered May 23 '11 at 14:38

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
}

score 5 · Answer 3 · answered May 23 '11 at 14:39

5

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...

answered May 23 '11 at 14:39

Daniel Hilgarth

171,043
40
335
443

score 4 · Answer 4 · answered Mar 26 '14 at 22:03

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;
}

answered Mar 26 '14 at 22:03

CrazyPyro

3,257
3
30
39

score 4 · Answer 5 · answered Jun 07 '16 at 11:33

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.

score 1 · Answer 6 · answered Aug 11 '22 at 08:23

1

That's also worked for me:

reservation.IsFull = rdr["isfull"] is DBNull ? false : (bool)rdr["isfull"];

answered Aug 11 '22 at 08:23

michant

11
1

score 0 · Answer 7 · answered Jun 02 '17 at 17:09

For others that arrive on this page from google:

DataRow also has a function .IsNull("ColumnName")

    public DateTime? TestDt; 
    public Parse(DataRow row)
    {
        if (!row.IsNull("TEST_DT"))
            TestDt = Convert.ToDateTime(row["TEST_DT"]);
    }

score 0 · Answer 8 · answered Jul 02 '19 at 11:24

I faced this problem today and resolved it by checking the mapping class. I had a class which had 5 properties to which 5 columns returned from stored procedure were being mapped. Those properties were non-nullable and due to which i was getting the error. I made them nullable and the issue resolved.

    public int? Pause { get; set; }
    public int? Delay { get; set; }
    public int? Transition { get; set; }
    public int? TransitionTime { get; set; }
    public int? TransitionResolution { get; set; }

Added "?" with data type to made them nullable.

Secondly i also added isNull check in the stored procedure as well as follows:

    isnull(co_pivot.Pause, 0) as Pause,
    isnull(co_pivot.Delay, 0) as Delay,
    isnull(co_pivot.Transition, 0) as Transition,
    isnull(co_pivot.TransitionTime, 0) as TransitionTime,
    isnull(co_pivot.TransitionResolution, 0) as TransitionResolution

Hope this helps someone.

Object cannot be cast from DBNull to other types

8 Answers8

Linked

Related