Results 1 to 1 of 1

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

  1. #1

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    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.
    Last edited by si_the_geek; Oct 30th, 2007 at 11:35 AM. Reason: changed code tags for clarity

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width