Solved

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

Posted on 2012-03-23
11
1,913 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

627 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