Microsoft KB Archive/198300

= PRB: Invalid Use of NULL Assigning Text Fields Value =

Article ID: 198300

Article Last Modified on 3/14/2005

-

APPLIES TO


 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition

-



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.



Steps to Reproduce Behavior
<ol> Create a Standard EXE project in Visual Basic.</li> Add a Reference to Microsoft Remote Data Object 2.0.</li> Add a multiline text box and a command button to the form.</li>  Place the following code in the command button Click event:

NoteYou must change UID= and PWD= 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= ;pwd= "

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 </li></ol>

Note Be sure to put your SQL Server name in the Connection string.

<div class="references_section">