Simple way to convert dbNull to a string in VB.NET

Question

I'm looking for a simpler way to check if a value is dbNull and to convert it to an empty string if so.

An example of a situation where I need this would be:

Dim dt As New DataTable
Dim conn As New OleDbConnection(someConnStr)
Dim adap As New OleDbDataAdapter(qryCSSInfo, cssConn)
adap.Fill(dt)


Dim someStr As String = "The first column of the first row returned: " & dt.rows(0).item(0)
Msgbox(someStr)

The problem is that if dt.rows(0).item(0) is null in the database it will be returned as a dbNull value, which can apparently not be appended to a string.

My solution to this problem has been using if statements to replace the value with blank strings:

Dim dt As New DataTable
Dim conn As New OleDbConnection(someConnStr)
Dim adap As New OleDbDataAdapter(qryCSSInfo, cssConn)
adap.Fill(dt)


If Not isDBNull(dt.rows(0).item(0)) then
     Dim someStr As String = "The first column of the first row returned: " & dt.rows(0).item(0)
Else
     Dim someStr As String = "The first column of the first row returned: " & ""
End If
Msgbox(someStr)

This works fine for my purposes, but it gets overwhelming if I have to make this check for every column I need to use in the table. Say I had 10 columns from the table that I wanted to display with this string. I'd have to make this check on each one to ensure they weren't null. Is there an easier or simpler way of doing so?

Answer 1

For string types you can directly use it this way dt.rows(0).item(0).ToString(), without the If condition

adap.Fill(dt)

Dim someStr As String = "The first column of the first row returned: " & dt.rows(0).item(0).ToString()

MsgBox(somestr)

i.e. you can completely omit the if statement. As per MSDN any DBNull value will be converted to EmptyString with .ToString()

Also check this SO post Conversion from type 'DBNull' to type 'String'

However, for non-string database column types such as integers, doubles you must apply checks using IsDBNull to avoid any exceptions.