Record Count

I am trying to fill a span with the record count of my grid view.  It kinda of works.  But here is the thing.  I have added paging displaying 10 records per page. The record count is for each page.  So it keeps being 10, until the last page where there are 3 records and the record count is three.

I need the record count to reflect the whole recordset not just the records per page.  The simple code I am using at the mo is this

        int RecCount = GridView1.Rows.Count;
        string WrRecCount = RecCount.ToString();
        totalRecords.InnerHtml = WrRecCount;
LVL 20
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Elvio Lujan.Net Senior DeveloperCommented:
if you are filling the gridview with a dataset you can do the count from the dataset or datatable!
REA_ANDREWAuthor Commented:
Do you know the code to do this?
Elvio Lujan.Net Senior DeveloperCommented:
sure... can post the portion of code where you fill the gridview?
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

REA_ANDREWAuthor Commented:
<asp:GridView ID="GridView1"  DataSourceID="SqlDataSource1" runat=server />
<asp:SqlDataSource ID="SqlDataSource1" runat=server SelectCommand="SELECT [au_id],[au_lname],[au_fname],[phone],[address],[city],[state],[zip],[contract] FROM [authors]" ConnectionString="<%$ConnectionStrings:pubsConn1%>" />
Elvio Lujan.Net Senior DeveloperCommented:
in the SqlDataSourceStatusEventArgs is the property AffectedRows that have the number of rows
REA_ANDREWAuthor Commented:
I have read that but it does not work.
You can try executing a seperate SQL command to get the total count i.e. "Select Count(ID) from TABLE". Set the count equal to a variable and that will be your total number of records. True, this makes to DB calls, but you could also execute 2 selects with one command, but then you'd have to use a data adapter and dataset to access the two command results.
REA_ANDREWAuthor Commented:

I am aware that I can do it in SQL, but I am just flabbergasted that there is no straighforward way to count the number of records in a result generated by an SQL Statement.

I have searched the google for hours looking and for me that is too long for such a simple task, Or May Be no so simple.

Anyone else have any ideas? Or links of interest
What is the SQL statement you are currently executing?
REA_ANDREWAuthor Commented:
<asp:GridView ID="GridView1"  DataSourceID="SqlDataSource1" runat=server />
<asp:SqlDataSource ID="SqlDataSource1" runat=server SelectCommand="SELECT [au_id],[au_lname],[au_fname],[phone],[address],[city],[state],[zip],[contract] FROM [authors]" ConnectionString="<%$ConnectionStrings:pubsConn1%>" />
If you don't want to use second Select query then try this:

In your html gridview add:

and in your code behind:
    protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
        if (e.Row.RowType == DataControlRowType.DataRow)
        Context.Items["GridCount"] = Convert.ToInt32(Context.Items["GridCount"].ToString())+1;

in Page_Load event add:
Context.Items.Add("GridCount", 0);

So to run this I created a label and assiged a value to it, like this(in html view of the page):
<asp:Label ID="Label1" runat="server"><%= HttpContext.Current.Items["GridCount"].ToString() %></asp:Label>
Here is a code that will allow you to count all the records, by first making the gridview disabling paging, counting the records, setting the value to lbl1(label) then re-enabling paging.
        GridView1.AllowPaging = False
        lbl1.Text = GridView1.Rows.Count()
        GridView1.AllowPaging = True

C# (virtually identical):
        GridView1.AllowPaging = false;
        lbl1.Text = GridView1.Rows.Count();
        GridView1.AllowPaging = true;

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
I know this is closed but I have a followup question on the accepted solution. If i need to open an new question I will but this seems like the place to start.

I used this method (I am using the VB) and it really doesn't work for me. When  I try to change the page  i get the exception "The GridView 'MyGrid' fired event PageIndexChanging which wasn't handled."  MyGrid has  properties AllowPaging set to "True" and PageSize = "10". This problem still occurs even after I comment out the code lines to where I am not trying to change any paging property values, and have reloaded the page. In other words it is now completed broken. I have even closed the project & Visual Studio, reopened...nada will not work at all now.

 Also even though I think I am creating the label text BEFORE the paging properties are reset, the label only displays "20..." i.e. the page size, unless the number of returns is less of course.
Anyone know why and how to fix it?  thanks

With MyGrid
             .AllowPaging = False
            i = .Rows.Count()
            Label1.Text = i & " records returned"
            .AllowPaging = True
            .PageSize = 20 

        End With

Open in new window

Just wondering did you implement PageIndexChanging event for the grid?
aspx page...
<asp:GridView ID="MyGrid" OnPageIndexChanging="MyGrid_PageIndexChanging"  DataSourceID="SqlDataSource1" runat=server />

code behind...

Protected Sub MyGrid_PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
MyGrid.PageIndex = e.NewPageIndex
End Sub

OK that took care of the first part. However there are a few other issues here so I think it would be better to start a new question.
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.