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

TL;DR

• String.IsNullOrEmpty() checks only for Empty ([blank] i.e. '' or "") or Null values it does not check DBNull, if any field coming from database has its value DBNull it will raise an error.
• IsDBNull() checks for DBNull (it is not same as Null)
• .ToString will convert DBNull to Empty String i.e. ''

Details
Consider following SQL table example (using SQL Server as base)

Table Structure:

Column_Name        Type and Size   Other Properties
----------------   -------------   ----------------------
Company_ID         int             IDENTITY(1,1) NOT NULL
Company_Name       nvarchar (50)                 NOT NULL   
Company_Address    nvarchar (50)                     NULL

INSERT statements:

INSERT [tbl_company] ([Company_Name], [Company_Address]) VALUES ('ABC', 'QWERT')
INSERT [tbl_company] ([Company_Name], [Company_Address]) VALUES ('ASD', ' ')
INSERT [tbl_company] ([Company_Name], [Company_Address]) VALUES ('XYZ', '')
INSERT [tbl_company] ([Company_Name])                    VALUES ('PQR')

Table Data:

Company_ID    Company_Name      Company_Address
-----------   ----------------  ---------------
1             ABC               QWERT
2             ASD               [SPACE]
3             XYZ               [BLANK]
4             PQR               NULL

Testing Company_Address with IsNullOrEmpty() and IsDBNull() using a SqlDataReader (r):

Company_ID IsNullOrEmpty(r("Company_Address")) IsDBNull(r("Company_Address"))
---------- ----------------------------------- ------------------------------
1          False                               False
2          False                               False
3          True                                False
4          ERROR                               True

And now specific to the Question
What the OP is trying here, lets consider all statements one by one

The IIF statement with IsNullOrEmpty (wrong)

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

In this statement OP is accessing the value as dtSample.Rows(grdInfo.SelectedIndex).Item("Company") and checking it with IsNullOrEmpty() and then converting the result of IsNullOrEmpty to string using .ToString i.e. IsNullOrEmpty(value).ToString(). If the value is DBNull it will always return an error. The correct way to use it is

IsNullOrEmpty(dtSample.Rows(grdInfo.SelectedIndex).Item("Company").ToString)

See last part Company")).ToString vs Company").ToString), just a case of MISPLACED ")"

Second (IIF with IsDBNull) and third (IF with IsNullOrEmpty) statements of OP are correct

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

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

As regards to the second statement OP is correctly ordering the parameters i.e. first Company field is converted to string using dtSample.Rows(grdInfo.SelectedIndex).Item("Company").ToString. This converts any DBNull to Empty string and then it checked for IsNullOrEmpty. Now as the value has already been converted to EmptyString it will not give any error.

Old discussions with OP

The difference is clear in your text. Your first statement is:

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

and the second one is

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

Now break them apart, first statement (IIF)

String.IsNullOrEmpty(dtSample.Rows(grdInfo.SelectedIndex).Item("Company")).ToString
'Item("Company")).ToString

And second part (IF)

String.IsNullOrEmpty(dtSample.Rows(grdInfo.SelectedIndex).Item("Company").ToString)
'Item("Company").ToString)

Found any difference?
In first statement you are converting the result of IsNullOrEmpty to String
In second one you are converting .Item("Company") ToString and then comparing it.

If .Item("Company") returns DBNull
then IsNullOrEmpty failed because .Item("Company") returned type DBNull whereas IsNullOrEmpty checks for null
IsDBNull worked because it checks for DBNull

All point of a misplaced bracket ")" It's a typo

And regarding your usage of these statements:
If and IIF need to check the results as booleans and not as strings
It is better recommended to remove the ToString portion of your statements

Answer 2

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)

Answer 3

The IsNullOrEmpty function checks whether or not a string is empty or null. DBNull is not null (Nothing), but rather is a class that indicates a value from a database does not exist. IsDbNull checks for whether or not a value equals DBNull.

You may not be getting the error you mention in the question on the line of code referenced as this runs just fine for me:

        strTest = IIf(String.IsNullOrEmpty(DBNull.Value.ToString), "", DBNull.Value)

Answer 4

IsDBNull Function :

Returns a Boolean value that indicates whether an expression evaluates to the System.DBNull class.

IsDBNull returns True if the data type of Expression evaluates to the DBNull type; otherwise, IsDBNull returns False. The System.DBNull value indicates that the Object represents missing or nonexistent data. DBNull is not the same as Nothing, which indicates that a variable has not yet been initialized. DBNull is also not the same as a zero-length string (""), which is sometimes referred to as a null string.

example :

 Dim testVar As Object 
   Dim nullCheck As Boolean
      nullCheck = IsDBNull(testVar)
     testVar = ""
     nullCheck = IsDBNull(testVar)
     testVar = System.DBNull.Value
    nullCheck = IsDBNull(testVar)
     '  The first two calls to IsDBNull return False; the third returns True..

String.IsNullOrEmpty :

Indicates whether the specified string is null or an Empty string. IsNullOrEmpty is a convenience method that enables you to simultaneously test whether a String is null or its value is Empty.

Example :

    Class Sample
   Public Shared Sub Main()
  Dim s1 As String = "abcd"
  Dim s2 As String = ""
  Dim s3 As String = Nothing

  Console.WriteLine("String s1 {0}.", Test(s1))
  Console.WriteLine("String s2 {0}.", Test(s2))
  Console.WriteLine("String s3 {0}.", Test(s3))
   End Sub

   Public Shared Function Test(s As String) As String
     If String.IsNullOrEmpty(s) Then
     Return "is null or empty"
    Else
     Return String.Format("(""{0}"") is neither null nor empty", s)
    End If
   End Function 
 End Class  
     'The example displays the following output:
    'String s1 ("abcd") is neither null nor empty.
     'String s2 is null or empty.
      'String s3 is null or empty.