VB.NET: How do I use coalesce with db column values and nullable types? Or is there a better solution?

Question

I'm trying to do something similar to what's described here, but with nullable types.

http://www.csharp-station.com/Tutorials/Lesson23.aspx

int availableUnits = unitsInStock ?? 0;

In VB, it would be this:

Dim availableUnits As Int32 = If(unitsInStock, 0)

However I'm working with db columns, which could be DbNull, and nullable types, which can be Nothing (which is different to DbNull). If a column is DbNull, I want to return Nothing, otherwise return the value. For eg:

Dim availableUnits As Int32? = If(myDataReader("UnitsInStock").Value, Nothing)

The error I'm getting is "Specified cast is not valid" but I'm not sure why. I've tried this as well:

Dim availableUnits As Int32? = If(isDbNull(myDataReader("UnitsInStock").Value), myDataReader("UnitsInStock").Value, Nothing)

Which is messy and just results in the same error. The only thing that works is this:

Dim availableUnits As Int32?
If isDbNull(myDataReader("UnitsInStock").Value) Then
  availableUnits = myDataReader("UnitsInStock").Value
Else
  availableUnits = Nothing
End If

Which is just silly. Is there a better way of getting nullable db values into nullable variables that I'm not aware of?

score 3 · Answer 1 · answered Dec 08 '11 at 19:52

3

If using DataSets instead of DataReaders is an option for you, the DataRow.Field method knows how to handle Nullable variables properly:

Dim availableUnits = myDataRow.Field(Of Int32?)("UnitsInStock")

answered Dec 08 '11 at 19:52

Heinzi

167,459
57
363
519

Thanks, that's definitely handy to know. In this case though, need to work with the datareader. – ingredient_15939 Dec 09 '11 at 02:16

score 1 · Answer 2 · edited May 23 '17 at 10:26

1

As I mention in what turns out to be a very similar question you are best served by not using Nothing here, but rather New Int32?:

Dim availableUnits = If(myDataReader.IsDBNull("UnitsInStock"), _
                              New Int32?, _
                              myDataReader.GetInt32("UnitsInStock"))

edited May 23 '17 at 10:26

Community

1
1

answered Mar 07 '14 at 15:49

Mark Hurd

10,665
10
68
101

score 1 · Accepted Answer · answered Dec 08 '11 at 19:49

1

It is silly, but you have to cast the Nothing when you set it:

Dim availableUnits As Int32? = If(myDataReader.IsDBNull("UnitsInStock"), _
                                  CType(Nothing, Int32?), _
                                  myDataReader.GetInt32("UnitsInStock"))

answered Dec 08 '11 at 19:49

AJ.

16,368
20
95
150

Very true, and very weird, just discovered this after an hour of head scratching. Looks like this is about as simple as it gets. Many thanks. – ingredient_15939 Dec 09 '11 at 04:42
This problem arises because `Nothing` in VB does not necessarily mean `null`. See this question for details: http://stackoverflow.com/q/4147277/87698 – Heinzi Dec 09 '11 at 08:41
@Heinzi - I believe the same is true of C#, requiring the cast, i.e., `(Int32?)null`. – AJ. Dec 09 '11 at 17:47

VB.NET: How do I use coalesce with db column values and nullable types? Or is there a better solution?

3 Answers3

Linked