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


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!

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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.
gderoeckAuthor Commented:
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
gderoeckAuthor Commented:
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........

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.