Solved

Filtering a GridView With Dropdowns

Posted on 2014-01-16
6
360 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
  • 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
DLL in ASP.NET 20 56
Duplicate a row 2 46
Class object 2 26
Name space syntax error 12 39
This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

939 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now