Dbnull data processing in vb.net

Question

I want to generate some formatted output of data received from an MS-Access database and stored in a DataTable object / variable myDataTable. However, some of the fields in myDataTable cotain dbNull data. Thus, the following VB.net code fragment will throw errors if the value of any of the lastname, intials, or sID fields 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 can I make the above code work when the fields can contain dbNull without having to check every time if the data is dbNull, as in this question ?

Answer 1

The only way I know is to test it, you can do a combination, if you want, to make it easier.

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

I wrote in VB, since it seems like what you need, even if you mix languages.

Edit

Cleared to use IsDbNull to make it more readable

Answer 2

I'm tired of dealing with this problem, so I wrote the NotNull () function to help me.

 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 

Using:

 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 just pointed everything as an object. But I prefer the universal version.

Answer 3

You can also use the Convert.ToString () and Convert.ToInteger () methods to convert elements with a null DB value.

Answer 4

A variant of the Steve Worthem 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 

eg

 mynullable = GetNullable(Of Integer?)(myobj) 

Then you can request mynullable (e.g. mynullable.HasValue )

Answer 5

Microsoft came up with DBNull in .NET 1.0 to represent the NULL database. However, it is a pain to use, because you cannot create a strongly typed variable to store a true value or null. Microsoft solved this problem in .NET 2.0 with null types. However, you are still stuck in large chunks of APIs that use DBNull and cannot be changed.

Just a suggestion, but I usually do this:

• All variables containing data read or written to the database should be able to process null values. For value types, this means that they become Nullable (Of T). For reference types (String and Byte ()), this means that the value will be Nothing.
• Write a set of functions to convert between an "object that may contain DBNull" and a "NULL.NET variable". Wrap all DBNull API calls in these functions, then pretend that DBNull does not exist.

Answer 6

If you use the BLL / DAL setting, try iif when reading into an object in 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 

Answer 7

For strings containing strings, I can convert them to strings, as if changing

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

to

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

For comparison, the expression if myItem ("sID") = sID needs to be changed to

 myItem("sID").Equals(sID) 

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

Answer 8

You can use the IsDbNull function:

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

Answer 9

  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 

Answer 10

Hello friends

This is the shortest method for checking db Null in a DataGrid and converting to a string

• create a cell validation event and write this code
• If Convert.ToString (dgv.CurrentCell.Value) = "" Then
• CurrentCell.Value = ""
• End if

Answer 11

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

BEFORE (msg error caused):

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

AFTER (no more error messages :)):

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

Answer 12

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