How to check if it's null?

Question

I retrieve data from database like below. How do I check whether the value retrieved from database is null?

Private Function GetBatch() As DataSet
        Dim dataset As New DataSet
        Dim adapter As Data.SqlClient.SqlDataAdapter
        Dim cn As New System.Data.SqlClient.SqlConnection(connectionstring())
        GetBatchCommand.Connection = cn
        adapter = New Data.SqlClient.SqlDataAdapter(GetBatchCommand)
        adapter.Fill(dataset)
        Return dataset
End Function

Dim dataset As New DataSet
            dataset = GetBatch()

With dataset.Tables(0)

Dim PersonID As String = .Rows(int).Item("personId")

I'd like to check whether personID is null. How do do that?

Answer 1

If DBNull.Value.Equal(.Rows(int).Item("personId")) Then
...

DBNull

Answer 2

You can also use the Convert.DbNull constant.

Answer 3

Try DataRow's IsNull method to check null values :

Dim isPersonIDNull As Boolean = .Rows(0).IsNull("personId")

Or use IsDBNull method :

Dim isPersonIDNull As Boolean = IsDBNull(.Rows(int).Item("personId"))

Or manually Check if the value equals DBNull :

Dim isPersonIDNull As Boolean = .Rows(int).Item("personId").Equals(DBNull.Value)

Answer 4

You have to check if the value is null before you assign it to PersonID

like:

if .Rows(int).Item("personId") = DBNull.Value Then
  ' Assign some Dummy Value
  PersonID = ""
else
  PersonID = .Rows(int).Item("personId") 
end if

I would recommend to extract that piece of code into a helper method which gets either the value or a default for a given column.

Answer 5

This situation can occur if table with no rows, that time ds.Table(0).Rows(int).Item("personId") will return null reference exception

so you have to use two conditions

Dim PersonID As String =""
if(ds.tables.count>0) Then
 if(ds.tables(0).Rows.Count>0) Then
   if(NOT DBNull.Value.Equal((ds.tables(0).Rows(int).Item("PersonID"))) Then
              PersonID = ds.tables(0).Rows(int).Item("PersonID")

I think It will solve your issue..just minor syntax variation may be present