Test for empty cell Access DB VB.net?

Question

I'm wondering on whether its possible to check if a specific cell in a record is empty before attempting to collect its value and crashing the program if it cant be assigned to a variable.

Something along the lines of SELECT FirstNameColumn FROM tableNames WHERE LastNameColumn = "Smith", and to test if the FirstNameColumn has a value in it.

Im using VB.net, with the OleDB and an Access Database.

Answer 1

If you had some code that retrieved a record, and some of the values on the row might be null:

Public Sub ReadMyData(ByVal connectionString As String)
    Dim queryString As String = "SELECT FirstNameColumn FROM tableNames WHERE LastNameColumn = 'Smith'"
    Using connection As New OleDbConnection(connectionString)
        Dim command As New OleDbCommand(queryString, connection)

        connection.Open()

        Dim reader As OleDbDataReader = command.ExecuteReader()
        While reader.Read()


            If Not reader.IsDbNull(0) Then 
               Console.WriteLine(reader.GetString(0))
            End If

        End While

        reader.Close()
    End Using
End Sub

A DataReader has an IsDBNull method hat will tell you whether a cell on a row is null.

Really though, using a DataReader is really low level and quite hard work. There are a lot easier ways to use a database. The next step up might reasonably be a dataadapter and datatable, offering a similar function:

 Dim da as New OleDbDataAdapter("SELECT FirstNameColumn FROM tableNames WHERE LastNameColumn = 'Smith'", connStr)  
 DataTable dt = new DataTable()  
 custDA.Fill(dt)  

 For Each ro as DataRow in dt.Rows
   If Not ro.IsNull("FirstNameColumn") Then
     Console.WriteLine(ro("FirstNameColumn"))
   End If
 Next ro

TableAdapters another level of abstraction, removes all the horrible dealing with string column names amongst other things:

 Dim ta as New PersonTableAdapter
 Dim dt as PersonDataTable = ta.GetData()
 For Each ro in dt
   If Not ro.IsFirstNameColumnNull() Then
     Console.WriteLine(ro.FirstNameColumn)
   End If
 Next ro

Entity Framework is probably equivalently high level, possibly higher:

Dim p as Person = context.Person.Where(Function(x) x.LastName = "Smith")
If p.FirstNameColumn Is Not Nothing Then
  Console.WriteLine(p.FirstNameColumn)
End If