How to determin if dataset calculated column is dbnull

[moondaddy]

I have a dataset cached on the server and there are times I try to read from
it before much data has been added to it. when its initially created, I add
the user name and some base information but most of the columns are empty.
When I come back to it and try to read its data most columns are OK even
though I haven't entered any data yet. Their values will be "" or 0.
However I have some calculated columns based on expressions (which work OK
when the columns they use to compute have data), and when I try to read from
these columns before transaction data has been entered, I get an error msg:

Cast from type 'DBNull' to type 'Single' is not valid

I can understand why this is happening, but I need to evaluate if this
column is dbnull so I will know if I should try to get its value or not.
There's no property like IsDbNull I can use. Here's some suto code of what
I want to do:

if dr.myCol.IsDbNull then

or
if dr.myCol.DbNull.value = null then 'or something....

Thanks.

 

[William Ryan eMVP]

If you want the value to be 0, then add a ISNULL(yourcurrentexpression, 0)
to your expression, then anything that calculates as null will be 0 instead.

 

[Kevin Yu [MSFT]]

Thanks for Bill's quick response.

Hi moondaddy,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to check if the value of a
field is DBNull. If there is any misunderstanding, please feel free to let
me know.

Bill has provided us with a good solution. Besides, you can also try the
following:

If dr("myCol") = DBNull.Value Then ...

HTH. If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[moondaddy]

Thanks for all of your answers. regarding the example below, I got a blue
squigly under the line saying that it could not use the "=" operator with
strings and dbnull so I tried the following line and it compiled but still
got an exception which I'll post below
(note this line of code is diferent from my original post which used an
expression column integer data type, but i'm having similar issues with it
today where I'm having trouble handling dbnull)

If dr.ShippingDiscountReason = DBNull.Value.ToString Then

and here's the exception:

InnerException {System.InvalidCastException} System.Exception
Message "Cannot get value because it is DBNull." String

I get the same error with Kurt's sample:

If (Convert.IsDBNull(dr.ShippingDiscountReason)) Then...

 

[Kevin Yu [MSFT]]

Hi moondaddy,

I'm so sorry that I made a mistake in my last post. We have to use 'Is'
operator when checking for DBNull objects. Please use the following if
statement instead.

If dr.ShippingDiscountReason Is DBNull.Value Then

For more information about 'Is' operator, please check the following link:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/
vaopris.asp

Apologize again for the inconvenience. If anything is unclear, please feel
free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[moondaddy]

I already tried that one and I get an invalid cast exception when that line
executes:
Message "Cast from type 'DBNull' to type 'String' is not valid." String

Furthermore, if the datatype is numeric I get a blue line under dr.TaxRate
and it's tooltip is:
'Is' requires operands that have reference types, but this operand has the
value type 'Single'.

Note: it says 'Single' even though its a float in the dataset.

how can I test for dbnull without getting an exception?

 

[Tor Arne Gjelhus]

Hi
I havent seen the complete thread on this one so i might be way off.
But if you have a strongly typed DataSet your and your row has a column
named "UserName" the row object will have a
IsUserNameNull() method. Or generally speaking IsColumnNameNull() where you
substitute ColumnName with any column in that datatable

For a "regular" dataset you have the following
ds.Tables.Rows[j].IsNull["columname"] (there are some more overloads for
the isnull method, check the documentation)

Tor Arne Gjelhus

 

[Kevin Yu [MSFT]]

Hi moondaddy,

I think we have to use dr("ColumnName"), because it returns the object
which hasn't been casted to the data type of the column. Would you also try
the following? Assume that the column name is ShippingDiscountReason.

If dr("ShippingDiscountReason") Is DBNull.Value Then

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Kevin Yu [MSFT]]

Hi moondaddy,

Please also try If dr.IsNull("ShippingDiscountReason") Then

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[moondaddy]

Thanks. Here's the 2 you recomded which worked for me on another field (I'm
using this one for the test since its what I'm working on at the moment):

If dr("TotalPieces") Is DBNull.Value Then
and
If dr.IsNull("TotalPieces") Then

I'll asume its going to work for the original field I was having trouble
with.

 

[Kevin Yu [MSFT]]

Hi moondaddy,

It was nice to know that you have it worked. Thanks for sharing your
experience with all the people here. If you have any questions, please feel
free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."