This is a migrated thread and some comments may be shown as answers.

Grouping with Programmic Filtering

1 Answer 79 Views

Grid

This is a migrated thread and some comments may be shown as answers.

Annette asked on 01 Apr 2011, 08:09 PM

I've getting an error on the grouping of my grid. I get the "Column ? does not exist in table" however it's here. I have a form with several ways to filter the grid. I need to group the grid based on the first 4 columns.

Here is my aspx and code behind.

CODE BEHIND

protected void btnReport_Click(object sender, EventArgs e)

{

grdInventory.Visible = true;

grdInventory.Rebind();

}

protected void grdInventory_NeedDataSource(object sender, GridNeedDataSourceEventArgs e)

{

List<ProductInventoryList> pList = (List<ProductInventoryList>)Session["ShipmentActivity"];

#region

Selection

string selection = "";

if (ddlProduct.SelectedItem.Text != "")

{

selection += "Product = '" + ddlProduct.SelectedItem.Text + "'";

}

if (ddlLot.SelectedItem.Text != "")

{

if (selection != string.Empty)

{

selection += " AND ";

}

selection += Operator(ddlLotSearch.SelectedItem.Text, "Lot", ddlLot.SelectedItem.Text);

}

if (ddlProtocolID.SelectedItem.Text != "")

{

if (selection != string.Empty)

{

selection += " AND ";

}

selection += Operator(ddlProtocolSearch.SelectedItem.Text, "ProtocolID", ddlProtocolID.SelectedItem.Text);

}

if (ddlShipTo.SelectedItem.Text != "")

{

if (selection != string.Empty)

{

selection += " AND ";

}

selection += "ShipTo = '" + ddlShipTo.SelectedItem.Text+"'";

}

if (ddlStockNumber.SelectedItem.Text != "")

{

if (selection != string.Empty)

{

selection += " AND ";

}

selection += Operator(ddlStockSearch.SelectedItem.Text, "StockNumber", ddlStockNumber.SelectedItem.Text);

}

if (ddlShipNo.SelectedItem.Text !="")

{

if (selection != string.Empty)

{

selection += " AND ";

}

selection += Operator(ddlShipNo.SelectedItem.Text, "ShippmentNumber", ddlShipNo.SelectedItem.Text);

}

if (ddlClientShip.SelectedItem.Text != "")

{

if (selection != string.Empty)

{

selection += " AND ";

}

selection += Operator(ddlShipNo.SelectedItem.Text, "ClientOrderNumber", ddlShipNo.SelectedItem.Text);

}

if (dtStartDate.SelectedDate != null)

{

if (selection != string.Empty)

{

selection += " AND ";

}

selection += "ShipDate >= '" + dtStartDate.SelectedDate.Value.ToShortDateString() +"'";

}

if (dtEndDate.SelectedDate != null)

{

if (selection != string.Empty)

{

selection += " AND ";

}

selection += "ShipDate <= '" + dtEndDate.SelectedDate.Value.ToShortDateString() + "'";

}

#endregion

Selection

var query = from l in pList

select new ProductInventoryList

{

DrugName = l.DrugName.Trim(),

Organization = l.Organization.Trim(),

StockNumber = l.StockNumber.Trim(),

UnitsPerPackage = l.UnitsPerPackage.Trim(),

PackingUnit = l.PackingUnit.Trim(),

PackContainer = l.PackContainer.Trim(),

Lot = l.Lot.Trim(),

ProtocolID = l.ProtocolID.Trim(),

ShipmentNumber= l.ShipmentNumber,

ClientOrderNumber = l.ClientOrderNumber,

ShipTo = l.Organization,

ShipmentAddress = l.ShipmentAddress,

ShipmentAddress2 = l.ShipmentAddress2,

ShipCity= l.ShipCity,

ShipState= l.ShipState,

ShipZipCode = l.ShipZipCode,

ShipCountry = l.ShipCountry,

Investigator = l.Investigator,

QuantityShipped = l.QuantityShipped,

ShipmentDate = l.ShipmentDate

};

DataTable dt = new DataTable();

dt.Columns.Add("ShipmentNumber", typeof(string));

dt.Columns.Add("ShipDate", typeof(DateTime));

dt.Columns.Add("ProtocolID", typeof(string));

dt.Columns.Add("ClientShip", typeof(string));

dt.Columns.Add("ShipTo", typeof(string));

dt.Columns.Add("ShipAddress", typeof(string));

dt.Columns.Add("Product", typeof(string));

dt.Columns.Add("PackUnit", typeof(string));

dt.Columns.Add("Investigator", typeof(string));

dt.Columns.Add("StockNumber", typeof(string));

dt.Columns.Add("Lot", typeof(string));

dt.Columns.Add("QtyShipped", typeof(string));

foreach (var p in query)

{

DataRow row = dt.NewRow();

if (p.ShipmentNumber != "")

{

row["ShipmentNumber"] = p.ShipmentNumber;

}

if (p.ShipmentDate != null)

{

row["ShipDate"] = DateTime.Parse(p.ShipmentDate).Date;

}

if (p.ProtocolID == "")

{

row["ProtocolID"] = " ";

}

else

{

row["ProtocolID"] = p.ProtocolID;

}

row["ClientShip"] = p.ClientOrderNumber;

row["ShipTo"] = p.ShipTo;

row["ShipAddress"] = p.ShipmentAddress + "<br/>" + p.ShipmentAddress2 + "<br/>" + p.ShipCity + ", " + p.ShipState + " " + p.ShipZipCode + " " + p.ShipCountry;

row["Investigator"] = p.Investigator;

row["Product"] = p.DrugName;

row["PackUnit"] = p.UnitsPerPackage + " " + p.PackingUnit + " " + p.PackContainer;

row["StockNumber"] = p.StockNumber;

row["Lot"] = p.Lot;

row["QtyShipped"] = p.QuantityShipped;

dt.Rows.Add(row);

}

grdInventory.VirtualItemCount = dt.Rows.Count;

grdInventory.DataSource = dt.Select(selection);
}

ASPX

<telerik:RadScriptBlock ID="RadScriptBlock1" runat="server">

function RequestStarted(ajaxManager, eventArgs) {

if (eventArgs.get_eventTarget().indexOf("imgbtnExcel") != -1)

eventArgs.set_enableAjax(false);

}

script>

</telerik:RadScriptBlock>

<h1>Product Inventory</h1>

<br />

div class="maincontent">

div class="labels">

<asp:Label ID="lblProduct" runat="server" Text="Product:"></asp:Label>

div>

div class="textlabel">

<telerik:RadComboBox ID="ddlProduct" runat="server" EnableEmbeddedSkins="false"

Skin="DMID" onload="ddlProduct_Load" onprerender="ddlProduct_PreRender"

DataTextField="Description" Width="350px">

</telerik:RadComboBox>

div>

div class="labels">

<asp:Label ID="lblLot" runat="server" Text="Lot #:"></asp:Label>

div>

div class="textlabel">

<telerik:RadComboBox ID="ddlLotSearch" runat="server"

EnableEmbeddedSkins="false" Skin="DMID"

onprerender="ddlLotSearch_PreRender" onload="ddlLotSearch_Load">

</telerik:RadComboBox>

<telerik:RadComboBox ID="ddlLot" runat="server" EnableEmbeddedSkins="false"

Skin="DMID" onload="ddlLot_Load" onprerender="ddlLot_PreRender"

DataTextField="Lot">

</telerik:RadComboBox>

div>

div class="labels">

<asp:Label ID="lblProtocolID" runat="server" Text="Protocol #:"></asp:Label>

div>

div class="textlabel">

<telerik:RadComboBox ID="ddlProtocolSearch" runat="server"

EnableEmbeddedSkins="false" Skin="DMID"

onprerender="ddlProtocolSearch_PreRender" onload="ddlProtocolSearch_Load">

</telerik:RadComboBox>

<telerik:RadComboBox ID="ddlProtocolID" runat="server"

EnableEmbeddedSkins="false" Skin="DMID" onload="ddlProtocolID_Load"

onprerender="ddlProtocolID_PreRender" DataTextField="ProtocolID">

</telerik:RadComboBox>

div>

div class="labels">

<asp:Label ID="lblShipTo" runat="server" Text="Ship To:" ></asp:Label>

div>

div class="textlabel">

<telerik:RadComboBox ID="ddlShipTo" runat="server"

EnableEmbeddedSkins="false" Skin="DMID" onload="ddlShipTo_Load"

onprerender="ddlShipTo_PreRender" DataTextField="ShipTo" Width="250px">

</telerik:RadComboBox>

div>

div class="labels">

<asp:Label ID="lblStockNumber" runat="server" Text="FBS Stock Number:"></asp:Label>

div>

div class="textlabel">

<telerik:RadComboBox ID="ddlStockSearch" runat="server"

EnableEmbeddedSkins="false" Skin="DMID"

onprerender="ddlStockSearch_PreRender" onload="ddlStockSearch_Load">

</telerik:RadComboBox>

<telerik:RadComboBox ID="ddlStockNumber" runat="server"

EnableEmbeddedSkins="false" Skin="DMID" onload="ddlStockNumber_Load"

onprerender="ddlStockNumber_PreRender" DataTextField="StockNumber">

</telerik:RadComboBox>

div>

div class="labels">

<asp:Label ID="lblFBSShipNo" runat="server" Text="FBS Ship #:"></asp:Label>

div>

div class="textlabel">

<telerik:RadComboBox ID="ddlShipNoSearch" runat="server"

EnableEmbeddedSkins="false" Skin="DMID"

onprerender="ddlShipNoSearch_PreRender" onload="ddlShipNoSearch_Load">

</telerik:RadComboBox>

<telerik:RadComboBox ID="ddlShipNo" runat="server"

EnableEmbeddedSkins="False" Skin="DMID"

onprerender="ddlShipNo_PreRender" onload="ddlShipNo_Load" >

</telerik:RadComboBox>

div>

div class="labels">

<asp:Label ID="lblClientShip" runat="server" Text="Client Ship #:"></asp:Label>

div>

div class="textlabel">

<telerik:RadComboBox ID="ddlClientShip" runat="server" EnableEmbeddedSkins="false"

Skin="DMID" onload="ddlClientShip_Load" onprerender="ddlClientShip_PreRender"

DataTextField="ClientShip">

</telerik:RadComboBox>

div>

div class="labels">

<asp:Label ID="lblStartDate" runat="server" Text="Start Date:"></asp:Label>

div>

div class="textlabel">

<telerik:RadDatePicker ID="dtStartDate" runat="server" Skin="DMID" EnableEmbeddedSkins="False">

Calendar UseRowHeadersAsSelectors="False" UseColumnHeadersAsSelectors="False"

ShowRowHeaders="False"></Calendar>

DatePopupButton ImageUrl="" HoverImageUrl=""></DatePopupButton>

DateInput DisplayDateFormat="MM/dd/yyyy" DateFormat="MM/dd/yyyy" EnableEmbeddedSkins="False"></DateInput>

</telerik:RadDatePicker>

<asp:Label ID="lblEndDate" runat="server" Text="End Date:"></asp:Label>

<telerik:RadDatePicker ID="dtEndDate" runat="server" Skin="DMID" EnableEmbeddedSkins="False">

Calendar UseRowHeadersAsSelectors="False" UseColumnHeadersAsSelectors="False"

ShowRowHeaders="False"></Calendar>

DatePopupButton ImageUrl="" HoverImageUrl=""></DatePopupButton>

DateInput DisplayDateFormat="MM/dd/yyyy" DateFormat="MM/dd/yyyy" EnableEmbeddedSkins="False"></DateInput>

</telerik:RadDatePicker>

div>

p style="text-align: center">

<telerik:RadButton ID="btnReport" runat="server" Text="Generate Report"

EnableEmbeddedSkins="False" Skin="DMID" onclick="btnReport_Click">

</telerik:RadButton>

<telerik:RadGrid ID="grdInventory" runat="server" EnableEmbeddedSkins="False"

Skin="DMID" AllowPaging="True"

AutoGenerateColumns="False" CellSpacing="0"

ShowFooter="True" ShowStatusBar="True"

PageSize="20"

onneeddatasource="grdInventory_NeedDataSource"

GridLines="None" >

HeaderContextMenu CssClass="GridContextMenu GridContextMenu_Default" ></HeaderContextMenu>

</ExportSettings>

MasterTableView CommandItemDisplay="Top" UseAllDataFields="true" GroupLoadMode="Server" TableLayout="Fixed">

CommandItemSettings ShowAddNewRecordButton="False"

ShowExportToExcelButton="True" ShowRefreshButton="False">

CommandItemSettings>

<asp:ImageButton ID="imgbtnExcel" runat="server" ImageUrl="~/App_Themes/DMID/Images/Excel.gif" OnClick="imgbtnExcel_Click" />

CommandItemTemplate>

HeaderStyle HorizontalAlign="Center" Font-Bold="True"></HeaderStyle>

RowIndicatorColumn>

ExpandCollapseColumn FilterControlAltText="Filter ExpandColumn column">

ExpandCollapseColumn>

<telerik:GridBoundColumn DataField="ShipmentNumber"

FilterControlAltText="Filter ShipmentNumber column"

HeaderText="FBS Ship #"

UniqueName="ShipmentNumber" DataType="System.String"

SortExpression="ShipmentNumber" >

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="ShipDate"

FilterControlAltText="Filter ShipDate column"

HeaderText="Date"

UniqueName="ShipDate" DataType="System.DateTime" DataFormatString="{0:d}"

SortExpression="ShipDate" >

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="ProtocolID"

FilterControlAltText="Filter ProtocolID column"

HeaderText="Protocol #" SortExpression="ProtocolID"

UniqueName="ProtocolID" DataType="System.String" >

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="ClientShip"

SortExpression="ClientShip" HeaderText="Client Ship #"

UniqueName="ClientShip" DataType="System.String" >

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="ShipTo"

SortExpression="ShipTo"

HeaderText="Shipped To" ShowSortIcon="False"

UniqueName="ShipTo" DataType="System.String">

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="ShipAddress"

FilterControlAltText="Filter ShipAddress column" HeaderText="Ship To Address"

SortExpression="ShipAddress" UniqueName="ShipAddress">

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="Product"

SortExpression="Product"

FilterControlAltText="Filter Product column"

HeaderText="Product" ShowSortIcon="False"

UniqueName="Product">

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="PackUnit"

SortExpression="PackUnit"

FilterControlAltText="Filter PackUnit column"

HeaderText="Packaging Unit" ShowSortIcon="False"

UniqueName="PackUnit" >

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="Investigator"

FilterControlAltText="Filter Investigator column"

SortExpression="Investigator"

HeaderText="Investigator Name" ShowSortIcon="False"

UniqueName="Investigator">

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="StockNumber" SortExpression="StockNumber" HeaderText="FBS Stock #"

ShowSortIcon="False" UniqueName="StockNumber" DataType="System.String">

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="Lot" HeaderText="Lot #"

UniqueName="Lot" SortExpression="Lot" >

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="QtyShipped" SortExpression="QtyShipped"

HeaderText="Qty Shipped" UniqueName="QtyShipped" DataType="System.String">

</telerik:GridBoundColumn>

</Columns>

<telerik:GridGroupByExpression>

<telerik:GridGroupByField FieldName="ShipmentNumber" HeaderText="FBS Ship#"></telerik:GridGroupByField>

<telerik:GridGroupByField FieldName="ShipDate" HeaderText="Date" FormatString="{0:d}" ></telerik:GridGroupByField>

<telerik:GridGroupByField FieldName="ProtocolID" HeaderText="Protocol #" ></telerik:GridGroupByField>

<telerik:GridGroupByField FieldName="ClientShip" HeaderText="Client Ship #" ></telerik:GridGroupByField>

<telerik:GridGroupByField FieldName="ShipTo" HeaderText="Shipped To"></telerik:GridGroupByField>

</SelectFields>

<telerik:GridGroupByField FieldName="ShipmentNumber" SortOrder="Ascending" ></telerik:GridGroupByField>

<telerik:GridGroupByField FieldName="ShipDate" HeaderText="Date" ></telerik:GridGroupByField>

<telerik:GridGroupByField FieldName="ProtocolID" HeaderText="Protocol #"></telerik:GridGroupByField>

<telerik:GridGroupByField FieldName="ClientShip" HeaderText="Client Ship #" ></telerik:GridGroupByField>

<telerik:GridGroupByField FieldName="ShipTo" HeaderText="Shipped To" ></telerik:GridGroupByField>

</GroupByFields>

</telerik:GridGroupByExpression>

</GroupByExpressions>

EditFormSettings>

EditColumn FilterControlAltText="Filter EditCommandColumn column"

CancelImageUrl="Cancel.gif" EditImageUrl="Edit.gif" InsertImageUrl="Update.gif"

UpdateImageUrl="Update.gif"></EditColumn>

EditFormSettings>

PagerStyle AlwaysVisible="True" PageButtonCount="20"></PagerStyle>

MasterTableView>

<ClientSettings AllowGroupExpandCollapse="True" ReorderColumnsOnClient="True" AllowDragToGroup="True"

AllowColumnsReorder="True">

</ClientSettings>

FilterMenu EnableImageSprites="False"></FilterMenu>

</telerik:RadGrid>

telerik

:RadAjaxManager ID="RadAjaxManager1" runat="server"

DefaultLoadingPanelID="RadAjaxLoadingPanel1">

<telerik:AjaxSetting AjaxControlID="btnReport">

<telerik:AjaxUpdatedControl ControlID="grdInventory"

LoadingPanelID="RadAjaxLoadingPanel1" />

</UpdatedControls>

</telerik:AjaxSetting>

<telerik:AjaxSetting AjaxControlID="grdInventory">

<telerik:AjaxUpdatedControl ControlID="grdInventory"

LoadingPanelID="RadAjaxLoadingPanel1" />

</UpdatedControls>

</telerik:AjaxSetting>

</AjaxSettings>

</telerik:RadAjaxManager>

<telerik:RadAjaxLoadingPanel ID="RadAjaxLoadingPanel1" runat="server" Skin="Web20">

</telerik:RadAjaxLoadingPanel>

1 Answer, 1 is accepted

answered on 07 Apr 2011, 09:55 AM

Hello Annette,

Could you share the exact error that you get and when you get it? Does the error occurs on loading the page or when changing grouping or filtering?
I tried to run your site, but I will also need the code for ProductInventoryList data class in order to debug it.
Please also take a look at source code of this demo with filtering and grouping that is working correctly: http://demos.telerik.com/aspnet-ajax/grid/examples/groupby/groupfooter/defaultcs.aspx

Best wishes,
Vasil
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items