ASP.Net Gridview Paging=True Record Count

[tqeonline]

Hello All,

i posted this in a different forum and they told me I should try it here. That's why this might be a redundant post to some if you are in that forum as well. So, sorry.

I have been trying for the past couple hours trying to get the # of results to show up on this query.

I have a query that runs to a gridview and paging is enabled.

I need to know the exact number of results that are displayed.

I am doing it all in VB.

Gridview = GridView1
DataSource = SqlDataSource1

If there was a cool way to make it show something like

"You are view results 1 to 10 out of 678" That'd be ideal.

 

[tqeonline]

The SQLsource is listed as a "Dataset".

Here is the code I have it pulling from the tables.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
                    ConnectionString="<%$ ConnectionStrings:INTABLESConnectionString %>" 
                    
                    
                    SelectCommand="SELECT Stock_Num AS Location, Stock_Desc AS Description, Stock_QOH AS QOH, GMPN + ' ' + DelphiPN + ' ' + FCIPN + ' ' + MolexPN + ' ' + MolexPN + ' ' + TycoPN + ' ' + YazakiPN + ' ' + EPCPN + ' ' + Other AS PN FROM Stock_Info WHERE (Stock_QOH > 0) AND (GMPN + ' ' + DelphiPN + ' ' + FCIPN + ' ' + MolexPN + ' ' + MolexPN + ' ' + TycoPN + ' ' + YazakiPN + ' ' + EPCPN + ' ' + Other LIKE '%' + ? + '%') UNION ALL SELECT Stock_Num AS Location, Stock_Desc AS Description, Stock_QOH AS QOH, Stock_PN AS PN FROM ManEntry WHERE (Stock_QOH > 0) AND (Stock_PN LIKE '%' + ? + '%')" 
                    
                    
                    ProviderName="<%$ ConnectionStrings:INTABLESConnectionString.ProviderName %>">
                    <SelectParameters>
                        <asp:ControlParameter ControlID="txtSearch" Name="?" PropertyName="Text" 
                            DefaultValue="%" />
                        <asp:ControlParameter ControlID="txtSearch" DefaultValue="%" Name="?" 
                            PropertyName="Text" />
                    </SelectParameters>
                </asp:SqlDataSource>

The only thing I did for the paging to work is set paging = true. I limit each page to 10 results.

 

[ca8msm]

This is just one of the reasons why we advise against using the drag/drop DataSource controls; they simply don't give you the ability to interact easily with them.

Saying that, you could extract the data out of the control, convert it to a DataTable and the the total number of rows from the Rows property e.g.

Dim dv As New System.Data.DataView  
Dim dt As New System.Data.DataTable  
dv = SqlDataSource1.Select(DataSourceSelectArguments.Empty)  
dt = dv.ToTable()
Response.Write(dt.Rows)

but this is a very messy approach IMO.

Mark,

Darlington Web Design
Experts, Information, Ideas & Knowledge
ASP.NET Tips & Tricks

 

[jmeckley]

paging the data at the UI is not efficient. You will query all the records from the database, pass them over the wire and then bind all the records to the grid. 10 - 20 records will actually be displayed. but the grid is holding on too all of them to construct the paging.

a better approach is to page the data on the datbase by executing 2 queries.
1. get a specific page of results
2. get the total number of records.
3. build the UI.

How you page depends on your data access strategy. if you are writing the sql manually (stored procs, or raw sql) then you will need to research how to page data effectively. if you are using an ORM then paging is built in. since you are currently using the sqldatasource control I'll assume you are writing raw sql queries.

I believe the ListView control in conjunction with a Paging control will accommodate your needs better with this approach than a GridView. Bind the page of data to ListView and set the page index, rows per page and total number of rows on the Pageing control. I could be wrong as I don't work with webforms or server controls though.

If that doesn't work you can use a ListView and build the paging mechanism manually in the footer of the ListView control.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[tqeonline]

This is what I ended up doing.

<script runat="server">
    Protected Sub gvResults_DataBound(ByVal sender As Object, ByVal e As System.EventArgs)
        gvResults.FooterRow.Cells(0).Text = lblCount.Text
        gvResults.FooterRow.Cells(0).ColumnSpan = gvResults.Columns.Count
        gvResults.FooterRow.Cells(0).HorizontalAlign = HorizontalAlign.Center
    End Sub

    Protected Sub SqlDataSource1_Selected(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)
        lblCount.Text = "Results Found = " & e.AffectedRows.ToString
    End Sub
</script>