How to display a record count from a SQL query in the footer of an ASP.NET DataGrid with paging activated.

Posted on 2004-10-29
Last Modified: 2008-02-01

I have a ASP.NET DataGrid displaying records from a SQL query.  I have paging activated and everything works fine.  What I'd like to do now is to display the total number of records produced from the query in the footer of the DataGrid.  I am using a code behind page and I'm using VB.NET.  Web Matrix is my development tool, if that matters.  Thank you in advance!

Question by:gderoeck
    LVL 21

    Expert Comment

    In most databases, the total number of records are not computed until you retrieve the last row from the result set.  Therefore, to get a count have a count version of the query:
             select count(*) from TABLELIST where WHERECLAUSE
    where TABLELIST is the list of tables you are quering
    and WHERCLAUSE is the same where clause you used in the result set query
    LVL 1

    Expert Comment

    Greg -

    I believe you want to create a footer template that calls a function in your CodeBehind (let us call it "Format_PageCount()").
    Since the RENDERING of the data grid takes place AFTER the actual query, the record count is already known.
    After you build your data source you can get the record count from it (from the DataReader if you are using SQLClient).
    CACHE this value (or the entire recordset) into a member variable of the code behind.  Then your
    Format_PageCount function just needs to be something as simple as:

    protected function Format_PageCount()
      return "Page "  & m_my_cached_page_count.toString()
    end function

    Your footer template will just call this function: <%# Format_PageCount() %>

    Hope that helps.
    LVL 1

    Author Comment

    Problem is, I'm not using a DataReader - I'm using a DataAdapter to fill in my recordset.  Maybe you could give me the steps I need to do this?  I'm learning..........  Thanks for the responses!

    This is the function that builds my recordset:

    Private Function CreatePMIMIGenDataSet() As DataSet

                    if ddDates.SelectedIndex <> -1 then
                         strDate = ddDates.SelectedItem.Text
                      end if

                    'Connection String

                    Dim command As New System.Data.SqlClient.SqlCommand
                    command.Connection = connection

                    'Dim s as String

                    command.CommandText = "proc_InventoryReportMain"
                    command.CommandType = CommandType.StoredProcedure

                    Session("SQLQuery") = "proc_InventoryReportMain '" & strDate & "'"

                    Dim param As System.Data.SqlClient.SqlParameter
                    param = command.Parameters.Add("@DateDataProcessed", SqlDbType.nvarchar)
                    param.Direction = ParameterDirection.Input
                    param.Value = strDate

                    dim Query as new string("proc_InventoryReportMain '" & strDate & "'")
                    Dim dataAdapter As New SqlClient.SqlDataAdapter(Query, connection)
                    dataAdapter.TableMappings.Add("Table", "MIPMIGenData")

                    Dim DataSet As New DataSet

                    Return DataSet

                    'Disconnect Connection

                End Function
    LVL 1

    Author Comment

    Ok, I figured it out.  In the function I used to build my dataset, before I set up the data adapter, I put in the following code:

    Dim myReader as SqlDataReader
                    myReader = command.ExecuteReader()
                    Count = 0
                        While myReader.Read()
                            Count = Count + 1
                        End While
                    End Try

    Count is a public variable.  I then put the following code in my DataGrid OnItemCreatedEventHandler property function:

    Dim itemType As ListItemType
                    itemType = e.Item.ItemType
                    If itemType = ListItemType.Footer Then

                        Dim msg As String

                        msg = "<b>" & Count & " Loans</b>"

                        Dim msgCell As TableCell
                        msgCell = e.Item.Cells(0)
                        msgCell.Text = msg
                        msgCell.HorizontalAlign = HorizontalAlign.Left
                    End If

    Now, every time I click the button to refresh the data based on the data chosen, it gives me a record count in the footer.

    Anyone see any potential problems here?  Again, I am new, but learning........
    LVL 1

    Accepted Solution


    1. Your method for getting the count is fine. You could probably be more elegant about it but not worth it IMHO.
    2. Are you specifying the columns in the data grid explicitly or are you using AutoGenerateColumns=true?

    Post all the text betweeen <asp:DataGrid> and the closing tag. (</asp:DataGrid>)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    Here we come across an interesting topic of coding guidelines while designing automation test scripts. The scope of this article will not be limited to QTP but to an overall extent of using VB Scripting for automation projects. Introduction Now…
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
    Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

    911 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now