Microsoft KB Archive/308050

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

Article ID: 308050

Article Last Modified on 3/9/2006

-

APPLIES TO


 * Microsoft .NET Framework 1.1 Service Pack 1
 * Microsoft Visual Basic .NET 2002 Standard Edition
 * 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.



Steps to Reproduce the Behavior
 Open Visual Studio .NET. Create a new Windows Application in Visual Basic .NET. Make sure that your project contains a reference to the System.Data namespace. Place a Command button on Form1 and change its Name property to btnTest.</li>  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 </li>  Paste the following code in the code window after the region &quot;Windows Form Designer generated code&quot;: Private Sub btnTest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTest.Click Dim myConnString As String = _ &quot;User ID=sa;password=sa;Initial Catalog=pubs;Data Source=mySQLServer&quot; Dim mySelectQuery As String = &quot;SELECT * FROM Authors&quot; 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(&quot;No data returned&quot;) Else MessageBox.Show(&quot;Number of Records returned: &quot; & RecordCount) End If   Catch ex As Exception MessageBox.Show(ex.ToString) Finally myReader.Close myConnection.Close End Try End Sub </li> Modify the Connection string (myConnString) as appropriate for your environment.</li> Save your project. On the Debug menu, click Start, and run your project.</li> Click the button and you can see the record count.</li></ol>

<div class="references_section">