FIX: "Unhandled Exception" Error Message When You Use ExecuteReader Method with CommandBehavior.SingleRow (313547)


The information in this article applies to:

  • Microsoft ADO.NET (included with the .NET Framework)
This article was previously published under Q313547
This article refers to the following Microsoft .NET Framework Class Library namespaces:
  • System.Data
  • System.Data.OleDb

SYMPTOMS

If you try to return an empty DataReader object by using the ExecuteReader method of an OleDbCommand object with a CommandBehavior value of SingleRow, you receive the following error message or similar:
An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll
This occurs when you use the Microsoft OLE DB Provider for SQL Server with the OLE DB .NET Managed Provider.

If you catch the exception, the message of the exception states:
Object or data matching the name, range, or selection criteria was not found within the scope of this operation.
You expect the DataReader to be returned without an error. In addition, you expect the Read method of the DataReader to return False if no rows are found.

RESOLUTION

When you use the OLE DB Provider for SQL Server, do not use a CommandBehavior value of SingleRow if you do not return records with the ExecuteReader method of OleDBCommand.

Alternately, use the SQL Server .NET Managed Provider. The error does not occur when you use the SQL Server .NET Managed Provider, even if you use a CommandBehavior value of SingleRow.

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article. This bug was corrected in Microsoft ADO.NET (included with the .NET Framework 1.1).

MORE INFORMATION

Steps to Reproduce the Behavior

  1. Start Microsoft Visual Studio .NET, and then create a new Visual Basic Console application. Module1 and a reference to the System.Data namespace are added by default.
  2. Add the following Imports statements to the top of Module1's Code window:
    Imports System
Imports System.Data.OleDb
  3. Paste the following code into the Sub Main procedure:
    Dim cn As OleDbConnection = New OleDbConnection("Provider=SQLOLEDB;Data Source=servername;" & _
                                                "User ID=login;Password=password;" & _
                                                "Initial Catalog=Northwind;")
cn.Open()

'Specify that no records be returned.
Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM Customers WHERE 1=0", cn)

Dim dr As OleDbDataReader
Try
      dr = cmd.ExecuteReader(CommandBehavior.SingleRow)
      'WORKAROUND: Comment out the above line, and uncomment the following line.
      'dr = cmd.ExecuteReader()

      Console.WriteLine("ExecuteReader is executed")
      dr.Close()
Catch ex As Exception
      Console.WriteLine(ex.Message.ToString())
Finally
      'Pause.
      Console.ReadLine()
      'Clean up.
      cn.Close()
End Try
  4. Modify the connection string to connect to the computer that is running SQL Server.
  5. Press F5 to build and to start the project. You receive an error message. Press ENTER to return to the development environment.
  6. To work around this problem, comment out the following line 
          dr = cmd.ExecuteReader(CommandBehavior.SingleRow)
    and uncomment the following line:
          'dr = cmd.ExecuteReader()
  7. Press F5 to rebuild and start the project. Notice that the ExecuteReader method succeeds. Press ENTER to return to the Development Environment.

REFERENCES

For more information, visit the following Microsoft Web site:

OleDbCommand.ExecuteReader Method
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataOleDbOleDbCommandClassExecuteReaderTopic2.asp

Modification Type:MajorLast Reviewed:4/9/2003
Keywords:kbfix kbbug kbDatabase kbpending kbSystemData KB313547
©2002 Microsoft Corporation. All rights reserved.