asp.net c# dropdownlist choice makes gridview display selected number of records

Hello,

I have a page where a user searches a database based on a stored procedure. The stored procedure populates a gridview.  I'm attempting to have a dropdownlist control where I can offer users the choice of displaying "25", "50", "100" records or "display all".

After the search is complete and the gridview displays the total number of records, I would like to offer users the ability to see only 25, 50, 100 or all records.

The gridview should also offer pagination in the footer...

ANY HELP with this would be greatly appreciated.
Codeaddict7423Asked:
Who is Participating?

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

x
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.

ingriTCommented:
use the asp:datapager control
and then programmatically set the PageSize property for this pager when the drop box is changed.

See here for samples and more properties: http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.datapager.aspx

and use an asp:Listview for your items and an asp:DataSource for your query.
RickCommented:
If you want the user to see only 25, 50, 100 or all records you could use:

   "select top 25 ... from ..." or
   "select top 50 ... from ...",
   etc.
Codeaddict7423Author Commented:
ingriT,

Thank you for the quick reply.  I read your suggested website for a solution; however, I'm still in the dark.  

Can you provide a working example?
My *.apx page code follows:
-------------------
  <asp:Panel ID="pnl_gvWarrantSearchResults" runat="server" Visible="false">
                        <asp:SqlDataSource ID="ds_warrantsearchresults" runat="server" ConnectionString="<%$ ConnectionStrings:Jailinfo_JAUConnectionString1 %>"
                            SelectCommand="sp_getAllWarrantsbyName" SelectCommandType="StoredProcedure">
                            <SelectParameters>
                                <asp:ControlParameter ControlID="FirstNameTextBox" Name="Firstname" PropertyName="Text"
                                    Type="String" Size="50" ConvertEmptyStringToNull="false" />
                                <asp:ControlParameter ControlID="LastNameTextBox" Name="Lastname" PropertyName="Text"
                                    Type="String" ConvertEmptyStringToNull="false" />
                                <asp:ControlParameter ControlID="SpnTextBox" Name="Spn" PropertyName="Text" Type="String"
                                    ConvertEmptyStringToNull="false" />
                            </SelectParameters>
                        </asp:SqlDataSource>
                        <asp:GridView ID="gv_warrantsearchresults" runat="server" DataKeyNames="Spn"
                            AllowSorting="True" DataSourceID="ds_warrantsearchresults" CellPadding="4" Font-Names="Arial"
                            Font-Size="Small" ForeColor="#0033CC" GridLines="None" Width="580px" EmptyDataText="NO Matching Data Found"
                            OnSelectedIndexChanged="gv_warrantsearchresults_SelectedIndexChanged" PageSize="15"
                            ShowFooter="True">
                            <%-- <HeaderStyle CssClass="GridHeader" /> --%>
                            <RowStyle BackColor="#EFF3FB" />
                            <EmptyDataRowStyle Font-Names="Arial" Font-Size="Small" ForeColor="Red" />
                            <Columns>
                                <asp:CommandField ShowSelectButton="True" />
                               
                            </Columns>
                           
                           
                            <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" Font-Names="Arial"
                                Font-Size="X-Small" />
                            <PagerSettings Mode="NumericFirstLast" />
                            <PagerStyle BackColor="#507CD1" ForeColor="White" HorizontalAlign="center" />
                            <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
                            <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" HorizontalAlign="Left" />
                            <EditRowStyle BackColor="#2461BF" />
                            <AlternatingRowStyle BackColor="White" HorizontalAlign="Left" VerticalAlign="Top" />
                        </asp:GridView>
--------------------

The stored procedure that drives the search follows:
-------------------
USE [Jailinfo_JAU]
GO
/****** Object:  StoredProcedure [dbo].[sp_getAllWarrantsbyName]    Script Date: 03/22/2012 14:09:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            <Author,,Name>
-- Create date: <Create Date,,>
-- Description:      <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_getAllWarrantsbyName]

      -- Add the parameters for the stored procedure here
@Firstname varchar(50),
@Lastname varchar(50),
@Spn varchar(18)

AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.

      SET NOCOUNT ON;

    --Firstname,Lastname,Spn, SONumber

SELECT   distinct Spn,Firstname as First,Lastname as Last,SONumber
                 
      FROM [Jims_Mis_Warrants]
     
      WHERE [Firstname] like  
      CASE WHEN ISNULL(@Firstname,'') = '' THEN [Firstname] ELSE  '%' + @Firstname  + '%' END
      AND [Lastname] like  
      CASE WHEN ISNULL(@Lastname,'') = '' THEN [Lastname] ELSE '%' + @Lastname + '%' END
      AND [Spn] like
      CASE WHEN ISNULL(@Spn ,'')= '' THEN [Spn] ELSE '%' + @Spn + '%' END
--        AND [SONumber] like
--        CASE WHEN ISNULL (@SONumber ,'')= '' THEN [SONumber] ELSE '%' + @SONumber + '%' END


     
      ORDER BY [Lastname], [Firstname], [Spn], [SONumber]

END
---------------

When the user run an initial search and the gridview displays the results, I would like to offer a dropdown control to limit the number of rows displayed on the gridview.

ANY help with this would be greatly appreciated.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Tom BeckCommented:
Setting up paging in a GridView is just a matter of adding  AllowPaging="true" to the markup. then you could porgramatically set the number of records per page based on the dropdown list selection by setting GridView1.PageSize  = 50; for example.
ingriTCommented:
Try this on your *.aspx page
    <asp:Panel ID="pnl_gvWarrantSearchResults" runat="server" Visible="false">
        <asp:SqlDataSource ID="ds_warrantsearchresults" runat="server" ConnectionString="<%$ ConnectionStrings:Jailinfo_JAUConnectionString1 %>"
            SelectCommand="sp_getAllWarrantsbyName" SelectCommandType="StoredProcedure">
            <SelectParameters>
                <asp:ControlParameter ControlID="FirstNameTextBox" Name="Firstname" PropertyName="Text"
                    Type="String" Size="50" ConvertEmptyStringToNull="false" />
                <asp:ControlParameter ControlID="LastNameTextBox" Name="Lastname" PropertyName="Text"
                    Type="String" ConvertEmptyStringToNull="false" />
                <asp:ControlParameter ControlID="SpnTextBox" Name="Spn" PropertyName="Text" Type="String"
                    ConvertEmptyStringToNull="false" />
            </SelectParameters>
        </asp:SqlDataSource>
        <asp:DropDownList ID="ddl_warrantsearchresults" runat="server" OnSelectedIndexChanged="ddl_warrantsearchresults_changed">
            <asp:ListItem Text="25" Value="25" />
            <asp:ListItem Text="50" Value="50" />
            <asp:ListItem Text="100" Value="100" />
            <asp:ListItem Text="all" Value="all" />
        </asp:DropDownList>            
        <asp:GridView ID="gv_warrantsearchresults" runat="server" DataKeyNames="Spn" AllowSorting="True"
            DataSourceID="ds_warrantsearchresults" CellPadding="4" Font-Names="Arial" Font-Size="Small"
            ForeColor="#0033CC" GridLines="None" Width="580px" EmptyDataText="NO Matching Data Found"
            OnSelectedIndexChanged="gv_warrantsearchresults_SelectedIndexChanged" PageSize="15"
            ShowFooter="True">
            <%-- <HeaderStyle CssClass="GridHeader" /> --%>
            <RowStyle BackColor="#EFF3FB" />
            <EmptyDataRowStyle Font-Names="Arial" Font-Size="Small" ForeColor="Red" />
            <Columns>
                <asp:CommandField ShowSelectButton="True" />
            </Columns>
            <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" Font-Names="Arial"
                Font-Size="X-Small" />
            <PagerSettings Mode="NumericFirstLast" />
            <PagerStyle BackColor="#507CD1" ForeColor="White" HorizontalAlign="center" />
            <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
            <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" HorizontalAlign="Left" />
            <EditRowStyle BackColor="#2461BF" />
            <AlternatingRowStyle BackColor="White" HorizontalAlign="Left" VerticalAlign="Top" />
        </asp:GridView>
        <asp:DataPager ID="dp_warrantsearchresults" PagedControlID="gv_warrantsearchresults"
            PageSize="25" runat="server" />

Open in new window


And add this method to you *.aspx.cs code:
protected void ddl_warrantsearchresults_changed(object sender, EventArgs e)
        {
            string selectedValue = ddl_warrantsearchresults.SelectedValue;
            if (selectedValue.Equals("all"))
            {
                dp_warrantsearchresults.PageSize = 1000000000;
            }
            else
            {
                dp_warrantsearchresults.PageSize = Convert.ToInt32(selectedValue);
            }
        }

Open in new window


I'm doing this without testing, so you might need to do a dp_warrantsearchresults.DataBind() or a gv_warrantsearchresults.DataBind() in the method after you set the new PageSize.
Codeaddict7423Author Commented:
ingriT,

Thank you for your help.  I copied your code suggestions and received this error message:
------------
Control 'gv_warrantsearchresults' does not implement IPageableItemContainer.

------------

Below, please find my *.aspx code:
------------
 <div class="warrantsearchcontenttext">
                   
                    <div style="overflow-y: scroll; height: 450px; width: 580px;">
                        <asp:Panel ID="pnl_gvWarrantSearchResults" runat="server" Visible="false">
        <asp:SqlDataSource ID="ds_warrantsearchresults" runat="server" ConnectionString="<%$ ConnectionStrings:Jailinfo_JAUConnectionString1 %>"
            SelectCommand="sp_getAllWarrantsbyName" SelectCommandType="StoredProcedure">
            <SelectParameters>
                <asp:ControlParameter ControlID="FirstNameTextBox" Name="Firstname" PropertyName="Text"
                    Type="String" Size="50" ConvertEmptyStringToNull="false" />
                <asp:ControlParameter ControlID="LastNameTextBox" Name="Lastname" PropertyName="Text"
                    Type="String" ConvertEmptyStringToNull="false" />
                <asp:ControlParameter ControlID="SpnTextBox" Name="Spn" PropertyName="Text" Type="String"
                    ConvertEmptyStringToNull="false" />
            </SelectParameters>
        </asp:SqlDataSource>
        <asp:DropDownList ID="ddl_warrantsearchresults" runat="server" OnSelectedIndexChanged="ddl_warrantsearchresults_changed">
            <asp:ListItem Text="25" Value="25" />
            <asp:ListItem Text="50" Value="50" />
            <asp:ListItem Text="100" Value="100" />
            <asp:ListItem Text="all" Value="all" />
        </asp:DropDownList>            
        <asp:GridView ID="gv_warrantsearchresults" runat="server" DataKeyNames="Spn" AllowSorting="True"
            DataSourceID="ds_warrantsearchresults" CellPadding="4" Font-Names="Arial" Font-Size="Small"
            ForeColor="#0033CC" GridLines="None" Width="580px" EmptyDataText="NO Matching Data Found"
            OnSelectedIndexChanged="gv_warrantsearchresults_SelectedIndexChanged" PageSize="15"
            ShowFooter="True">
            <%-- <HeaderStyle CssClass="GridHeader" /> --%>
            <RowStyle BackColor="#EFF3FB" />
            <EmptyDataRowStyle Font-Names="Arial" Font-Size="Small" ForeColor="Red" />
            <Columns>
                <asp:CommandField ShowSelectButton="True" />
            </Columns>
            <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" Font-Names="Arial"
                Font-Size="X-Small" />
            <PagerSettings Mode="NumericFirstLast" />
            <PagerStyle BackColor="#507CD1" ForeColor="White" HorizontalAlign="center" />
            <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
            <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" HorizontalAlign="Left" />
            <EditRowStyle BackColor="#2461BF" />
            <AlternatingRowStyle BackColor="White" HorizontalAlign="Left" VerticalAlign="Top" />
        </asp:GridView>
        <asp:DataPager ID="dp_warrantsearchresults" PagedControlID="gv_warrantsearchresults"
            PageSize="25" runat="server" />

                           
                            <img src="images/spacer.gif" width="600px" height="1px" alt="" /></asp:Panel>
                    </div>
                </div>
---------------

Below, please find my codebehind code:
---------------

        protected void ddl_warrantsearchresults_changed(object sender, EventArgs e)
        {
            string selectedValue = ddl_warrantsearchresults.SelectedValue;
            if (selectedValue.Equals("all"))
            {
                dp_warrantsearchresults.PageSize = 1000000000;
            }
            else
            {
                dp_warrantsearchresults.PageSize = Convert.ToInt32(selectedValue);
            }
        }

-----------------------


ANY help would be most appreciated.
Vishal KedarCommented:
ingriTCommented:
The link provided by vishal_114 looks like SPAM to me. But try the solution TommyBoy provided;
Setting up paging in a GridView is just a matter of adding  AllowPaging="true" to the markup. then you could porgramatically set the number of records per page based on the dropdown list selection by setting GridView1.PageSize  = 50; for example.

Your *.aspx page would look like this;
<asp:Panel ID="pnl_gvWarrantSearchResults" runat="server" Visible="false">
        <asp:SqlDataSource ID="ds_warrantsearchresults" runat="server" ConnectionString="<%$ ConnectionStrings:Jailinfo_JAUConnectionString1 %>"
            SelectCommand="sp_getAllWarrantsbyName" SelectCommandType="StoredProcedure">
            <SelectParameters>
                <asp:ControlParameter ControlID="FirstNameTextBox" Name="Firstname" PropertyName="Text"
                    Type="String" Size="50" ConvertEmptyStringToNull="false" />
                <asp:ControlParameter ControlID="LastNameTextBox" Name="Lastname" PropertyName="Text"
                    Type="String" ConvertEmptyStringToNull="false" />
                <asp:ControlParameter ControlID="SpnTextBox" Name="Spn" PropertyName="Text" Type="String"
                    ConvertEmptyStringToNull="false" />
            </SelectParameters>
        </asp:SqlDataSource>
        <asp:DropDownList ID="ddl_warrantsearchresults" runat="server" OnSelectedIndexChanged="ddl_warrantsearchresults_changed"
        AutoPostBack="true">
            <asp:ListItem Text="25" Value="25" />
            <asp:ListItem Text="50" Value="50" />
            <asp:ListItem Text="100" Value="100" />
            <asp:ListItem Text="all" Value="all" />
        </asp:DropDownList>            
        <asp:GridView ID="gv_warrantsearchresults" runat="server" DataKeyNames="Spn" AllowSorting="True"
            DataSourceID="ds_warrantsearchresults" CellPadding="4" Font-Names="Arial" Font-Size="Small"
            ForeColor="#0033CC" GridLines="None" Width="580px" EmptyDataText="NO Matching Data Found"
            OnSelectedIndexChanged="gv_warrantsearchresults_SelectedIndexChanged" PageSize="15"
            ShowFooter="True" AllowPaging="true">
            <%-- <HeaderStyle CssClass="GridHeader" /> --%>
            <RowStyle BackColor="#EFF3FB" />
            <EmptyDataRowStyle Font-Names="Arial" Font-Size="Small" ForeColor="Red" />
            <Columns>
                <asp:CommandField ShowSelectButton="True" />
            </Columns>
            <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" Font-Names="Arial"
                Font-Size="X-Small" />
            <PagerSettings Mode="NumericFirstLast" />
            <PagerStyle BackColor="#507CD1" ForeColor="White" HorizontalAlign="center" />
            <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
            <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" HorizontalAlign="Left" />
            <EditRowStyle BackColor="#2461BF" />
            <AlternatingRowStyle BackColor="White" HorizontalAlign="Left" VerticalAlign="Top" />
        </asp:GridView>

Open in new window


And the .aspx.cs file cpntains this:
protected void ddl_warrantsearchresults_changed(object sender, EventArgs e)
    {
        string selectedValue = ddl_warrantsearchresults.SelectedValue;
        if (selectedValue.Equals("all"))
        {
            gv_warrantsearchresults.PageSize = 1000000000;
        }
        else
        {
            gv_warrantsearchresults.PageSize = Convert.ToInt32(selectedValue);
        }
    }

Open in new window

Codeaddict7423Author Commented:
ingriT,
Your code worked great. I am getting the following exception thrown when I select the 'all' option in the dropdownlist.
the message is as follows:
---------------------
Exception of type 'System.OutOfMemoryException' was thrown.
--------------------
the stack trace follows:
---------------------
[OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown.]
   System.Collections.ArrayList.set_Capacity(Int32 value) +41
   System.Web.UI.WebControls.GridView.CreateChildControls(IEnumerable dataSource, Boolean dataBinding) +705
   System.Web.UI.WebControls.CompositeDataBoundControl.PerformDataBinding(IEnumerable data) +57
   System.Web.UI.WebControls.GridView.PerformDataBinding(IEnumerable data) +14
   System.Web.UI.WebControls.DataBoundControl.OnDataSourceViewSelectCallback(IEnumerable data) +114
   System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +31
   System.Web.UI.WebControls.DataBoundControl.PerformSelect() +142
   System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +73
   System.Web.UI.WebControls.GridView.DataBind() +4
   System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
   System.Web.UI.WebControls.BaseDataBoundControl.OnPreRender(EventArgs e) +22
   System.Web.UI.WebControls.GridView.OnPreRender(EventArgs e) +17
   System.Web.UI.Control.PreRenderRecursiveInternal() +80
   System.Web.UI.Control.PreRenderRecursiveInternal() +171
   System.Web.UI.Control.PreRenderRecursiveInternal() +171
   System.Web.UI.Control.PreRenderRecursiveInternal() +171
   System.Web.UI.Control.PreRenderRecursiveInternal() +171
   System.Web.UI.Control.PreRenderRecursiveInternal() +171
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +842
-----------------


ANY light  you could shed on this would be great.
ingriTCommented:
The value 1000000000 is probably to big for in Integer.

Try setting it to 1000 (or how many your total could be).

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
Codeaddict7423Author Commented:
ingriT,
THANK YOU...
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
ASP.NET

From novice to tech pro — start learning today.