String.IsNullOrEmpty VS IsDBNull in validation when using IIF and IF ELSE

Question

This is not clear to me, so if anyone can explain in detailed if what is the difference of the two functions (IsDBNull and String.IsNullOrEmpty) of VB.Net.

Below is my scenario why i ask that, i have column Company in my table which the value is NULL, then i used IIF function of vb.net to validate if it is NULL then assign empty string (""), else assign the value coming from data table

Scenario:

1. Below is using String.IsNullOrEmpty and i get a conversion error:

   Conversion from type 'DBNULL' to 'String' is not valid.

   txtCompany.Text = IIf(String.IsNullOrEmpty(dtSample.Rows(grdInfo.SelectedIndex).Item("Company")).ToString, "", dtSample.Rows(grdInfo.SelectedIndex).Item("Company"))
   

2. However when i replace String.IsNullOrEmpty by IsDBNull , the validation works fine.

   txtCompany.Text = IIf(IsDBNull(dtSample.Rows(grdInfo.SelectedIndex).Item("Company")).ToString, "", dtSample.Rows(grdInfo.SelectedIndex).Item("Company"))
   

EDIT:

And it is confusing because if i did the validation using IF ELSE condition (see sample code below) with the use of String.IsNullOrEmpty, it works fine.

    If String.IsNullOrEmpty(dtSample.Rows(grdInfo.SelectedIndex).Item("Company").ToString) = True Then
        txtCompany.Text = ""
    Else
        txtCompany.Text = dtSample.Rows(grdInfo.SelectedIndex).Item("Company").ToString
    End If

The confusing part is when i used IIF(String.IsNullOrEmpty...etc) it returns an error. but when i used the normal IF String.IsNullOrEmpty(dtSample.Rows....etc) = True it works fine.

Any explanation would much appreciated. Thanks

Answer 1

You cannot mix and match String.IsNullOrEmpty and IsDBNull because they work on two different things. The first on strings, the second on Data items read from the database.

But a very important element of this is that your code is invalid. Neither "Scenario" snippet compiles under Option Strict. If you leave VB to guess what you mean, you will get confusing results.

Snippet 1:

txtCompany.Text = IIf(String.IsNullOrEmpty(dtSample.Rows(grdInfo.SelectedIndex).Item("Company")).ToString, "", dtSample.Rows(grdInfo.SelectedIndex).Item("Company"))

Simplified:

Dim foo = IIf(String.IsNullOrEmpty(zDT.Rows(23).Item("Name")).ToString,
                "", zDT.Rows(23).Item("Name"))

This is illegal because zDT.Rows(23).Item("Name") is an object, but String.IsNullOrEmpty expects a string. Your ToString is misplaced - it is not converting the db Item, it is converting the entire IIF bool expresion!

The compiler warns you of both withOption Strict On.

The conversion throws an exception because VB must convert the db Object item ( zDT.Rows(23).Item("Name")) to a string. The way it does it results in an error when when the db data is DBNull.

Snippet 2:

txtCompany.Text = IIf(IsDBNull(dtSample.Rows(grdInfo.SelectedIndex).Item("Company")).ToString, "", dtSample.Rows(grdInfo.SelectedIndex).Item("Company"))

Simplified:

foo = IIf(IsDBNull(zDT.Rows(23).Item("Name")).ToString, 
               "", zDT.Rows(23).Item("Name"))

This is slightly better but a string is till being used in place of the Boolean expression. When fixed, you have:

IsDBNull(zDT.Rows(23).Item("Name"))

IsDBNull is testing a database item (Object) to see if it has data. It will work. IsNullOrEmpty should not be used to test for DBNull and cant with Option Strict. You'd have to convert the dbItem to string first, then it will only work depending on how you convert.

' cant use string method to test an object
String.IsNullOrEmpty(zDT.Rows(23).Item("Name"))

' this will work:
String.IsNullOrEmpty(zDT.Rows(23).Item("Name").ToString)

' this will not:
String.IsNullOrEmpty(CStr(zDT.Rows(23).Item("Name")))

Use DBNull tests for data objects, and IsNullOrEmpty on strings.

Also, if you use the newer If operator in place of the old IIf function you can avoid other issues. The operator allows short circuiting, the syntax is the same:

Dim foo = If(bool expr, True result, False result)