Microsoft KB Archive/147651

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

Article ID: 147651

Article Last Modified on 5/7/2003

-

APPLIES TO


 * Microsoft Visual Basic 5.0 Learning Edition
 * Microsoft Visual Basic 6.0 Learning Edition
 * Microsoft Visual Basic 5.0 Professional Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft Visual Basic 4.0 Professional Edition
 * Microsoft Visual Basic 4.0 16-bit Enterprise Edition

-



This article was previously published under Q147651



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:

 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: 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  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.</li> Add a data control (Data1) and a text box (Text1) to the form.</li>  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 </li> Run the program.</li></ol>

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.</li> Add a data control (Data1) and a text box (Text1) to the form.</li>  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 </li> Run the program.</li></ol>

Additional query words: kbVBp400 kbVBp500 kbVBp600 kbdse kbDSupport kbVBp

Keywords: kbprb KB147651

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.