Help and Support
 

powered byLive Search

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

Article ID:308050
Last Review:March 9, 2006
Revision:1.2
This article was previously published under Q308050
For a Microsoft Visual C# .NET version of this article, see 308352 (http://support.microsoft.com/kb/308352/).

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

SYMPTOMS

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

Back to the top

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.

Back to the top

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).

Back to the top

STATUS

This behavior is by design.

Back to the top

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.

Back to the top

REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
194973 (http://support.microsoft.com/kb/194973/EN-US/) PRB: ADO: Recordcount May Return -1

Back to the top


APPLIES TO
Microsoft ADO.NET (included with the .NET Framework)
Microsoft Visual Basic .NET 2002 Standard Edition
Microsoft ADO.NET 2.0

Back to the top

Keywords: 
kbnofix kbprb kbsqlclient kbsystemdata KB308050

Back to the top

Article Translations

 

Related Support Centers

Other Support Options

  • Need More Help?
    Contact a Support professional by Email, Online or Phone.
  • Customer Service
    For non-technical assistance with product purchases, subscriptions, online services, events, training courses, corporate sales, piracy issues, and more.
  • Newsgroups
    Pose a question to other users. Discussion groups and Forums about specific Microsoft products, technologies, and services.