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

 Last reviewed: April 16, 1996
Article ID: Q147651
The information in this article applies to:
  • 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: 4.00 Access vb4win vb416
KBCategory: kbprg kbcode kbprb
KBSubcategory: PrgOther

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 16, 1996
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.