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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

You can use the IsDbNull function     If  IsDbNull myItem  sID      False AndAlso myItem  sID    sID Then        Do something End If

User · Answer

You can use the IsDbNull function     If  IsDbNull myItem  sID      False AndAlso myItem  sID    sID Then        Do something End If

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

You can use the IsDbNull function     If  IsDbNull myItem  sID      False AndAlso myItem  sID    sID Then        Do something End If

User · Answer

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

User · Answer

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

User · Answer

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  gt  0 And Convert ToString dt Rows 0  Item 0        Then         MsgBox  datbase is null        ElseIf dt Rows Count  gt  0 And Convert ToString dt Rows 0  Item 0    lt  gt     Then         MsgBox  datbase have value        End If

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

You can use the IsDbNull function     If  IsDbNull myItem  sID      False AndAlso myItem  sID    sID Then        Do something End If

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt  0 And Convert ToString dt Rows 0  Item 0        Then         MsgBox  datbase is null        ElseIf dt Rows Count  gt  0 And Convert ToString dt Rows 0  Item 0    lt  gt     Then         MsgBox  datbase have value        End If

[vb.net] handling dbnull data in vb.net

Examples related to vb.net

Examples related to dbnull