Carla Romere
asked on
Filtering a GridView With Dropdowns
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:
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();
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>
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> </td>
<td> </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> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
</table>
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>
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?
If you are hitting the database every time, then why are you not including the filter condition in the query?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
sammySeltzer - I added that to my web.config and am still getting the same error.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That one did the trick. It's doing exactly what I'd hoped it would do now and is very fast.