handling dbnull data in vb.net

Question

I want to generate some formatted output of data retrieved from an MS-Access database and stored in a DataTable object/variable, myDataTable. However, some of the fields in myDataTable cotain dbNull data. So, the following VB.net code snippet will give errors if the value of any of the fields lastname, intials, or sID is dbNull.

   dim myDataTable as DataTable
   dim tmpStr as String
   dim sID as Integer = 1

   ...
   myDataTable = myTableAdapter.GetData() ' Reads the data from MS-Access table
   ...

   For Each myItem As DataRow In myDataTable.Rows

    tmpStr = nameItem("lastname") + " " + nameItem("initials")

    If myItem("sID")=sID Then
        ' Do something
    End If

    ' print tmpStr

   Next

So, how do i get the above code to work when the fields may contain dbNull without having to check each time if the data is dbNull as in this question?

can you indicate WHERE (on which line) error occurs ? I suppose on `If MyItem("sID")=sID` but I'm not sure. — schlebe, Nov 22 '22 at 09:44
Are you sure that field name is "sID" ? It is special that a column name begin with "s"; normally prefixing a variable by "s" is to indicate that it is a String variable. — schlebe, Nov 22 '22 at 09:45

score 143 · Accepted Answer · answered Oct 21 '08 at 18:07

143

The only way that i know of is to test for it, you can do a combined if though to make it easy.

If NOT IsDbNull(myItem("sID")) AndAlso myItem("sID") = sId Then
   'Do success
ELSE
   'Failure
End If

I wrote in VB as that is what it looks like you need, even though you mixed languages.

Edit

Cleaned up to use IsDbNull to make it more readable

answered Oct 21 '08 at 18:07

Mitchel Sellers

62,228
14
110
173

1

Thanks for your answer. I guess it looks like mixed languanges because of the way I coded the comments with "//" instead of ticks for VB.net comments? – Azim J Oct 21 '08 at 18:11
1

Yup! And the == in the comparison – Mitchel Sellers Oct 21 '08 at 18:11
I think I fixed the code in the question to be pure VB.Net code. Thanks for your answers. – Azim J Oct 21 '08 at 18:18
1

10 years later. "+" as well. :P – Johnny Prescott May 21 '17 at 01:54
The `DataRow` class has its own `IsNull` method. That code would also not compile with `Option Strict On`. – jmcilhinney Oct 26 '18 at 12:17

Steve Wortham · Answer 2 · 2009-11-12T17:15:24.460

37

I got tired of dealing with this problem so I wrote a NotNull() function to help me out.

Public Shared Function NotNull(Of T)(ByVal Value As T, ByVal DefaultValue As T) As T
        If Value Is Nothing OrElse IsDBNull(Value) Then
                Return DefaultValue
        Else
                Return Value
        End If
End Function

Usage:

If NotNull(myItem("sID"), "") = sID Then
  ' Do something
End If

My NotNull() function has gone through a couple of overhauls over the years. Prior to Generics, I simply specified everything as an Object. But I much prefer the Generic version.

edited Nov 12 '09 at 17:15

answered Nov 12 '09 at 17:10

Steve Wortham

21,740
5
68
90

2

+1 Works very well, I've named it IsNull() as per SQL Server. – Lazlow Jul 05 '10 at 09:06
5

I prefer making the 2nd variable optional so that the signature is like this: `Public Shared Function NotNull(Of T)(ByVal Value As T, Optional ByVal DefaultValue As T = Nothing) As T` and it can be called as `If NotNull(myItem("sID")) = sID Then` or as listed above. – Daniel Jan 21 '14 at 17:36
Doesn't work for me using VS 2008: tried to use this method with dataset generated dataTable and didn't work. `NotNull(rw.computer)` fails because the Dataset.Designer.vb code tries to convert to string before passing as argument. I suppose it would work with generic DataTable. – D_Bester Aug 10 '16 at 18:10
Put this into it when using nullable of integer: Public Function NotNull(ByVal value As Integer?) As Integer Return NotNull(value, 0) End Function – Richard Griffiths Oct 07 '16 at 16:38
I love this method. Concise and effective. +1 – Jessica Jul 02 '19 at 14:56

score 12 · Answer 3 · answered Oct 21 '08 at 18:09

12

You can also use the Convert.ToString() and Convert.ToInteger() methods to convert items with DB null effectivly.

answered Oct 21 '08 at 18:09

Mitchel Sellers

62,228
14
110
173

score 6 · Answer 4 · edited Dec 14 '18 at 14:23

6

A variation on Steve Wortham's code, to be used nominally with nullable types:

Private Shared Function GetNullable(Of T)(dataobj As Object) As T
    If Convert.IsDBNull(dataobj) Then
        Return Nothing
    Else
        Return CType(dataobj, T)
    End If
End Function

e.g.

mynullable = GetNullable(Of Integer?)(myobj)

You can then query mynullable (e.g., mynullable.HasValue)

edited Dec 14 '18 at 14:23

Jimi

29,621
8
43
61

answered Mar 31 '12 at 05:52

Greg May

61
1
1

score 3 · Answer 5 · answered Jun 07 '09 at 13:30

Microsoft came up with DBNull in .NET 1.0 to represent database NULL. However, it's a pain in the behind to use because you can't create a strongly-typed variable to store a genuine value or null. Microsoft sort of solved that problem in .NET 2.0 with nullable types. However, you are still stuck with large chunks of API that use DBNull, and they can't be changed.

Just a suggestion, but what I normally do is this:

All variables containing data read from or written to a database should be able to handle null values. For value types, this means making them Nullable(Of T). For reference types (String and Byte()), this means allowing the value to be Nothing.
Write a set of functions to convert back and forth between "object that may contain DBNull" and "nullable .NET variable". Wrap all calls to DBNull-style APIs in these functions, then pretend that DBNull doesn't exist.

score 2 · Answer 6 · answered Sep 05 '12 at 12:25

If you are using a BLL/DAL setup try the iif when reading into the object in the DAL

While reader.Read()
 colDropdownListNames.Add(New DDLItem( _
 CType(reader("rid"), Integer), _
 CType(reader("Item_Status"), String), _
 CType(reader("Text_Show"), String), _
 CType( IIf(IsDBNull(reader("Text_Use")), "", reader("Text_Use")) , String), _
 CType(reader("Text_SystemOnly"), String), _
 CType(reader("Parent_rid"), Integer)))
End While

score 2 · Answer 7 · answered Oct 21 '08 at 18:08

2

You can use the IsDbNull function:

  If  IsDbNull(myItem("sID")) = False AndAlso myItem("sID")==sID Then
    // Do something
End If

answered Oct 21 '08 at 18:08

brendan

29,308
20
68
109

score 1 · Answer 8 · answered Nov 27 '13 at 15:40

   VB.Net
   ========
    Dim da As New SqlDataAdapter
    Dim dt As New DataTable
    Call conecDB()        'Connection to Database
    da.SelectCommand = New SqlCommand("select max(RefNo) from BaseData", connDB)

    da.Fill(dt)

    If dt.Rows.Count > 0 And Convert.ToString(dt.Rows(0).Item(0)) = "" Then
        MsgBox("datbase is null")

    ElseIf dt.Rows.Count > 0 And Convert.ToString(dt.Rows(0).Item(0)) <> "" Then
        MsgBox("datbase have value")

    End If

Azim J · Answer 9 · 2008-10-21T18:14:05.977

1

For the rows containing strings, I can convert them to strings as in changing

tmpStr = nameItem("lastname") + " " + nameItem("initials")

to

tmpStr = myItem("lastname").toString + " " + myItem("intials").toString

For the comparison in the if statement myItem("sID")=sID, it needs to be change to

myItem("sID").Equals(sID)

Then the code will run without any runtime errors due to vbNull data.

edited Oct 21 '08 at 18:14

answered Oct 21 '08 at 18:08

Azim J

8,260
7
38
61

score 1 · Answer 10 · answered Sep 26 '16 at 20:33

1

I think this should be much easier to use:

select ISNULL(sum(field),0) from tablename

Copied from: http://www.codeproject.com/Questions/736515/How-do-I-avoide-Conversion-from-type-DBNull-to-typ

answered Sep 26 '16 at 20:33

Sabbir Hassan

175
14

score 0 · Answer 11 · answered Aug 11 '13 at 10:10

0

Hello Friends

This is the shortest method to check db Null in DataGrid and convert to string

create the cell validating event and write this code
If Convert.ToString(dgv.CurrentCell.Value) = "" Then
CurrentCell.Value = ""
End If

answered Aug 11 '13 at 10:10

JAY SINGH

17
2

score 0 · Answer 12 · edited Apr 07 '16 at 07:06

This is BY FAR the easiest way to convert DBNull to a string. The trick is that you CANNOT use the TRIM function (which was my initial problem) when referring to the fields from the database:

BEFORE (produced error msg):

Me.txtProvNum.Text = IIf(Convert.IsDBNull(TRIM(myReader("Prov_Num"))), "", TRIM(myReader("Prov_Num")))

AFTER (no more error msg :-) ):

Me.txtProvNum.Text = IIf(Convert.IsDBNull(myReader("Prov_Num")), "", myReader("Prov_Num"))

score 0 · Answer 13 · answered Nov 10 '21 at 17:12

0

Simple, but not obvious.

DbNull.Value.Equals(myValue)

I hate VB.NET

answered Nov 10 '21 at 17:12

B H

1,730
18
24

schlebe · Answer 14 · 2022-11-22T10:58:56.380

For your problem, you can use following special workaround coding that only exists in VB.Net.

Dim nId As Integer = dr("id") + "0"

This code will replace DBNull value contained in id column by integer 0.

The only acceptable default value is "0" because this expression must also be used when dr("id") is not NULL !

So, using this technic, your code would be

   Dim myDataTable as DataTable
   Dim s as String
   Dim sID as Integer = 1

   ...
   myDataTable = myTableAdapter.GetData() ' Reads the data from MS-Access table
   ...

   For Each myItem As DataRow In myDataTable.Rows
       s = nameItem("lastname") + " " + nameItem("initials")
       If myItem("sID") + "0" = sID Then
           ' Do something
       End If
   Next

I have tested this solution and it works on my PC on Visual Studio 2022.

PS: if sID can be equal to 0 and you want to do something distinct when dr("sID") value is NULL, you must also adept you program and perhaps use Extension as proposed at end of this answer.

I have tested following statements

Dim iNo1 As Integer = dr("numero") + "0"
Dim iNo2 As Integer = dr("numero") & "0" '-> iNo = 10 when dr() = 1
Dim iNo3 As Integer = dr("numero") + "4" '-> iNo = 5  when dr() = 1
Dim iNo4 As Integer = dr("numero") & "4" '-> iNo = 14 when dr() = 1
Dim iNo5 As Integer = dr("numero") + "" -> System.InvalidCastException : 'La conversion de la chaîne "" en type 'Integer' n'est pas valide.'
Dim iNo6 As Integer = dr("numero") & "" -> System.InvalidCastException : 'La conversion de la chaîne "" en type 'Integer' n'est pas valide.'
Dim iNo7 As Integer = "" + dr("numero") -> System.InvalidCastException : 'La conversion de la chaîne "" en type 'Integer' n'est pas valide.'
Dim iNo8 As Integer = "" & dr("numero") -> System.InvalidCastException : 'La conversion de la chaîne "" en type 'Integer' n'est pas valide.'
Dim iNo9 As Integer = "0" + dr("numero")
Dim iNo0 As Integer = "0" & dr("numero")

Following statements works also correctly

Dim iNo9 As Integer = "0" + dr("numero")
Dim iNo0 As Integer = "0" & dr("numero")

I recognize that is a little tricky.

If trick are not your tips, you can also define an Extension so that following code works.

Dim iNo = dr.GetInteger("numero",0)

where GetInteger() code can be following

Module Extension
    '***********************************************************************
    '* GetString()
    '***********************************************************************

    <Extension()>
    Public Function GetString(ByRef rd As SqlDataReader, ByRef sName As String, Optional ByVal sDefault As String = "") As String
        Return GetString(rd, rd.GetOrdinal(sName), sDefault)
    End Function

    <Extension()>
    Public Function GetString(ByRef rd As SqlDataReader, ByVal iCol As Integer, Optional ByVal sDefault As String = "") As String
        If rd.IsDBNull(iCol) Then
            Return sDefault
        Else
            Return rd.Item(iCol).ToString()
        End If
    End Function

    '***********************************************************************
    '* GetInteger()
    '***********************************************************************

    <Extension()>
    Public Function GetInteger(ByRef rd As SqlDataReader, ByRef sName As String, Optional ByVal iDefault As Integer = -1) As Integer
        Return GetInteger(rd, rd.GetOrdinal(sName), iDefault)
    End Function

    <Extension()>
    Public Function GetInteger(ByRef rd As SqlDataReader, ByVal iCol As Integer, Optional ByVal iDefault As Integer = -1) As Integer
        If rd.IsDBNull(iCol) Then
            Return iDefault
        Else
            Return rd.Item(iCol)
        End If
    End Function

End Module

These methods are more explicitely and less tricky.

In addition, it is possible to define default values other than ZERO and also specific version as GetBoolean() or GetDate(), etc ...

Another possibility is to report SQL default conversion in SQL command using COALESCE SQL command !

handling dbnull data in vb.net

14 Answers14

Linked

Related