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?
 
ingriTConnect With a Mentor Commented:
The value 1000000000 is probably to big for in Integer.

Try setting it to 1000 (or how many your total could be).
0
 
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.
0
 
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.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
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.
0
 
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.
0
 
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.
0
 
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.
0
 
Vishal KedarCommented:
0
 
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

0
 
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.
0
 
Codeaddict7423Author Commented:
ingriT,
THANK YOU...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.