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:
- Start a new project in Visual Basic. Form1 is created by default.
- Add a data control (Data1) and a text box (Text1) to the form.
- 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
- 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:
- Start a new project in Visual Basic. Form1 is created by default.
- Add a data control (Data1) and a text box (Text1) to the form.
- 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
- 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:
- Start a new project in Visual Basic. Form1 is created by default.
- Add a data control (Data1) and a text box (Text1) to the form.
- 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
- Run the program.