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 =
"<Unknown>"
Else
txtDateShipped.Text =
drReader(0).ToString
End 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 =
"<Unknown>"
Else
txtDateShipped.Text =
drReader(0).ToString
End 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 =
"<Unknown>"
Else
txtDateShipped.Text =
drReader(0).ToString
End 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.
End of Article
Prev. page
1
2
[3]
next page -->
