Click here to Skip to main content
13,045,790 members (46,190 online)
Click here to Skip to main content
Add your own
alternative version


31 bookmarked
Posted 18 Nov 2003

How to bind multiple resultsets into a DataGrid control

, 18 Nov 2003
Rate this:
Please Sign up or sign in to vote.
This article describes how to bind multiple resultsets into a DataGrid control.


There are sometimes when you find yourself in a situation where you obtain many resultsets from a given stored procedure, and then you want to bind those results into a DataGrid control. This article shows a very simple example of how to do this.

The Problem

The initial approach one may take is just to try and get the results into a DataSet or DataReader. The problem is that if you get the results in any of these objects, and try to bind it to a DataGrid control, you would only get the results of the first table (resultset) that your stored procedure returns.

After trying to find some help in newsgroups all over the web, I couldn’t find a solution to do this. So I began thinking in an alternative way of how to do it, and I found myself with a very simple answer.

The Solution

I needed an object that could be bound to a DataGrid, so I thought, why not use a DataTable object. So the only thing left to do was to create the DataTable with the results of the different tables that I get from my stored procedure. This can be done very easily with the help of the DataReader object.

The Code

First we write the code for the DataReader that holds the results of the SP.

Public Function GetSPResults(intMyParam As Integer) As SqlDataReader
 Dim myConnection As New _ 
 Dim myCommand As New SqlCommand("MySP", myConnection)
 myCommand.CommandType = CommandType.StoredProcedure
 myCommand.Parameters.Add(New SqlParameter("@MyParam", SqlDbType.Int))
 myCommand.Parameters("@MyParam").Value = intMyParam 
 Dim dr As SqlDataReader = _ 
 Return dr
End Function

Now we create the table object with the fields we need and we add the data from the results in the SP above.

Private Function CreateTable() As DataTable
    Dim drData As SqlDataReader
    Dim flgNextResult As Boolean = True
    'create a new empty Table object 
    Dim objTable As New DataTable("MyTable")

    'Add fields to the table
    objTable.Columns.Add("Number", System.Type.GetType("System.String"))
    objTable.Columns.Add("Description", System.Type.GetType("System.String"))
    objTable.Columns.Add("Amount", System.Type.GetType("System.Decimal"))

    Dim objDataRow As DataRow
    Dim decAmount As Decimal
    Dim strAccount As String
    Dim strDesc As String

    'Get the results from the Function above
    drData = GetSPResults()

    'Loop through each resultsets
    Do Until Not flgNextResult
        'Loop through each row of a resultset
        Do While drData.Read()
            If Not drData.IsDBNull(0) Then
                decAmount = drData("Amount")
                strAccount = drData("Number")
                strDesc = drData("Description")
                'Generate new row in table
                objDataRow = objTable.NewRow()

                'Add values to fields in the row
                objDataRow("Number") = strAccount
                objDataRow("AmountPY") = decAmountPY
                objDataRow("Description") = strDesc

                'Add the row to the table
            End If
    flgNextResult = drData.NextResult()

    'Return table object
    Return objTable
End Function

Finally, we bind the object to a DataGrid in a BindGrid function.

Private Sub BindGrid()
    Dim dtData As New DataTable
    dtData = CreateTable()
    DataGrid1.DataSource = dtData.DefaultView
End Sub


We have seen one very simple approach of being able to achieve this goal. Now, we can even page, sort or whatever we want to do with our DataGrid. I hope you find this approach useful.


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

Hugo Flores
Web Developer
United States United States
No Biography provided

You may also be interested in...


Comments and Discussions

GeneralMy vote of 5 Pin
manoj kumar choubey7-Feb-12 19:28
membermanoj kumar choubey7-Feb-12 19:28 
GeneralFollow-Up Question Pin
sueolo12-Sep-05 6:03
membersueolo12-Sep-05 6:03 
GeneralWhy use a DataReader and a DataTable Pin
Andrew Wiseman12-May-05 2:09
sussAndrew Wiseman12-May-05 2:09 
GeneralRe: Why use a DataReader and a DataTable Pin
Hugo Flores12-May-05 14:33
memberHugo Flores12-May-05 14:33 
QuestionWouldn't this be easier with a SQL UNION clause ? Pin
Erhan Hosca26-Nov-03 4:22
memberErhan Hosca26-Nov-03 4:22 
AnswerRe: Wouldn't this be easier with a SQL UNION clause ? Pin
Hugo Flores1-Dec-03 4:49
memberHugo Flores1-Dec-03 4:49 
QuestionHow about a DataView Pin
Marc Clifton19-Nov-03 13:40
editorMarc Clifton19-Nov-03 13:40 
AnswerRe: How about a DataView Pin
Hugo Flores1-Dec-03 4:45
memberHugo Flores1-Dec-03 4:45 
GeneralRe: How about a DataView Pin
Marc Clifton1-Dec-03 10:56
editorMarc Clifton1-Dec-03 10:56 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.170713.1 | Last Updated 19 Nov 2003
Article Copyright 2003 by Hugo Flores
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid