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

ID: Q147651

The information in this article applies to:

Microsoft Visual Basic for Windows Learning, Professional, and Enterprise Editions, versions 5.0, 6.0
Professional and Enterprise Editions of Microsoft Visual Basic, 16-bit only, for Windows, version 4.0

SYMPTOMS

It is possible to receive the error message "Invalid Use of Null" when assigning data from an empty field in a database to a variable or control. This article explains why this error occurs and how to work around it.

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.

WORKAROUND

Visual Basic provides two mechanisms for working around the error.

The IsNull() function method.
The ampersand (&) concatenation method.

The IsNull() Function Method

The IsNull() function allows you to detect Null. Here's how you could use IsNull() in a database program:

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
      If IsNull(Data1.RecordSet("Author")) Then
         Text1.Text=""
      Else
         Text1.Text = Data1.Recordset("Author")
      End If
   End Sub

4. Run the program.

The Ampersand (&) Concatenation Method

The other method is to take advantage of Visual Basic's string concatenation operator--the ampersand (&). If one of the arguments in a concatenation is valid and the other is Null, a concatenation will convert the null value to "". You can take advantage of this behavior when assigning values that might return Null. When concatenating a valid string with a value that could return null, the result will always be a valid string. Here's an example that uses string concatenation:

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.

Additional reference words: kbVBp400 kbVBp500 kbVBp600 kbdse kbDSupport kbVBp Platform : WINDOWS

Last Reviewed: August 7, 1998