Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 21

Expert Comment

ID: 12451902
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

Expert Comment

ID: 12461971
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.

Author Comment

ID: 12464415
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

Author Comment

ID: 12464845
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........

Accepted Solution

sbrown2k earned 375 total points
ID: 12470513

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>)

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
Make the most of your online learning experience.
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 …
Starting up a Project

618 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