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?

With ole it gets pretty cumbersome. ADO.NET tends to generate isColNameNull() functions for you so using it is a little better, but the idea is the same. You have to check for a dbnull before trying to use the data. — Brian Hibbert, Oct 28 '15 at 20:37

score 15 · Accepted Answer · edited May 23 '17 at 11:44

15

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.

edited May 23 '17 at 11:44

Community

1
1

answered Oct 28 '15 at 20:51

haraman

2,744
2
27
50

1

.ToString() is the easiest .Net way to handle this for String database types. – NoAlias Oct 28 '15 at 22:05
Thank you so much. I’ve had to go work on a old project and this was killing me. I hated van until just this moment – dgo Sep 29 '19 at 00:38

score 3 · Answer 2 · answered Oct 28 '15 at 20:31

3

You can leverage the If Operator to reduce a few lines of code:

Dim someStr As String = "The first column of the first row returned: " & _
                        If(dt.rows(0).item(0) Is DbNull.Value, String.Empty, dt.rows(0).item(0))

answered Oct 28 '15 at 20:31

NoAlias

9,218
2
27
46

rheitzman · Answer 3 · 2015-10-29T14:43:17.670

You should be able to concatenate a null field with a string - it should convert to an empty string. That said row.IsNull(index) is a good test to use.

    SQL = "Select top 10 Region, CompanyName FROM Suppliers"
    Dim dt As DataTable = Gen.GetDataTable(SQL, scon)
    For Each row As DataRow In dt.Rows
        MsgBox(row("companyName") & " region: " & row("Region")) ' null allowed
        If row.IsNull("region") Then ' .Net test for Null
            MsgBox(row("companyName") & " region is null")
        Else
            'continue
        End If
    Next

You can also resolve this in the query - covert nulls to useful (or empty) strings. The example query is from SQL Server, I don't know if your DB supports COALESCE.

    MsgBox("COALESCE") ' SQL Server - may not be the same in ODBC databases
    SQL = "Select top 10 COALESCE(Region,'na') Region, CompanyName FROM Suppliers"
    dt = Gen.GetDataTable(SQL, scon)
    For Each row As DataRow In dt.Rows
        MsgBox(row("companyName") & " region: " & row("Region"))
    Next

Some coding notes:

    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 ' in OP
        '...
    End If

    ' save some typing if you know there will be only one record
    ' will throw exception is no rows are returned, check for expected count
    Dim row As DataRow = dt.Rows(0)
    If Not IsDBNull(row(0)) Then
        '...
    End If
    ' or 
    If Not row.IsNull(0) Then
        '...
    End If

    ' note the fields can be accessed by name so you can avoid hard coding field position
    If Not row.IsNull("FieldName") Then
        '...
    End If

Note that nulls in the SQL query work the opposite way - a concatenation of a string and null in SQL returns null. — rheitzman, Oct 28 '15 at 20:53
Putting this validation in the SQL query is a good point. Since the DB engine wasn't specified, recommending the more universal Coalesce above IsNull was smart too. — NoAlias, Oct 28 '15 at 21:57

score 0 · Answer 4 · answered Oct 28 '15 at 21:51

The simplest way to do it is just add a "" after the field or string. Eg.:

  dim EmptyString as string = Nullfield() & ""
  if EmptyString = ""
     ' in the sample, it should.
  end if

So, in your code you can use:

 If dt.rows(0).item(0) & "" = "" then
      ' it should be...
 end if

score 0 · Answer 5 · answered Jun 11 '21 at 12:46

0

I got some null data into cells of a datagrid; to correctly retrieve that data I concatenate the "" string to the cell value:

Dim readVal As String = "" & row.Cells(2).Value

answered Jun 11 '21 at 12:46

Zac

4,510
3
36
44

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

5 Answers5