There is no RecordCount property when yo use the OleDbDataReader class or the SqlDataReader class in Visual Basic .NET (308050)


The information in this article applies to:

  • Microsoft ADO.NET (included with the .NET Framework)
  • Microsoft Visual Basic .NET (2002)
  • Microsoft ADO.Net 2.0
This article was previously published under Q308050
For a Microsoft Visual C# .NET version of this article, see 308352.

This article refers to the following Microsoft .NET Framework Class Library namespace:
  • System.Data.SqlClient

SYMPTOMS

When using the OleDbDataReader or SqlDataReader class, there is no RecordCount property to tell how many records are being fetched.

CAUSE

The DataReader object (or even the back-end data source) doesn't typically know how many records are being fetched until the last one is sent to the client. Even an ActiveX Data Objects (ADO) Recordset returns -1 for RecordCount when retrieving data using a forward-only cursor. DataReader exhibits similar behavior because it uses a forward-only cursor for retrieving rows and columns.

RESOLUTION

You can work around this problem in the following ways:
  • Count the records as you go through the reader.
  • Run a SELECT COUNT(*) query first (although this may be out of date by the time you finish reading data).

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce the Behavior

  1. Open Visual Studio .NET.
  2. Create a new Windows Application in Visual Basic .NET.
  3. Make sure that your project contains a reference to the System.Data namespace.
  4. Place a Command button on Form1 and change its Name property to btnTest.
  5. Use the Imports statement on the System and System.Data namespaces so that you are not required to qualify declarations in those namespaces later in your code.
    Imports System
Imports System.Data
Imports System.Data.SqlClient
  6. Paste the following code in the code window after the region "Windows Form Designer generated code":
    Private Sub btnTest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTest.Click
    Dim myConnString As String = _
        "User ID=sa;password=sa;Initial Catalog=pubs;Data Source=mySQLServer"
    Dim mySelectQuery As String = "SELECT * FROM Authors"
    Dim myConnection As New SqlConnection(myConnString)
    Dim myCommand As New SqlCommand(mySelectQuery, myConnection)
    myConnection.Open()

    Dim myReader As SqlDataReader
    myReader = myCommand.ExecuteReader()
    Dim RecordCount As Integer
    Try
        While myReader.Read()
            RecordCount = RecordCount + 1
        End While

        If RecordCount = 0 then
            MessageBox.Show("No data returned")
        Else
            MessageBox.Show("Number of Records returned: " & RecordCount)
        End If
    Catch ex As Exception
        MessageBox.Show(ex.ToString())
    Finally
        myReader.Close()
        myConnection.Close()
    End Try
End Sub
  7. Modify the Connection string (myConnString) as appropriate for your environment.
  8. Save your project. On the Debug menu, click Start, and run your project.
  9. Click the button and you can see the record count.

REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

194973 PRB: ADO: Recordcount May Return -1

Modification Type:MinorLast Reviewed:3/9/2006
Keywords:kbnofix kbprb kbSqlClient kbSystemData KB308050
©2004 Microsoft Corporation. All rights reserved.