Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2012-03-23
11
Medium Priority
?
1,948 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
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

963 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