PRB: Error When Assign DB Value to Var: Invalid Use of Null

CAUSE

The variant data type can hold several types of data. It can also be Null or Empty. It is important to distinguish between Null and Empty. A Null variant contains no valid data, while an Empty variant has not been initialized.

When an Empty variant is assigned to a string it is converted to "". When an Empty variant is assigned to a numeric it is converted to 0. The Null variant on the other hand has no valid data, so it cannot be assigned to a string or a numeric. Trying to assign a Null variant generates the "Invalid Use of Null" error. The following example demonstrates this behavior:

   Dim a as Variant
   Dim b as Integer
   a = Null
   b = a
				

Some properties, functions, and methods also return Null. An obvious example is the Null function. To avoid the "Invalid Use of Null" error, don't assign a function or method that returns Null to a string or numeric. The following example demonstrates this behavior:

   Dim b as Integer
   b = Null
				

The "Invalid Use of Null" error can also occur when assigning a value to a string or numeric property of a control. The text property of a text box is a string property. The following example shows how the "Invalid Use of Null" error can occur with a text box.

   Text1.Text = Null
				

In database programming, you may also receive the "Invalid Use of Null" error when assigning the value of a field to a text box. This happens because the Value property returns Null when the field contains no valid data. Here's an example that demonstrates this:

1. Start a new project in Visual Basic. Form1 is created by default.
2. Add a data control (Data1) and a text box (Text1) to the form.
3. Add the following code to the Form_Load event:

      Private Sub Form_Load()
         Data1.DatabaseName = "C:\VB\BIBLIO.MDB"
         Data1.RecordSource = "Authors"
         Data1.Refresh
         Data1.Recordset.AddNew
         Data1.Recordset.Update
         Data1.Recordset.Bookmark = Data1.Recordset.LastModified
         Text1.Text = Data1.Recordset("Author")
      End Sub
   						

4. Run the program.