ADO.NET NULL Values tutorials

Tags: VB.NET, VB 2008, VB 2010, VB 2012, VB 2013

This lesson describes how to work with NULL values

Probably there is nothing more frustrating for SQL developers, than dealing with NULL values. Generally speaking, NULL means that a value has not been entered for a particular column in a row. It means that neglecting to take possible NULL values into consideration can often cause incomplete results.

The following example should give you an idea about the challenges of working with NULL values.

    Dim ConnectionString As String = _
    "Data Source=SqlServerNameOrIP;Initial Catalog=DBName;Integrated Security=True"
    Dim connection As New SqlConnection(ConnectionString)
      ' declare and create a sql command
      Dim command As SqlCommand = connection.CreateCommand

      ' set the sql statements
      command.CommandText = "SELECT UserName, Email FROM Users"

      ' declare and build the Sql Reader
      Dim reader As SqlDataReader = command.ExecuteReader

      Dim list As New List(Of Recipient)
      While reader.Read
      Dim recipient As New Recipient
      recipient.UserName = reader.GetString(0)
      ' the following line will throw an exception
      ' if the column email is NULL
      recipient.Email = reader.GetString(1)
      End While
      ' ToDo: something with the recipients list
    Catch ex As Exception
    End Try

Please notice that NULL is not a value and that it cannot equal itself or any other value. However SQL provides specific expressions and functions to test for NULL values.

To test for a NULL value you will want to use the IS [NOT] NULL expression.

    ' select all users without middle name provided

In addition, using the function ISNULL you can change a NULL value to a value like an empty string ('') or zero (0). The syntax of the ISNULL function is:

ISNULL([value], [replacement])

    ' select all users with or without middle name provided
    SELECT FirstName + ' ' + ISNULL(MiddleName,'') + ' ' + LastName AS FullName FROM Users

Another function that also replaces NULL values with another value is a COALESCE which meets ANSI standards and is more versatile than ISNULL.

The syntax of the COALESCE function is:

COALESCE([value1], [value2], ....,[valueN])

    ' select all users with or without middle name provided
    SELECT FirstName + COALESCE(' ' + MiddleName,'') + ' ' + LastName AS FullName FROM Users

In this specific case if the MiddleName value is NULL, COALESCE function will eliminate the space so no extra spaces show up in your results as when you use the ISNULL function.

Share This

Home | About | Contact | Privacy Policy

Copyright 2017 - All Rights Reserved.