Troubleshooting
Problem
ADO may return the run-time error "Error 94 : Invalid use of Null" when accessing LOB fields a second time.
Resolving The Problem
Problem Description
When using ADO to retrieve the value of fields with an attribute adFldLong, ADO will return the correct value for the data the first time the field value is accessed. Any further attempts to access the field data will return the value NULL. The Run-time error Error 94 : Invalid use of Null is commonly seen. The error affects IBM OS/400 and IBM i5/OS data types of BLOB, CLOB, and DBCLOB. The error occurs with MDAC 2.5, 2.6, and 2.7.
The error is seen regardless of how the data is accessed. The Value property and the GetChunk method experience the same error.
Problem Detail
The Microsoft OLEDB provider for ODBC (MSDASQL) is using the SQLGetData API to retrieve long data. MSDASQL first calls SQLGetData with a zero length buffer to get the actual data size. If the Value property is used to access the data, MSDASQL then calls SQLGetData with a buffer size large enough for all data. If the GetChunk method is used, MSDASQL makes several calls to SQLGetData until all the data is returned. In each case, Client Access and MSDASQL return the data and proper data length. If the application then attempts to access the data a second time, MSDASQL repeats the first step. This fails with SQL_NO_DATA.
The SQLGetData API does not allow an application to retrieve data for the same column more then once. The SQLGetData API documentation states that the first step the ODBC driver performs is: "1. Returns SQL_NO_DATA if it has already returned all of the data for the column." MSDASQL and ADO map this to a null value. Also note that this error is not specific to Client Access or OS/400 or i5/OS. The same error was seen in testing of SQL Server 2000 text fields and the SQL Server 2000 ODBC driver used with MSDASQL.
Problem Resolution
This is a limitation of the Microsoft OLEDB provider for ODBC and ADO. Applications must access the field value only one time. In Microsoft Visual Basic, there are several ways that the data is accessed besides directly checking the value property. These include:
| o | Checking for null using the isNull function |
| o | Moving the cursor over the field object name while in debug mode of the IDE |
| o | Adding a watch in the IDE |
Example
Dim clobdata As String
Dim v as Variant
Do
v = rs(4).GetChunk(BlockSize)
'If remainder from previous call is 0 then GetChunk returns null
Debug.Print "length so far: " & Len(clobdata) & " vartype:" & VarType(v)
if isNull(v) then
if Len(clobdata) = 0 then clobdata = "<null>"
exit do 'No more data to fetch, remainder was 0
end if
'Save data to file or variable
clobdata = clobdata & v
Loop While Len(v) = BlockSize 'len of last chunk will be < blocksize if
'data is not a multiple of blocksize
Historical Number
26251112
Was this topic helpful?
Document Information
Modified date:
18 December 2019
UID
nas8N1017056