Database - Why do I get the error "Invalid use of Null" when using field values?

This is a fairly common error when using field values to set the values of controls (such as TextBoxes), and is caused by a concept which can be hard to grasp - Nulls.

Null is not a value, it is instead the absence of a value. It is not the equivalent of 0 (for numeric fields) or "" (for string fields), it is unknown or not set.

If you try to set the value of a textbox (or other control) to a field which is Null, the control doesn't know how to deal with it - as you are basically saying "dont have a value at all" instead of "be an empty string".


The following examples assume that your current code is like this:

Code:

---

Text1.Text = myRecordset.Fields("FieldName").Value

---

Note that the corrections shown will work for most controls, and syntax for specifying the field (eg: myRecordset.("FieldName") / myRecordset!FieldName).

In order to fix the problem you can detect if the field is Null, and if so just use an empty string as the value, eg:

Code:

---

  If IsNull(myRecordset.Fields("FieldName").Value) Then
    Text1.Text = ""
  Else
    Text1.Text = myRecordset.Fields("FieldName").Value
  End If

---

Alternatively, you can use a nice little trick to convert the value to a valid string. If you append an empty string ("") to a Null in VB, the resultant text is an empty string - while appending an empty string to the end of a string value will make no difference!

This means that the only extra code you need for each string/text field which can be Null is a few characters:

Code:

---

  Text1.Text = myRecordset.Fields("FieldName").Value & ""

---

One thing to bear in mind is how you treat the values when you write them back to the database - do you want to write an empty string to the databse if the textbox is empty, or do you want to write Null? This is a question which only you can answer, and the methods you can use to do this will depend on your circumstances.