DBNull error is coming up

Question

I have a database table as

FruitCrateTable FruitCrateID, FruitCrateName, FruitGarden

Now I also created a Stored procedure as,

CREATE procedure [dbo].[GetFruitCrate]
(
    @FruitCrateID int,
    @FruitCrateName varchar(222)
)
AS

SELECT 
    *
FROM 
    FruitCrateTable
WHERE
    FruitCrateID = @FruitCrateID
and
    FruitCrateName = @FruitCrateName

Now when I try to check if FruitGarden is null or not using DataAccess,

I created instance of row as,

 public myDataAccess.DataAccess.GetFruitCrateRow fruitCrateRow;
 //then

 if (!string.IsNullOrEmpty(fruitCrateRow.FruitGarden))
            {
//do something but i am getting error, how can i fix this issue ?
else { // do something else }

Error,

The value for column &#39;FruitGarden&#39; in table &#39;GetFruitCrate&#39; is DBNull.</message><full>System.Data.StrongTypingException: The value for column &#39;FruitGarden&#39; in table &#39;GetFruitCrate&#39; is DBNull. ---&gt; System.InvalidCastException: Unable to cast object of type &#39;System.DBNull&#39; to type &#39;System.String&#39;.

I left Fruit Garden as Null as it can be null

Update

when i try fruitCrateRow.FruitGarden != DBNull.Value I get Error Operator '!=' cannot be applied to operands of type 'string' and 'System.DBNull

Your stored proc is `[dbo].[GetFruitCrate]`, but you're calling `GetFruitCrateRow`? — Pat Lillis, Jul 05 '13 at 12:28

Raphaël Althaus · Answer 1 · 2013-07-05T12:57:55.727

Well, as stated by the Exception, fuitCraterow.FruitGarden is DBNull, which can't be casted to a string (which happens when you call string.IsNullOrEmpty)

So... test for DbNull

if (!DBNull.Value.Equals(fruitCrateRow.FruitGarden) && 
   !string.IsNullOrEmpty(fruitCraterow.FruitGarden))

to check for DbNull, see msdn

To evaluate database fields to determine whether their values are DBNull, you can pass the field value to the DBNull.Value.Equals method. However, this method is rarely used because there are a number of other ways to evaluate a database field for missing data. These include the Visual Basic IsDBNull function, the Convert.IsDBNull method, the DataTableReader.IsDBNull method, the IDataRecord.IsDBNull method, and several other methods.

So

if (!Convert.IsDBNull(fruitCrateRow.FruitGarden) && 
     !string.IsNullOrEmpty(fruitCrateRow.FruitGarden)

should also be fine.

EDIT

you can still add your string.IsNullOrEmpty test after the DBNull test, as it will be evaluated only if left part is true.

when i try fruitCrateRow.FruitGarden != DBNull.Value I get Error Operator '!=' cannot be applied to operands of type 'string' and 'System.DBNull — Mathematics, Jul 05 '13 at 12:33
@user13814 you're right, corrected and edited (but Claudio Redi had already corrected) — Raphaël Althaus, Jul 05 '13 at 12:42

Claudio Redi · Accepted Answer · 2013-07-05T12:35:25.660

4

DBNull is not the same as null. Try with

if (!DBNull.Value.Equals(fruitCrateRow.FruitGarden) && 
     fruitCrateRow.FruitGarden != "")

edited Jul 05 '13 at 12:35

answered Jul 05 '13 at 12:28

Claudio Redi

67,454
15
130
155

score 0 · Answer 3 · edited May 23 '17 at 11:59

0

Rather than string.isNullOrEmpty you'll need to check:

if (fruitCrateRow.FruitGarden != DbNull.Value)
            {
//do something but i am getting error, how can i fix this issue ?

As it's a Database null.

See: What is the difference between null and System.DBNull.Value?

and: http://msdn.microsoft.com/en-us/library/system.dbnull.aspx

edited May 23 '17 at 11:59

Community

1
1

answered Jul 05 '13 at 12:30

RemarkLima

11,639
7
37
56

DBNull error is coming up

3 Answers3