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

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.

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


                Connect()
                '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

                dataAdapter.Fill(DataSet)
                Return DataSet


                Disconnect()
                'Disconnect Connection

            End Function

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
                Try
                    While myReader.Read()
                        Count = Count + 1
                    End While
                Finally
                    myReader.Close()
                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........