Solved

Filtering a GridView With Dropdowns

Posted on 2014-01-16
6
362 Views
Last Modified: 2014-01-17
I am building an inventory dashboard to simplify having many different inventory reports. I have a gridview and four dropdown windows. I have it set to load the full query on page load, and then filter the list with the four dropdowns. The page loads great. But the first time I choose any dropdown, I get the message: "Maximum request length exceeded."

This is set in web.config:       <httpRuntime maxRequestLength="1000000" />

This query returns approximately 20,000 rows and actually runs fairly quickly. But filtering it down causes the above error.

This is my pageload event:
            VerifyRenderingInServerForm(form1);
            GridView1.GridLines = GridLines.Both;
            SqlInventory.FilterExpression = "[WAREHOUSE] LIKE '{0}' AND [BIN_LOCATION] LIKE '{1}' AND [PART_CODE] LIKE '{2}' AND [IC_LOT_NUMBER] LIKE '{3}'";
            GridView1.DataBind();

Open in new window

My SQL datasource:
        <asp:SqlDataSource ID="SqlInventory" runat="server" 
            ConnectionString="<%$ ConnectionStrings:fin_pilotConnectionString %>" 
            SelectCommand="SELECT WAREHOUSE, BIN_LOCATION, PART_CODE, PART_DESC_1, IC_STATUS_CODE, IC_LOT_NUMBER, UNIT_OF_MEASURE, STANDARD_COST, IC_QTY_AVAILABLE, CURR_VALUE  FROM [VW_CURRENT_INVENTORY_ALL] ORDER BY [WAREHOUSE], [BIN_LOCATION], [PART_CODE]">
            <FilterParameters>
                <asp:ControlParameter ControlID="ddlWarehouse" Name="WAREHOUSE" PropertyName="SelectedValue" />
                <asp:ControlParameter ControlID="ddlBin" Name="BIN_LOCATION" PropertyName="SelectedValue" />
                <asp:ControlParameter ControlID="ddlPartCode" Name="PART_CODE" PropertyName="SelectedValue" />
                <asp:ControlParameter ControlID="ddlLotNbr" Name="IC_LOT_NUMBER" PropertyName="SelectedValue" />
                </FilterParameters>
        </asp:SqlDataSource>

Open in new window

My dropdowns:
    <table style="width:800px; border:0">
    <tr>
        <td style="font-family:Arial narrow; font-size: small; width:50px">WHSE</td>
        <td style="font-family:Arial narrow; font-size: small; width:75px">BIN</td>
        <td style="font-family:Arial narrow; font-size: small; width:100px">PART</td>
        <td style="font-family:Arial narrow; font-size: small; width:215px">DESCRIPTION</td>
        <td style="font-family:Arial narrow; font-size: small; width:50px">STATUS</td>
        <td style="font-family:Arial narrow; font-size: small; width:100px">LOT NBR</td>
        <td style="font-family:Arial narrow; font-size: small; width:50px">UOM</td>
        <td style="font-family:Arial narrow; font-size: small; width:60px">STD COST</td>
        <td style="font-family:Arial narrow; font-size: small; width:50px">QTY</td>
        <td style="font-family:Arial narrow; font-size: small; width:50px">VALUE</td>
    </tr>
    <tr>
        <td>
            <asp:DropDownList ID="ddlWarehouse" runat="server" AutoPostBack="True" 
                DataSourceID="SqlWarehouse" DataTextField="WAREHOUSE" 
                DataValueField="WAREHOUSE" AppendDataBoundItems="True">
                <asp:ListItem Selected="True" Value="%">All Warehouses</asp:ListItem>
            </asp:DropDownList>
        </td>
        <td>
            <asp:DropDownList ID="ddlBin" runat="server" AutoPostBack="True" 
                DataSourceID="SqlBin" DataTextField="BIN_LOCATION" 
                DataValueField="BIN_LOCATION" AppendDataBoundItems="True">
                <asp:ListItem Selected="True" Value="%">All Bins</asp:ListItem>
            </asp:DropDownList>
        </td>
        <td>
            <asp:DropDownList ID="ddlPartCode" runat="server" AutoPostBack="True" 
                DataSourceID="sqlParts" DataTextField="PART_CODE" 
                DataValueField="PART_CODE" AppendDataBoundItems="True">
                <asp:ListItem Selected="True" Value="%">All Parts</asp:ListItem>
            </asp:DropDownList>
        </td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
        <td>
            <asp:DropDownList ID="ddlLotNbr" runat="server" AutoPostBack="True" 
                AppendDataBoundItems="True" DataSourceID="SqlLot" 
                DataTextField="IC_LOT_NUMBER" DataValueField="IC_LOT_NUMBER">
                <asp:ListItem Selected="True" Value="%">All Lot Numbers</asp:ListItem>
            </asp:DropDownList>
        </td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
    </tr>
    </table>

Open in new window

And my Gridview:
        <asp:GridView ID="GridView1" runat="server" AllowSorting="True" CssClass="grid" AutoGenerateColumns="False" DataSourceID="SqlInventory" ShowHeader="false" Width="800px" >
            <Columns>
                <asp:BoundField DataField="WAREHOUSE" HeaderText="WHSE" SortExpression="WAREHOUSE" ShowHeader="False" >
                    <ItemStyle HorizontalAlign="Left" VerticalAlign="Top" Width="50px" Wrap="False" />
                </asp:BoundField>

                <asp:BoundField DataField="BIN_LOCATION" HeaderText="BIN" SortExpression="BIN_LOCATION"  >
                    <ItemStyle HorizontalAlign="Left" VerticalAlign="Top" Width="75px" Wrap="False" />
                </asp:BoundField>

                <asp:BoundField DataField="PART_CODE" HeaderText="PART_CODE" SortExpression="PART_CODE"  >
                    <ItemStyle HorizontalAlign="Left" VerticalAlign="Top" Width="100px" Wrap="False" />
                </asp:BoundField>

                <asp:BoundField DataField="PART_DESC_1" HeaderText="PART_DESC_1" SortExpression="PART_DESC_1" >
                    <ItemStyle HorizontalAlign="Left" VerticalAlign="Top" Width="215px" Wrap="False" />
                </asp:BoundField>

                <asp:BoundField DataField="IC_STATUS_CODE" HeaderText="STATUS" SortExpression="IC_STATUS_CODE"  >
                    <ItemStyle HorizontalAlign="Left" VerticalAlign="Top" Width="50px" Wrap="False" />
                </asp:BoundField>

                <asp:BoundField DataField="IC_LOT_NUMBER" HeaderText="LOT_NBR" SortExpression="IC_LOT_NUMBER"  >
                    <ItemStyle HorizontalAlign="Left" VerticalAlign="Top" Width="100px" Wrap="False" />
                </asp:BoundField>

                <asp:BoundField DataField="UNIT_OF_MEASURE" HeaderText="UOM" SortExpression="UNIT_OF_MEASURE"  >
                    <ItemStyle HorizontalAlign="Left" VerticalAlign="Top" Width="50px" Wrap="False" />
                </asp:BoundField>

                <asp:BoundField DataField="STANDARD_COST" HeaderText="STD_COST" SortExpression="STANDARD_COST"  >
                    <ItemStyle HorizontalAlign="Left" VerticalAlign="Top" Width="60px" Wrap="False" />
                </asp:BoundField>

                <asp:BoundField DataField="IC_QTY_AVAILABLE" HeaderText="QTY_AVL" SortExpression="IC_QTY_AVAILABLE"  >
                    <ItemStyle HorizontalAlign="Left" VerticalAlign="Top" Width="50px" Wrap="False" />
                </asp:BoundField>

                <asp:BoundField DataField="CURR_VALUE" HeaderText="CURR_VALUE" SortExpression="CURR_VALUE"  >
                    <ItemStyle HorizontalAlign="Left" VerticalAlign="Top" Width="50px" Wrap="False" />
                </asp:BoundField>
            </Columns>
        </asp:GridView>

Open in new window

My goal is to load the entire inventory and then let the users filter it by any combination of the four dropdowns. How do I get around the error?
0
Comment
Question by:Hers2keep
[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
  • 3
  • 2
6 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 39788550
If you are hitting the database every time, then why are you not including the filter condition in the query?
0
 
LVL 28

Assisted Solution

by:sammySeltzer
sammySeltzer earned 500 total points
ID: 39788562
If your version of iis is iis7 or later, then add this to your web.config file:

 <system.webServer>
   <security>
      <requestFiltering>
         <requestLimits maxAllowedContentLength="1073741824" />
      </requestFiltering>
   </security>
 </system.webServer

Open in new window

>

and see if it helps
0
 

Author Comment

by:Hers2keep
ID: 39788564
Maybe I'm misunderstanding how the filter by gridview works using the FilterExpression. I thought it would just filter the data that is already IN the gridview. I was hoping to not have to hit the database everytime. That is why I had it load the full current inventory when the page opens. All warehouse, all bins, all parts, etc. Then I wanted them to be able to use the drop downs to view only the data they want to see.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Hers2keep
ID: 39788575
sammySeltzer - I added that to my web.config and am still getting the same error.
0
 
LVL 28

Accepted Solution

by:
sammySeltzer earned 500 total points
ID: 39788639
Hmm, I don't understand this.

As well know,  .NET limits the maximum data sent in each request to 4096 KB (4MB) by default.

So, to resolve the issue, what you tried and what I sent should have done the trick.

Ok try this:

 <configuration>
   <system.web>
   <httpRuntime maxRequestLength="51200"
   enable = "True"
   executionTimeout="45"/>
   </system.web>
 </configuration>

Open in new window


You can increase the length to your original size but try this first.
0
 

Author Closing Comment

by:Hers2keep
ID: 39788675
That one did the trick. It's doing exactly what I'd hoped it would do now and is very fast.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

In this Article, I will provide a few tips in problem and solution manner. Opening an ASPX page in Visual studio 2003 is very slow. To make it fast, please do follow below steps:   Open the Solution/Project. Right click the ASPX file to b…
Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

735 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