Using ODBCDataReader, why can't I detect DBNull in a field using DBNull.Value.Equals or IsDBNull in vb.net? Deffinitely DBNull

Question

The way I know it's deffintely DBNull is when I use String.IsNullOrEmpty it errors out with 'Cant convert type DBNull to type String'

I'm using an ODBCDataReader with the following ODBCCommand to transfer from a Quickbooks Desktop company file to a SQL database via LINQ; not sure if this is a quirk of ODBC or something as this is the first time I've used it:

SELECT id, string0, string1, string2, string3, int FROM Table1 WHERE (IsActive = 1)

To process the result I'm using:

IIf(IsDBNull(e(3)) Or IsNothing(e(3)), String.Empty, e(3))  

The table I'm transfering to does not allow NULLs.

I've tried using Not and swapping the resulting options around, I've tried using Is Nothing and DBNull.Value.Equals() and of course, IsNullOrEmpty. When I don't have IsNullOrEmpty in the statement I get a 'Cannot insert NULL into Table' I've also tried swapping Or with OrElse. As mentioned below I should be using OrElse anyway.

Now, with another table in the same company file this code works perfectly with DBNulls. I copied the code directly and I've tried every combination of things I can think of. What could possibly be wrong with this instance that would cause it to not recognize the field type?

If you can point me in the right direction, I'd greatly appreciate it.

Answer 1

What has tripped you up is that the Or in VB.NET is base don the one in VB6, which did not short-circuit the logic:

https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/operators/orelse-operator

Try this instead:

IIf(IsDBNull(e(3)) OrElse IsNothing(e(3)), String.Empty, e(3))  

Answer 2

This:

IIf(IsDBNull(e(3)) Or IsNothing(e(3)), String.Empty, e(3))  

is garbage for so many reasons.

Cutting to the chase, DBNull.ToString returns an empty String so, if what you want is a String if one exists or an empty String for NULL then all you have to do is call ToString:

Dim str = myDataReader(columnIndex).ToString()

Ignoring that though, You should NEVER use IIf, IsDBNull, or IsNothing and, as has already been established, you should pretty much ALWAYS use AndAlso and OrElse rather than And and Or. The latter should only be used when you want to specifically avoid short-circuiting and, in that case, your code is probably bad for other reasons.

So, rather than the IIf function, which was garbage from the outset, you should use the If operator, which was introduced in VB 2008. There are various ways to check for DBNull and a data reader provides its own. There are also better ways to check for Nothing but it's pointless here anyway because you can never get Nothing from a database.

Let's say that you were using a numeric column rather than text and you wanted to use zero in place of NULL values. The correct way to write code like you had would be this:

Dim num = If(myDataReader.IsDBNull(columnIndex), 0, myDataReader.GetInt32(columnIndex))

Like I said, the data reader itself can tell you whether a column is NULL. The If operator is effectively generic, which means that the two possible return values must be the same type or one type must be derived from the other. In this example, both are type Integer so we're good. If you did want to throw in a pointless check for Nothing then that would look like this:

Dim num = If(myDataReader.IsDBNull(columnIndex) OrElse myDataReader(columnIndex) Is Nothing,
             0,
             myDataReader.GetInt32(columnIndex))