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:

<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.

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

Comments

No comments yet. Be the first.