ADO.NET NULL Values ado.net 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)
    Try
      connection.Open()
      ' 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)
      list.Add(recipient)
      End While
      reader.Close()
      command.Dispose()
      ' ToDo: something with the recipients list
    Catch ex As Exception
      Console.Write(ex.Message)
    Finally
      connection.Close()
    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
    SELECT * FROM Users WHERE Phone IS NOT NULL
  

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 visual-basic-tutorials.com 2017 - All Rights Reserved.