ASP.Net GridView Group Rows


This article is written by Pon Saravanan  on 23-Jul-10 Last modified on :23-Jul-10





Grouping data in the ASP.Net GridView

 Presenting a grouped data in the standard GridView is really a tough task to get it to a satisfactory level. If you ever worked in Spreadsheets to group the data, you may have seen that the merge functionality gives a very good presentation for the grouped data

Note:

1) As it is already explained in earlier articles several times, I am not going to explain again how to get the data and how to bind the data from database. So please refer to the Asp.net DataControls section or GridView section for that matter.
2) Northwind database is used for samples, this database can be downloaded from MSDN
3) This sample is to explain how to have this merge functionality using row span attribute of the cell. So it may be a tough task to extend this task to get two or more columns to have this merge functionality.

RowSpan and ColSpan in HTML TD

In HTML TD we can use the COL span to spread a single cell to two columns. Likewise we can spread a cell to two rows also. In both the cases the cell where it is going to spread itself should not be exist. Otherwise you will see an additional cell. Keep this in mind before reading further. Without knowing this the source code may be bit harder to understand.

Screen Capture


 

RowDataBound event for grouping

I have preferred to write the function related to the grouping in the RowDataBound event since the RowDataBound will be fired for each row. So you don’t need to iterate all the rows again if you can finish everything in this event.

Use of Static variables

As I have already decided to write everything into the RowDataBound event, I ended up using few static variables. As the static variables will hold the value during the lifespan of the application, I preferred to use them to share the variables between rows(while iterating through rows in RowDataBound event).

Logic used to Group

Rowspan is used to specify how many rows the first cell has to spread. In addition the cells under the first cell in each row have to be removed. It is that simple. If you see the sample section you will know how I have accomplished this.

Source Code

Markup(ASPX)

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server" defaultbutton="btnLoad">
    <asp:Button runat="server" ID="btnLoad" Text="Load" />
        <asp:GridView runat="server" AutoGenerateColumns="false" ID="GridView1">
            <Columns>
                <asp:BoundField DataField="CategoryName" HeaderText="CategoryName" />
                <asp:BoundField DataField="ProductName" HeaderText="ProductName" />
                <asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice" />
            </Columns>
        </asp:GridView>
    </form>
</body>
</html>

Code-behind(ASPX.VB)

Imports System.Data.SqlClient
Imports System.Data
Partial Class _Default
    Inherits System.Web.UI.Page
    Private RowCount As Integer
    Private ReadOnly Property ConnectionString() As String
        Get
            Return "Server=.\SQLEXPRESS;Database=NorthWind;" & _
                    "Trusted_Connection=True"
        End Get
    End Property
    Private ReadOnly Property Connection() As SqlConnection
        Get
            Dim ConnectionToFetch As New SqlConnection(ConnectionString)
            ConnectionToFetch.Open()
            Return ConnectionToFetch
        End Get
    End Property
    Public Function GetData() As DataView
        Dim SelectQry = "select CategoryName,ProductName,UnitPrice " & _
        " from Products P Inner Join Categories C " & _
        " on P.CategoryID=C.CategoryID Order By CategoryName"
        Dim SampleSource As New DataSet
        Dim SortableView As DataView
        Try
            Dim SampleDataAdapter = New SqlDataAdapter(SelectQry, _
                                                       ConnectionString)
            SampleDataAdapter.Fill(SampleSource)
            SortableView = SampleSource.Tables(0).DefaultView
            RowCount = SampleSource.Tables(0).Rows.Count
        Catch ex As Exception
            Throw ex
        End Try
        Return SortableView
    End Function
   
    Private Sub LoadGrid()
        GridView1.DataSource = GetData()
        GridView1.DataBind()
    End Sub
    Protected Sub btnLoad_Click(ByVal sender As Object, _
                    ByVal e As System.EventArgs) Handles btnLoad.Click
        LoadGrid()
    End Sub
    Protected Sub GridView1_RowDataBound(ByVal sender As Object, _
                    ByVal e As GridViewRowEventArgs) Handles GridView1.RowDataBound
        Static RowSpan As Integer
        Static PrevItem As String
        Static FirstItemInIndex As Integer
        If (e.Row.RowIndex = 0) Then
            PrevItem = e.Row.Cells(0).Text
            FirstItemInIndex = 0
            RowSpan = 0
        End If
        If (e.Row.RowType = DataControlRowType.DataRow) Then
            If (PrevItem = e.Row.Cells(0).Text) Then
                If (e.Row.RowIndex <> FirstItemInIndex) Then
                    e.Row.Cells.RemoveAt(0)
                End If
                RowSpan += 1
            Else
                Dim FirstCell = GridView1.Rows(FirstItemInIndex).Cells(0)
                FirstCell.RowSpan = RowSpan
                FirstItemInIndex = e.Row.RowIndex
                PrevItem = e.Row.Cells(0).Text
                RowSpan = 1
            End If
        End If
        If (e.Row.RowIndex = RowCount - 1) Then
            Dim FirstCell = GridView1.Rows(FirstItemInIndex).Cells(0)
            FirstCell.RowSpan = RowSpan
        End If
    End Sub
End Class




« Previous -







Comments
  • GUEST
    The "FirstItemInIndex" variable seems to exceed
    the bounds of the array.
    just change it to FirstItemInIndex - 1
    8/3/2010 2:55:50 AM

  • GUEST
    Are you getting any exceptions? 8/3/2010 5:32:44 AM

  • GUEST
    This is just awesome!!! Thank you. 8/3/2010 9:45:58 AM

  • GUEST
    Cannot get this to work. I keep getting exceptions on the FirstItemIndex at the bottom 8/26/2010 2:25:06 PM

  • GUEST


    getting exceptions on then FirstItemInIndex too

    4/6/2011 12:10:43 PM

  • GUEST
    How can I modify this to apply it to all the columns of the GridView? 4/26/2011 4:42:41 AM

  • GUEST
    Gracias. Execelente lo que buscaba 4/26/2011 9:08:53 AM

  • GUEST
    hahahahaha 5/4/2011 2:37:01 AM

  • GUEST
    Thanks for this idea. I have modified the same code and made it work for 2 levels and with Paging option. Here is the code.

    Protected Sub grdDetails_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles grdDetails.RowDataBound
    Static RowSpan As Integer
    Static PrevItem As String
    Static PrevItem1 As String
    Static FirstItemInIndex As Integer
    Static FirstItemInIndex1 As Integer

    If (e.Row.RowType = DataControlRowType.DataRow) Then

    If (e.Row.RowIndex = 0) Then
    PrevItem = e.Row.Cells(0).Text
    PrevItem1 = e.Row.Cells(1).Text
    FirstItemInIndex = 0
    RowSpan = 0
    End If

    If (PrevItem = e.Row.Cells(0).Text) Then
    ''---------------------------------------------------------------------- ''-----------------Second column---------------------------------------- ''---------------------------------------------------------------------- If (PrevItem1 = e.Row.Cells(1).Text) Then
    If (e.Row.RowIndex <> FirstItemInIndex1) Then
    e.Row.Cells(1).Text = ""
    End If
    Else
    PrevItem1 = e.Row.Cells(1).Text
    End If
    ''---------------------------------------------------------------------- ''---------------------------------------------------------------------- ''---------------------------------------------------------------------- If (e.Row.RowIndex <> FirstItemInIndex) Then
    e.Row.Cells.RemoveAt(0)
    End If
    RowSpan += 1
    Else
    Dim FirstCell = grdDetails.Rows(FirstItemInIndex).Cells(0)
    FirstCell.RowSpan = RowSpan
    FirstItemInIndex = e.Row.RowIndex
    PrevItem = e.Row.Cells(0).Text
    PrevItem1 = e.Row.Cells(1).Text
    RowSpan = 1
    End If

    If (e.Row.RowIndex = RowCount - 1) Or (e.Row.RowIndex = grdDetails.PageSize - 1) Then
    Dim FirstCell = e.Row.Cells(0)
    FirstCell.RowSpan = RowSpan
    ElseIf (e.Row.DataItemIndex = RowCount - 1) Then
    grdDetails.Rows(FirstItemInIndex).Cells(0).RowSpan = RowSpan
    End If

    End If

    End Sub
    7/4/2011 12:19:01 AM

  • GUEST
    Thank you for the code, Much Appreciated. 7/4/2011 12:32:50 AM

  • GUEST
    Sorry some corrections in the above code

    Protected Sub grdDetails_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles grdDetails.RowDataBound
    Static RowSpan As Integer
    Static PrevItem As String
    Static PrevItem1 As String
    Static FirstItemInIndex As Integer
    Static FirstItemInIndex1 As Integer

    If (e.Row.RowType = DataControlRowType.DataRow) Then

    If (e.Row.RowIndex = 0) Then
    PrevItem = e.Row.Cells(0).Text
    PrevItem1 = e.Row.Cells(1).Text
    FirstItemInIndex = 0
    RowSpan = 0
    End If

    If (PrevItem = e.Row.Cells(0).Text) Then
    ''------------------------------------------------------------------------------------------
    ''-----------------Second column------------------------------------------------------------
    ''------------------------------------------------------------------------------------------
    If (PrevItem1 = e.Row.Cells(1).Text) Then
    If (e.Row.RowIndex <> FirstItemInIndex1) Then
    e.Row.Cells(1).Text = ""
    End If
    Else
    PrevItem1 = e.Row.Cells(1).Text
    End If
    ''------------------------------------------------------------------------------------------
    ''------------------------------------------------------------------------------------------
    ''------------------------------------------------------------------------------------------
    If (e.Row.RowIndex <> FirstItemInIndex) Then
    e.Row.Cells.RemoveAt(0)
    End If
    RowSpan += 1
    Else
    Dim FirstCell = grdDetails.Rows(FirstItemInIndex).Cells(0)
    FirstCell.RowSpan = RowSpan
    FirstItemInIndex = e.Row.RowIndex
    PrevItem = e.Row.Cells(0).Text
    PrevItem1 = e.Row.Cells(1).Text
    RowSpan = 1
    End If

    If (e.Row.RowIndex = RowCount - 1) Or (e.Row.RowIndex = grdDetails.PageSize - 1) Then
    If FirstItemInIndex = 0 Then
    Dim FirstCell = grdDetails.Rows(FirstItemInIndex).Cells(0)
    FirstCell.RowSpan = RowSpan
    Else
    Dim FirstCell = grdDetails.Rows(FirstItemInIndex - 1).Cells(0)
    FirstCell.RowSpan = RowSpan
    End If
    ElseIf (e.Row.DataItemIndex = RowCount - 1) Then
    grdDetails.Rows(FirstItemInIndex).Cells(0).RowSpan = RowSpan
    End If

    End If

    End Sub
    7/4/2011 1:27:47 AM

  • GUEST
    i already put FirstItemInIndex - 1 , but it still shown an error "Index was out of range. Must be non-negative and less than the size of the collection.
    Parameter name: index"
    1/18/2013 9:33:45 PM


Comments
   
Captcha Image
For you specially:  
Captcha Text Enter the text in the image.(Not Case sensitive)    



Spam Bot Trap



   



Select Theme
White
Blue
Brown
Gray