PRB: Recordset Invalid When Oracle Decode Statement Returns a Null Value Under Oracle 8 (253674)


The information in this article applies to:

  • ActiveX Data Objects (ADO) 2.1 SP2
  • ActiveX Data Objects (ADO) 2.5
  • ActiveX Data Objects (ADO) 2.6
  • ActiveX Data Objects (ADO) 2.7
  • Microsoft Visual Basic Professional Edition for Windows 6.0
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
This article was previously published under Q253674

SYMPTOMS

When creating an ADO recordset using an Oracle Decode statement that returns null values, ADO generates the following error message:
Run-Time error 'Data Provider or other Service Returned an E_Fail Status.'
NOTE: This error does not occur when you use the Oracle 8.1.6 client.

RESOLUTION

Change the CursorLocation to adUseServer.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Start Visual Basic and create a Standard Exe project. Create a reference to Microsoft ActiveX Data Objects 2.1 or later.
  2. Place the following code into Form1.
    Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Private Sub Form_Load()
    'Comment out the line below to make it work.
    cn.CursorLocation = adUseClient
    cn.Open "Provider=MSDAORA;Data Source=YourOraclealias;User ID=YourUserID;Password=YourPassword"
    rs.Open "select decode(employee_id,'7394','matches','') from employee", cn, adOpenKeyset, adLockOptimistic
    Debug.Print rs(0)

End Sub
  3. Change the ConnectionString to use a valid Data Source.
  4. Run the project.Result: The above mentioned run-time error.

  5. Comment this line of code: cn.CursorLocation = adUseClient
  6. Run the project again.Result: No errors. The recordset contains nulls but is valid.

REFERENCES

Modification Type:MajorLast Reviewed:8/23/2001
Keywords:kbDSupport kbOracle kbprb KB253674
©2002 Microsoft Corporation. All rights reserved.