locked
How to get number of records a query has returned using SqlDataSource control? RRS feed

  • Question

  • User-1001836498 posted

    Hi,

    I'm using SqlDataSource control.

    Is there a way to know how many records a query has returned?

    Thursday, May 17, 2007 7:01 AM

Answers

  • User-913230384 posted

    Try this one to get the total Rows Returned

     

     

    This is a simple trick about how you can get the total of rows returned from your SqlDataSource control’s SelectCommand query.

    <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>

    When you bind a data-source control to a GridView control and use paging etc, you can’t use the GridView’s Rows property to get the number of rows returned from your data-source control. The Rows property will only return the rows rendered by the GridView control. To get the total number of rows returned from the SelectCommand, you can hook up to the SqlDataSource’s Selected event. The Selected event’s SqlDataSourceStatusEventArgs event argument’s AffectedRows property will return the number of totals rows the SelectCommand returnes. The following example is a simple page with a GridView, SqlDataSource control and a label control. The lable control will be used to display the total number of rows the SelectComamand of the SqlDataSource control returns.

    <o:p></o:p> <o:p><%</o:p>@ Page Language="C#" AutoEventWireup="true" %> <o:p></o:p>

     

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

    <o:p> </o:p>

    <script runat="Server">

    <o:p> </o:p>

        protected void SqlDataSource1_Selected(object sender, SqlDataSourceStatusEventArgs e)

        {

            totalRows.Text = e.AffectedRows.ToString();

        }

    <o:p> </o:p>

    </script>

    <o:p> </o:p>

    <html xmlns="http://www.w3.org/1999/xhtml" >

    <head runat="server">

        <title>Untitled Page</title>

    </head>

    <body>

        <form id="form1" runat="server">

        <div>

            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="CustomerID"

                DataSourceID="SqlDataSource1" AllowPaging="True">

                <Columns>

                    <asp:BoundField DataField="CustomerID" HeaderText="CustomerID" ReadOnly="True" SortExpression="CustomerID" />

                    <asp:BoundField DataField="CompanyName" HeaderText="CompanyName" SortExpression="CompanyName" />

                    <asp:BoundField DataField="ContactName" HeaderText="ContactName" SortExpression="ContactName" />

                </Columns>

            </asp:GridView>

            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=(local);Initial Catalog=Northwind;Integrated Security=True"

                ProviderName="System.Data.SqlClient" SelectCommand="SELECT [CustomerID], [CompanyName], [ContactName] FROM [Customers]" OnSelected="SqlDataSource1_Selected">

            </asp:SqlDataSource>

    <o:p> </o:p>

            <asp:Label ID="Label1" runat="server" Text="Number of total rows:"></asp:Label>

            <asp:Label ID="totalRows" runat="server" Text="Label"></asp:Label>

       

        </div>

        </form>

    </body>

    </html>

     

    Hope this will help you

     

    Satya

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 18, 2007 7:28 AM

All replies

  • User-913230384 posted

    Try this one to get the total Rows Returned

     

     

    This is a simple trick about how you can get the total of rows returned from your SqlDataSource control’s SelectCommand query.

    <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>

    When you bind a data-source control to a GridView control and use paging etc, you can’t use the GridView’s Rows property to get the number of rows returned from your data-source control. The Rows property will only return the rows rendered by the GridView control. To get the total number of rows returned from the SelectCommand, you can hook up to the SqlDataSource’s Selected event. The Selected event’s SqlDataSourceStatusEventArgs event argument’s AffectedRows property will return the number of totals rows the SelectCommand returnes. The following example is a simple page with a GridView, SqlDataSource control and a label control. The lable control will be used to display the total number of rows the SelectComamand of the SqlDataSource control returns.

    <o:p></o:p> <o:p><%</o:p>@ Page Language="C#" AutoEventWireup="true" %> <o:p></o:p>

     

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

    <o:p> </o:p>

    <script runat="Server">

    <o:p> </o:p>

        protected void SqlDataSource1_Selected(object sender, SqlDataSourceStatusEventArgs e)

        {

            totalRows.Text = e.AffectedRows.ToString();

        }

    <o:p> </o:p>

    </script>

    <o:p> </o:p>

    <html xmlns="http://www.w3.org/1999/xhtml" >

    <head runat="server">

        <title>Untitled Page</title>

    </head>

    <body>

        <form id="form1" runat="server">

        <div>

            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="CustomerID"

                DataSourceID="SqlDataSource1" AllowPaging="True">

                <Columns>

                    <asp:BoundField DataField="CustomerID" HeaderText="CustomerID" ReadOnly="True" SortExpression="CustomerID" />

                    <asp:BoundField DataField="CompanyName" HeaderText="CompanyName" SortExpression="CompanyName" />

                    <asp:BoundField DataField="ContactName" HeaderText="ContactName" SortExpression="ContactName" />

                </Columns>

            </asp:GridView>

            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=(local);Initial Catalog=Northwind;Integrated Security=True"

                ProviderName="System.Data.SqlClient" SelectCommand="SELECT [CustomerID], [CompanyName], [ContactName] FROM [Customers]" OnSelected="SqlDataSource1_Selected">

            </asp:SqlDataSource>

    <o:p> </o:p>

            <asp:Label ID="Label1" runat="server" Text="Number of total rows:"></asp:Label>

            <asp:Label ID="totalRows" runat="server" Text="Label"></asp:Label>

       

        </div>

        </form>

    </body>

    </html>

     

    Hope this will help you

     

    Satya

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 18, 2007 7:28 AM
  • User324532216 posted

    Thanks, It works.

    Friday, July 3, 2009 3:43 AM
  • User1505363701 posted

    Thanx satya_tanwar, it works!

    Monday, January 10, 2011 1:40 AM