Have you ever been “greeted” with a message like this?

Server Error in ‘/’ Application.

Operator is not valid for type 'DBNull' and string “”

This is what happens when you work on an ASP.NET application and you try to do some string operation on a field that you expect it to be a string while in fact it is a DBNull. Since DBNull does not support the String methods in .NET the application crashes. I’ve had this sort of problem also when working with WinForms applications but I do not remember what were the error messages in that case. (I am pretty sure that there is a good reason for the distinction between DBNull and Nothing (null), but from my ‘too lazy too dig deeper into it’ perspective it seems like it is not necessary.)

The first thing one can do to prevent problems like this one is to write the queries that retrieve the data in such a way that they do not return null values. For example

SELECT ISNULL(MiddleName, '') as MiddleName
FROM Person

With this simplified query for each row where the MiddleName is null, the database will give us an empty string instead. Quite simple but unfortunately sometimes we do not have access to the query and we are stuck with a result set that contains null values. In that case one way to deal with it is to always check for a DBNull (for the fields that you know that can contain null values) before you want to perform some operation with the database value. For example

If Not IsDBNull(table.Row(1).Item("MiddleName")) Then
    txtMiddleName.Text = table.Row(1).Item("MiddleName")
End If

However as you see in the example that is a lot of typing and sometimes you just find out too late that the query might also return null values.

Another alternative was suggested by Mads Kristensen in his post named Remove nulls from a DataTable. He suggests a method that itterates through all the fields in the DataTable and whenever there is a field with a number data type (int, float, double) that has DBNull value it is replaced with a 0. I have to admit that his post was inspiration for me to do the same thing but in a slightly more efficient way (I think). His code is in C#, mine in Visual Basic so it is not completely duplicated :)

''' <summary>
''' Scan all the text fields in the DataTable and convert the 
''' DBNull fields into empty strings
''' </summary>
Public Shared Function CleanDataTable(ByVal dt As DataTable) As DataTable
    For Each column As DataColumn In dt.Columns
        If column.DataType Is System.Type.GetType("System.String") Then
            Dim nullRows As DataRow() = dt.Select(column.ColumnName & " IS NULL")
            For Each row As DataRow In nullRows
                row(column) = String.Empty
            Next
        End If
    Next
    Return dt
End Function

At first I was thinking about using a routine (Sub) with the datatable passed as a referenced parameter but I opted for this way since the code can be easily extended to be used as an Extension function.