How To Handle Multiple Results by Using the DataReader in Visual Basic .NET

Article translations Article translations
Article ID: 309490 - View products that this article applies to.
This article was previously published under Q309490
Expand all | Collapse all

On This Page

SUMMARY

This article contains a generic function that you can use to process multiple recordsets and other messages that are returned from stored procedures or the execution of batch SQL statements.

Description of the Technique

ActiveX Data Objects (ADO) can receive five different types of data from the server:
  • Recordset
  • Number of records that are modified by an action query (such as INSERT, UPDATE, DELETE, or SELECT INTO)
  • Informational message or warning
  • Error message
  • Stored procedure return values and output parameters
When you read the results of a batch SQL statement, you can use the NextResult method to position the DataReader at the next result in the resultset.

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that you need:
  • Microsoft Windows 2000 Professional, Windows 2000 Server, Windows 2000 Advanced Server, or Windows NT 4.0 Server
  • Microsoft Visual Studio .NET
  • Microsoft SQL Server 7.0 or later
This article assumes that you are familiar with the following topics:
  • Visual Studio .NET
  • ADO.NET fundamentals and syntax

Create Project and Add Code

This sample code uses the Authors table of the Pubs sample database.
  1. Paste the following statements into the SQL Query Analyzer tool or the ISQL utility:
    CREATE PROC MyProc
    AS
        SELECT * FROM Authors
        SELECT * FROM Authors WHERE State = 'CA'
    GO 
    					
  2. Open Visual Studio .NET.
  3. Create a new Visual Basic Windows Application project.
  4. Make sure that your project contains a reference to the System.Data namespace, and add a reference to this namespace if it does not.
  5. Place a Command button on Form1. Change the Name property of the button to btnTest, and change the Text property to Test.
  6. Use the Imports statement on the System, System.Data.OleDb, and System.Data.SqlClient namespaces so that you are not required to qualify declarations in those namespaces later in your code. Add the following code to the General Declarations section of Form1:
    Imports System
    Imports System.Data.OleDb
    Imports System.Data.SqlClient
    					
  7. Add the following code after the "Windows Form Designer generated code" region in the Code window.

    Note You must change User ID <username> and password =<strong password> to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.
        Private Sub btnTest_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles btnTest.Click
            Dim myConnString As String = _
                    "User ID=<username>;Password=<strong password>;Initial Catalog=pubs;Data Source=myServer"
            Dim myConnection As New SqlConnection(myConnString)
            Dim myCommand As New SqlCommand()
            Dim myReader As SqlDataReader
    
            myCommand.CommandType = CommandType.StoredProcedure
            myCommand.Connection = myConnection
            myCommand.CommandText = "MyProc"
            Dim RecordCount As Integer
    
            Try
                myConnection.Open()
                myReader = myCommand.ExecuteReader
                While myReader.Read()
                    'Write logic to process data for the first result.
                    RecordCount = RecordCount + 1
                End While
                MessageBox.Show("Total number of Authors: " & RecordCount.ToString)
    
                myReader.NextResult()
                RecordCount = 0
    
                While myReader.Read()
                    'Write logic to process data for the second result.
                    RecordCount = RecordCount + 1
                End While
                 MessageBox.Show("Authors from California: " & RecordCount.ToString)
            Catch ex As Exception
                MessageBox.Show(ex.ToString())
            Finally
                myConnection.Close()
            End Try
        End Sub
    					
  8. Modify the connection string (myConnString) as appropriate for your environment.
  9. Save your project. On the Debug menu, click Start to run your project.
  10. Click Test. Notice that the messages boxes display the data that the stored procedure returns.

REFERENCES

For additional information about calling stored procedures, click the article number below to view the article in the Microsoft Knowledge Base:
306574 How To Call SQL Server Stored Procedures in ASP.NET
For additional information about error handling, click the article number below to view the article in the Microsoft Knowledge Base:
308043 How To Obtain Underlying Provider Errors by Using ADO.NET in Visual Basic .NET
For additional information about parameters and stored procedures, click the article number below to view the article in the Microsoft Knowledge Base:
308051 PRB: Output Parameters Are Not Returned When You Run an ADO.NET Command in Visual Basic .NET
For more information on ADO.NET objects and syntax, refer to the following Microsoft .NET Framework Software Development Kit (SDK) documentation:
Accessing Data with ADO.NET
http://msdn2.microsoft.com/en-us/library/e80y5yhx(vs.71).aspx

Properties

Article ID: 309490 - Last Review: May 13, 2007 - Revision: 3.3
APPLIES TO
  • Microsoft ADO.NET (included with the .NET Framework)
  • Microsoft ADO.NET 1.1
  • Microsoft Visual Basic .NET 2002 Standard Edition
  • Microsoft Visual Basic .NET 2003 Standard Edition
Keywords: 
kbhowtomaster kbsqlclient kbsystemdata KB309490

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com