[Webinar] Streamline your web hosting managementRegister Today


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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
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 …
Six Sigma Control Plans

590 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