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
– Pat Lillis, Commented Jul 5, 2013 at 12:28

Raphaël Althaus · Accepted Answer · 2013-07-05 12:57:55Z

6

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.

edited Jul 5, 2013 at 12:57

answered Jul 5, 2013 at 12:28

Raphaël Althaus

60.6k6 gold badges106 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mathematics Over a year ago

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

Raphaël Althaus Over a year ago

@user13814 you're right, corrected and edited (but Claudio Redi had already corrected)

Claudio Redi · Accepted Answer · 2013-07-05 12:35:25Z

4

DBNull is not the same as null. Try with

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

edited Jul 5, 2013 at 12:35

answered Jul 5, 2013 at 12:28

Claudio Redi

68.6k15 gold badges138 silver badges161 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 11:59:09Z

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, 2017 at 11:59

CommunityBot

11 silver badge

answered Jul 5, 2013 at 12:30

RemarkLima

12.1k8 gold badges41 silver badges57 bronze badges

Collectives™ on Stack Overflow

DBNull error is coming up

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related