Solved

Filtering a GridView With Dropdowns

Posted on 2014-01-16
6
365 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:Carla Romere
[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 29

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:Carla Romere
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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 

Author Comment

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

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:Carla Romere
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

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!

Question has a verified solution.

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

In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

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