gderoeck
asked on
How to display a record count from a SQL query in the footer of an ASP.NET DataGrid with paging activated.
Hello!
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!
-Greg
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!
-Greg
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.toS tring()
end function
Your footer template will just call this function: <%# Format_PageCount() %>
Hope that helps.
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.toS
end function
Your footer template will just call this function: <%# Format_PageCount() %>
Hope that helps.
ASKER
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.SqlC ommand
command.Connection = connection
'Dim s as String
command.CommandText = "proc_InventoryReportMain"
command.CommandType = CommandType.StoredProcedur e
Session("SQLQuery") = "proc_InventoryReportMain '" & strDate & "'"
Dim param As System.Data.SqlClient.SqlP arameter
param = command.Parameters.Add("@D ateDataPro cessed", SqlDbType.nvarchar)
param.Direction = ParameterDirection.Input
param.Value = strDate
dim Query as new string("proc_InventoryRepo rtMain '" & strDate & "'")
Dim dataAdapter As New SqlClient.SqlDataAdapter(Q uery, connection)
dataAdapter.TableMappings. Add("Table ", "MIPMIGenData")
Dim DataSet As New DataSet
dataAdapter.Fill(DataSet)
Return DataSet
Disconnect()
'Disconnect Connection
End Function
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.SqlC
command.Connection = connection
'Dim s as String
command.CommandText = "proc_InventoryReportMain"
command.CommandType = CommandType.StoredProcedur
Session("SQLQuery") = "proc_InventoryReportMain '" & strDate & "'"
Dim param As System.Data.SqlClient.SqlP
param = command.Parameters.Add("@D
param.Direction = ParameterDirection.Input
param.Value = strDate
dim Query as new string("proc_InventoryRepo
Dim dataAdapter As New SqlClient.SqlDataAdapter(Q
dataAdapter.TableMappings.
Dim DataSet As New DataSet
dataAdapter.Fill(DataSet)
Return DataSet
Disconnect()
'Disconnect Connection
End Function
ASKER
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........
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........
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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