cannot insert DBNULL value into sql server

Question

Why i get this error?

Object cannot be cast from DBNull to other types.

when i inserted null value for a numeric datatype in sql server 2005

Here is my code,

 if (MeasurementTr.Visible == true)
    {
        materialIn.measurementId = Convert.ToInt64(DlMeasurement.SelectedValue.ToString());
    }
    else
    {
        materialIn.measurementId = Convert.ToInt64(DBNull.Value);
    }

EDIT :

Is this right??

 materialIn.measurementId = (Convert.ToInt64(DlMeasurement.SelectedValue.ToString()) != 0) ? Convert.ToInt64(DlMeasurement.SelectedValue.ToString()) : 0;

No. you cannot convert DBNull to Int64. You need to validate first that the object value isn't DBNull! — Fitzchak Yitzchaki, Jan 26 '10 at 05:20
It's the `else` block that's the problem. I'm sure that the `if` block is fine (`ToString` can never return `DBNull.Value`). As I said - use the `SetNull` or `SetmeasurementIdNull` method on the `DataRow`, if that's what it is, or just set it to `null` or `default(Int64)` if it's non-nullable. — Aaronaught, Jan 26 '10 at 05:29

score 5 · Accepted Answer · answered Jan 26 '10 at 05:05

You cannot convert DBNull.Value to Int64 as your code attempts to do in Convert.ToInt64. DBNull.Value means "unknown" or "nonexistent" and therefore there is no logical conversion to any other data type.

If the materialIn instance is some form of strongly-typed DataRow, it will have a SetMeasurementIdNull method, which is what you need to use. If you're using Linq to SQL or EF, you shouldn't need to deal with DBNull at all, you would instead just write materialIn.measurementId = null.

score 1 · Answer 2 · answered Jan 26 '10 at 05:02

1

Because that Object cannot be cast from DBNull to other types.

So what you need to do is to check the value for DBNull.

materialIn.measurementId = DlMeasurement.SelectedValue == DBNull.Value ? 0 : Convert.ToInt64(DlMeasurement.SelectedValue.ToString());

answered Jan 26 '10 at 05:02

Fitzchak Yitzchaki

9,095
12
56
96

@Mendy Operator '==' cannot be applied to operands of type 'string' and 'System.DBNull' – bala3569 Jan 26 '10 at 05:05
But SelectedValue is object, isn't it? – Fitzchak Yitzchaki Jan 26 '10 at 05:09

score 0 · Answer 3 · answered Jan 26 '10 at 05:06

0

DBNull doesn't convert to an Int64. You will need to use a nullable int and then store null in the value or do something with the value to represent null such as 0 that can be stored in your property.

answered Jan 26 '10 at 05:06

Kelsey

47,246
16
124
162

cannot insert DBNULL value into sql server

3 Answers3