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

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
Propriedades

ID do Artigo: 308050 - Última Revisão: 23 de mar de 2009 - Revisão: 1

Comentários