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 = "<Unknown>"
End If
Prev. page
[1]
2
3
next page 
