One of the more common problems in ASP.NET that I've seen, especially with newbie developers, is the improper handling of NULL values being returned from a database query. The first indication that developers usually get is the dreaded error message that says: "DBNULL cannot be converted to String."
The error usually occurs when the DataGrid, DataTable or Repeater controls are being used, as shown in Listing 1 below:
Listing 1: The Code that Generates the Error
<asp:datagrid id="datagrid" ....>
<Columns>
<asp:TemplateColumn ....>
<ItemTemplate>
<%# PrepareText(Container.DataItem("middle_name")) %>
</ItemTemplate>
</asp:TemplateColumn>
</Columns>
</asp:datagrid> |
In this example, the developer is trying to display a column from the database query, but wants to do some manipulation of the value before it's displayed. In the code, the value returned by Container.DataItem("middle_name")) is passed to the PrepareText function, which expects a String.
That's fine, if the column has a value. But if the column is NULL in the database, then DBNULL is what will be passed to the function. Unfortunately, DBNULL cannot be converted to a string, so a failure occurs.
The solution is to pass the database value as an object. This works because the Object class is the ancestor of all classes. Then the function can test whether the object is DBNULL before attempting to convert it to a string. Listing 2 shows the revised code for the function.
Listing 2: The Revised Function
FUNCTION PrepareText(objVal AS Object) AS String
DIM strArg AS String
DIM strNewText AS String
IF NOT IsDBNull(objVal) THEN
strArg = CSTR(objVal)
'--- Do any needed text manipulation
ELSE
strNewText = " "
END IF
RETURN strNewText
END FUNCTION |
|
