ADO.NET NULL Values
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:
' 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.