PRB: Invalid Use of NULL Assigning Text Fields Value (198300)


The information in this article applies to:

  • Microsoft Visual Basic Enterprise Edition for Windows 5.0
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
This article was previously published under Q198300

SYMPTOMS

When attempting to assign the value of a SQL Server text field to a text box control the following error may occur:
Invalid Use of Null.
The SQL Server text field may actually contain data in it, but even checking the value while in Debug mode can cause the fields contents to change to NULL.

CAUSE

Neither Remote Data Objects (RDO) or ActiveX Data Objects (ADO) are caching the text field value so the requests are sent back to the dataprovider. The cursor engine may not have enough information to request that field value again from the server.

RESOLUTION

There are several workarounds for this behavior:
  • Set the Cursorlocation to Rduseclient (RDO) or aduseclient (ADO).
  • Store the value of the fields contents to a variable BEFORE checking it or assigning to a text box control.
  • Instead of checking for nulls, use the following code to fill the text box:

    Text1.Text = recordset!field_name & ""
  • Use the Getchunk method to retrieve the contents of the text field. See article references below.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Create a Standard EXE project in Visual Basic.
  2. Add a Reference to Microsoft Remote Data Object 2.0.
  3. Add a multiline text box and a command button to the form.
  4. Place the following code in the command button Click event:

    NoteYou must change UID=<username> and PWD=<strong password> to the correct values before you run this code. Make sure that UID has the appropriate permissions to perform this operation on the database.
          Dim en As rdoEnvironment
      Dim cn As rdoConnection
      Dim rs As rdoResultset

      Dim sql As String

      sql = "Select * from pub_info"

      Set en = rdoEngine.rdoEnvironments(0)

      With en

         .CursorDriver = rdUseOdbc
         ' uncomment the following line for first workaround
         '.CursorDriver = rdUseClientBatch

      End With

      Dim cnStr As String

      cnStr = "driver={SQL Server};server=myserver;" & _
              "database=pubs;uid=<user name>;pwd=<strong password>"

      Set cn = en.OpenConnection(DSName:="", Prompt:=rdDriverNoPrompt, _
             Connect:=cnStr)

      Set rs = cn.OpenResultset(sql, rdOpenDynamic, rdConcurValues, _
             rdAsyncEnable)

      While rs.StillExecuting
          DoEvents
      Wend

      rs.MoveLast
      MsgBox "RDO: " & Str(rs.RowCount) & " rows returned."

      ' Move back to first record.
        rs.MoveFirst

      ' Uncomment the following two lines for the second workaround:
      'Dim test As Variant
      'test = rs!pr_info

      ' Check for null value before assigning.
      If IsNull(rs!pr_info) Then
     ' Comment preceding line and uncomment following line for second
     ' workaround:
     'If IsNull(test) Then
         Text1.Text = ""
          Else
        ' Assign value to text field if not NULL.
        Text1.Text = rs!pr_info
        ' Comment preceding line and uncomment following line for second
        ' workaround:
        'Text1.Text = test
      End If

      Do While Not rs.EOF
        Debug.Print rs!pr_info
        rs.MoveNext
      Loop

      rs.Close
      cn.Close
      en.Close
Note Be sure to put your SQL Server name in the Connection string.

REFERENCES

For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

194975 HOWTO: Read and Write BLOBs Using GetChunk and AppendChunk

153238 HOWTO: Use GetChunk and AppendChunk Methods of RDO and ADO Object

Modification Type:MinorLast Reviewed:3/14/2005
Keywords:kbprb kbRDO KB198300
©2004 Microsoft Corporation. All rights reserved.