Much ADO About Nothing

One item on most developers' wish list is a tried-and-true way to determine the best ways to test for and set NULL values in their applications—regardless of the language the applications are written in. For example, when they need to signify that DateShipped or DateDied dates haven't been reached and aren't known, most relational database developers have to define these and similar data columns to permit NULL values. Note that a NULL value in a data column isn't the same as an empty or NULL string or any other placeholder that can serve to show that the value isn't known. The language you're using can treat an empty string or a placeholder value (such as Unknown) as a string or valid value and can store a NULL as a "known" value in the database. The concept of NULL has been a part of relational databases since their inception, and NULLs have always garnered special treatment. I won't get into a long-winded discussion about the wisdom of using NULLs, but I do think NULL is a better placeholder than BHOM (Beats the Hell Out of Me), which I used when I taught programming for the US Army.

In classic ADO, developers have to deal with NULL values because they can't assign a NULL to a numeric variable, a string, the Value property of a TextBox control, or any control that doesn't accept a variant data type (which you can set to NULL). If you forget to deal with NULL values and try to assign a NULL to a TextBox control, Visual Basic (VB) 6.0 issues an Invalid Use of NULL (94) error message. But ADO.NET and Visual Basic .NET have better support for NULL values. When you're working with the Common Language Runtime (CLR) and the .NET languages, ADO.NET lets you store, set, and test for NULL values in a variety of ways—far more than with classic ADO. This article uses Visual Basic .NET to showcase ADO.NET and Visual Basic .NET's NULL support.

Testing for NULL Values in VB 6.0

In VB 6.0, if you expect that a field might contain a NULL value that will result in an Invalid Use of NULL error, you can short-circuit the problem by concatenating an empty string to the data Field.Value from the Recordset to the display TextBox.Text property as follows:

txtDateSold.Text =  rsMyData("DateSold") & ""

This approach converts NULL values to an empty string, which the user doesn't see. If the TextBox is a bound control, this empty string (which represents a NULL value) is written to the database as an empty string unless you add code to convert the value back to a NULL—a must if the database column data type isn't a varchar or char. Concatenating an empty string also works in Visual Basic .NET, but the user still can't tell the difference between an empty TextBox and one that reflects a NULL value in the database—and you still have to add code to convert the empty string and post a NULL value to the database.

The easiest way to test for a NULL value in VB 6.0 is by testing for the Null keyword, as the following code shows:

If Not rs(0) = Null Then   Text1.Text = rs(0)Else   Text1.Text = ""End If

You can also test for NULL values in VB 6.0 by using the IsNull() function, as the following code shows:

If IsNull(rs("DateShipped")) Then  txtDateShipped.Text =    ""Else  txtDateShipped.Text =    rs("DateShipped")End If

However, this approach takes a little longer to code and execute because it uses late binding. VB 6.0's IsNull() function corresponds to Visual Basic .NET's IsDBNull() function, which I show in a moment.

Setting NULL Values in VB 6.0

Setting an updateable Recordset Field object's Value property to NULL is painless: You can use either the DBNull or Null keywords, as the following three code examples show:

rs("Author") = DBNull   ' The third (2) Field object    ' is "Author". rs(2).Value = Nullrs.Fields(enuFields.Author).Value = DBNull

For this method to work, the Field object must be updateable and support NULL values. All three of the above lines of code produce the same result.

Testing for NULL Values in Visual Basic .NET

Visual Basic .NET (and the .NET Framework languages in general) support many techniques for handling NULL values, as the following code examples show. First, open a connection and run a query to return several columns in a rowset; in this case, the first column is always NULL. To simplify the code, I use the dv variable to hold the returned column value:

Dim dv As Object dv = drReader.GetValue(0)    ' Capture the first value from    ' the data stream.txtDateShipped.Text = "" &  dv.ToString

You can use any of the following techniques to test for NULL values in a specific item in the ADO.NET SqlDataReader object's Items collection, as I show later—to learn how to use the IsNULL() function to avoid NULLs, see the sidebar "Using T-SQL to Avoid Unwanted NULLs." Note that because I declare dv as an object, dv can contain a NULL value. If I don't declare dv as an object, ADO.NET will produce the exception message System
.InvalidCastException: Specified cast is not valid—but not until the query returns a NULL.

The first technique I use to test for NULL uses the Visual Basic .NET TypeOf function to examine the object data type:

If TypeOf dv Is DBNull Then   txtDateShipped.Text =     ""Else   txtDateShipped.Text =     drReader(0).ToStringEnd If

The next technique compares the data value in dv by using the expression Is DBNull.Value, which returns True if the object referenced is NULL:

If dv Is DBNull.Value Then   txtDateShipped.Text =     ""Else   txtDateShipped.Text =     drReader(0).ToStringEnd If

You can also use the IsDBNull expression to test for NULL against a specific SqlDataReader item; IsDBNull returns True if the column is set to NULL:

If IsDBNull(drReader(0)) Then   txtDateShipped.Text =     ""Else   txtDateShipped.Text =     drReader(0).ToStringEnd If

And the final technique, which Listing 1 shows, uses the IsDBNull method of the specified SqlDataReader or Rows object, which returns True if the specified column contains a NULL.

Passing a NULL Value Back to the Server

In some cases, you might have to pass a NULL back to the server either as the contents of an updateable DataTable's DataRow or as a parameter in a query invocation. Visual Basic .NET and other languages make this operation easy—but different from what you might be used to. The code in Listing 2 executes the AcceptNullParms sample stored procedure. The stored procedure accepts two input parameters. The first parameter defaults to NULL, so if no parameter is passed, the default value of NULL is applied. The second parameter is required but can be set to NULL in your ADO.NET code (as Listing 2 shows) when you set up the Parameters collection.

The code to execute this stored procedure in Visual Basic .NET isn't complex. Notice that when I set up the Parameters collection, I don't specify a value for the first parameter, so ADO.NET passes default as the first parameter, as the SQL Server Profiler trace below shows. When I ran Profiler on the ADO.NET code that used the DataAdapter Fill method to execute the query, Profiler reported that the following query resulted:

exec AcceptNullParms @DateShipped  = default, @QtyOrdered = NULL

I set the second Parameter.Value property to DBNull.Value to pass NULL as the data value, as the code in Listing 3 shows.

When you need to set a data column value to NULL in a DataRow (and the column permits you to set the value to NULL), simply set the object to DBNull.Value:

ds.Tables(0).Rows(0)("ISBN") = DBNull.Value

As more developers wade into ADO.NET, the number of questions about nothing—that is, about handling NULLs—is increasing proportionately. Fortunately, ADO.NET and the .NET languages provide far better support for nothing (er, NULL) than ADO and VB 6.0 do.