Knowledge Base |

# VB3 PRB: Error If Assign DB Value to Var: Invalid Use of Null

Article ID: 113032

Article Last Modified on 10/29/2003

APPLIES TO

- Microsoft Visual Basic 3.0 Professional Edition
- Microsoft Visual Basic 3.0 Professional Edition

This article was previously published under Q113032

## 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:

          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:

          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:

          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 query words: 3.00 Access

Keywords: kbprb KB113032