• 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

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