Solved

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

Posted on 2012-03-23
11
1,900 Views
Last Modified: 2012-03-27
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.
0
Comment
Question by:Codeaddict7423
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 6

Expert Comment

by:ingriT
ID: 37757608
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
 
LVL 13

Expert Comment

by:Rick
ID: 37757645
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
 

Author Comment

by:Codeaddict7423
ID: 37757740
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 38

Expert Comment

by:Tom Beck
ID: 37757751
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
 
LVL 6

Expert Comment

by:ingriT
ID: 37757783
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
 

Author Comment

by:Codeaddict7423
ID: 37757946
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
 
LVL 5

Expert Comment

by:Vishal Kedar
ID: 37770222
0
 
LVL 6

Expert Comment

by:ingriT
ID: 37770266
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
 

Author Comment

by:Codeaddict7423
ID: 37771392
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
 
LVL 6

Accepted Solution

by:
ingriT earned 500 total points
ID: 37771409
The value 1000000000 is probably to big for in Integer.

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

Author Closing Comment

by:Codeaddict7423
ID: 37771443
ingriT,
THANK YOU...
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question