- Home
- LLBLGen Pro
- Bugs & Issues
Invalid column name [SlotName Desc]!?
Joined: 07-Jan-2006
Hi,
I've data bound a C1 grid control to an LLBLGenDataSource2 control, pointed the datasourse to an EntityCollection, and set paging on.
When I run the form all is fine, until I click on a column to sort it. On the first click there is no problem:
SELECT TOP 20 [QuestServer].[dbo].[vewAdminShowProjects].[CustomerAccountName],
...
[QuestServer].[dbo].[vewAdminShowProjects].[QuestionnaireWaveStatus] FROM [QuestServer].[dbo].[vewAdminShowProjects] ORDER BY
[QuestServer].[dbo].[vewAdminShowProjects].[SlotName] ASC
But if I click a second time I get:
SELECT TOP 20 [QuestServer].[dbo].[vewAdminShowProjects].[CustomerAccountName],
...
[QuestServer].[dbo].[vewAdminShowProjects].[QuestionnaireWaveStatus] FROM [QuestServer].[dbo].[vewAdminShowProjects] ORDER BY [SlotName Desc] ASC
and the associated error stating that [SlotName Desc] is unrecognised.
Is this a C1 grid related error or something in the LLBLGen generated code? Page code is shown below.
Libraries are todays download.
Cheers, Jason
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Surveys.aspx.cs" Inherits="Admin_Surveys" %>
<%@ Register assembly="DevExpress.Web.ASPxGridView.v7.3, Version=7.3.5.0, Culture=neutral, PublicKeyToken=9b171c9fd64da1d1" namespace="DevExpress.Web.ASPxGridView" tagprefix="dxwgv" %>
<%@ Register assembly="DevExpress.Web.ASPxEditors.v7.3, Version=7.3.5.0, Culture=neutral, PublicKeyToken=9b171c9fd64da1d1" namespace="DevExpress.Web.ASPxEditors" tagprefix="dxe" %>
<%@ Register assembly="C1.Web.C1WebGrid.2" namespace="C1.Web.C1WebGrid" tagprefix="C1WebGrid" %>
<%@ Register assembly="C1.Web.Command.2" namespace="C1.Web.Command" tagprefix="c1c" %>
<%@ Register assembly="SD.LLBLGen.Pro.ORMSupportClasses.NET20, Version=2.5.0.0, Culture=neutral, PublicKeyToken=ca73b74ba4e3ff27" namespace="SD.LLBLGen.Pro.ORMSupportClasses" tagprefix="llblgenpro" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
Grid:<br />
</div>
<C1WebGrid:C1WebGrid ID="C1WebGrid2" runat="server" AutoGenerateColumns="False"
DataMember="DefaultView" DataSourceID="_ProjectListDS"
AllowPaging="True" AllowSorting="True" BackColor="White" BorderColor="#CC9966"
BorderStyle="None" BorderWidth="1px" CellPadding="4" PageSize="20">
<FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />
<ItemStyle BackColor="White" ForeColor="#330099" />
<Columns>
<C1WebGrid:C1ButtonColumn ButtonType="PushButton" CommandName="Select"
Text="Select">
</C1WebGrid:C1ButtonColumn>
<C1WebGrid:C1BoundColumn DataField="CustomerAccountName"
HeaderText="CustomerAccountName" SortExpression="CustomerAccountName"
Visible="False">
</C1WebGrid:C1BoundColumn>
<C1WebGrid:C1BoundColumn DataField="ClientName" HeaderText="ClientName"
SortExpression="ClientName">
</C1WebGrid:C1BoundColumn>
<C1WebGrid:C1BoundColumn DataField="ClientId" HeaderText="ClientId"
SortExpression="ClientId" Visible="False">
</C1WebGrid:C1BoundColumn>
<C1WebGrid:C1BoundColumn DataField="ProjectName" HeaderText="ProjectName"
SortExpression="ProjectName">
</C1WebGrid:C1BoundColumn>
<C1WebGrid:C1BoundColumn DataField="QuestionnaireWaveId"
HeaderText="QuestionnaireWaveId" SortExpression="QuestionnaireWaveId"
Visible="False">
</C1WebGrid:C1BoundColumn>
<C1WebGrid:C1BoundColumn DataField="WaveReference" HeaderText="WaveReference"
SortExpression="WaveReference">
</C1WebGrid:C1BoundColumn>
<C1WebGrid:C1BoundColumn DataField="SlotName" HeaderText="SlotName"
SortExpression="SlotName">
</C1WebGrid:C1BoundColumn>
<C1WebGrid:C1BoundColumn DataField="SlotChargeType" HeaderText="SlotChargeType"
SortExpression="SlotChargeType">
</C1WebGrid:C1BoundColumn>
<C1WebGrid:C1BoundColumn DataField="IsTestSlot" HeaderText="IsTestSlot"
SortExpression="IsTestSlot">
</C1WebGrid:C1BoundColumn>
<C1WebGrid:C1BoundColumn DataField="SurveySlotId" HeaderText="SurveySlotId"
SortExpression="SurveySlotId" Visible="False">
</C1WebGrid:C1BoundColumn>
<C1WebGrid:C1BoundColumn DataField="QuestionnaireWaveStatus"
HeaderText="QuestionnaireWaveStatus" SortExpression="QuestionnaireWaveStatus">
</C1WebGrid:C1BoundColumn>
</Columns>
<PagerStyle BackColor="#FFFFCC" ForeColor="#330099" HorizontalAlign="Center"
Mode="NumericPages" NextPageText="next" PrevPageText="previous" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" />
<SelectedItemStyle BackColor="#FFCC66" ForeColor="#333399" />
</C1WebGrid:C1WebGrid>
<llblgenpro:LLBLGenProDataSource2 ID="_ProjectListDS" runat="server"
AdapterTypeName="QuestMetrics.Server.Manager.BO.DatabaseSpecific.DataAccessAdapter, QuestMetrics.Server.Manager.BODBSpecific"
DataContainerType="EntityCollection" EnablePaging="True"
EntityFactoryTypeName="QuestMetrics.Server.Manager.BO.FactoryClasses.VewAdminShowProjectsEntityFactory, QuestMetrics.Server.Manager.BO">
</llblgenpro:LLBLGenProDataSource2>
</form>
</body>
</html>
Error Message:
[SqlException (0x80131904): Invalid column name 'ClientName Desc'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +925466 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800118 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1932 System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +31 System.Data.SqlClient.SqlDataReader.get_MetaData() +62 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1005 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +122 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12 System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +7 SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) +253
[ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: Invalid column name 'ClientName Desc'.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.] SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) +447 SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, IEntityFactory2 entityFactory, IEntityCollection2 collectionToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo, Boolean allowDuplicates, IEntityFields2 fieldsUsedForQuery) +688 SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollectionInternal(IEntityCollection2 collectionToFill, IRelationPredicateBucket& filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize) +983 SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize) +162 SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, Int32 pageNumber, Int32 pageSize) +54 SD.LLBLGen.Pro.ORMSupportClasses.LLBLGenProDataSourceView2.ExecuteSelectEntityCollection(Int32 pageSize, Int32 pageNumber, DataSourceSelectArguments arguments) +645 SD.LLBLGen.Pro.ORMSupportClasses.LLBLGenProDataSourceView2.ExecuteSelect(DataSourceSelectArguments arguments) +289 System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17 System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149 System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70 C1.Web.C1WebGrid.C1WebGrid.DataBind(Boolean keepGroups) +142 C1.Web.C1WebGrid.C1WebGrid.DataBind() +39 System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82 System.Web.UI.WebControls.BaseDataBoundControl.OnPreRender(EventArgs e) +19 C1.Web.C1WebGrid.C1WebGrid.OnPreRender(EventArgs e) +132 System.Web.UI.Control.PreRenderRecursiveInternal() +86 System.Web.UI.Control.PreRenderRecursiveInternal() +170 System.Web.UI.Control.PreRenderRecursiveInternal() +170 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2041
Please check the following thread: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=10679
Joined: 07-Jan-2006
Walaa wrote:
Please check the foloowing thread: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=10679
Hmm, OK, I've checked that thread, so does this mean that the sort mechanism for the LLBLGenProDataSource2 is incompatible with the DataSource standard? As this is a standard feature of databound grids, is there a reason this can't be supported in the LLBLGenProDataSource2?
I've not started looking into this in depth to see why and where this occurs, and I thought I'd start by asking if it's been encountered before, so I would still like to know why this occurs. Is this a bug or bodge in C1's implementation of data binding, is it a missing feature in the LLBLGenProDataSource2, or is it another failing in the MS implementation of data handling?
Cheers, Jason
Hmm, OK, I've checked that thread, so does this mean that the sort mechanism for the LLBLGenProDataSource2 is incompatible with the DataSource standard? As this is a standard feature of databound grids, is there a reason this can't be supported in the LLBLGenProDataSource2?
Not true 
You can check it out using the default MS GridView.
JSobell wrote:
Walaa wrote:
Please check the foloowing thread: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=10679
Hmm, OK, I've checked that thread, so does this mean that the sort mechanism for the LLBLGenProDataSource2 is incompatible with the DataSource standard? As this is a standard feature of databound grids, is there a reason this can't be supported in the LLBLGenProDataSource2?
We just derive from DataSourceControl, as all datasourcecontrol classes do (also the ones that come with .NET). We just fill in the blanks and obey what's defined in the sparse documentation from MS. In fact, we had to reverse engineer the objectdatasource to get the details but it works just as these controls. WIth other grids there's no problem.
The thing is this: There's no real documentation about this. The problem is that the sort expression, which fields to sort on, are passed to DataSourceControl.ExecuteSelect() in the DataSourceSelectArguments (please look it up in the MSDN). That class has a property, SortExpression. This is a string, and has the format: fieldName DESC or fieldName ASC or fieldName
At least with all other grids out there. Apparently, ComponentOne thought it should be lowercase 'desc'. Microsoft didn't specify what it should be, as you can see in the MSDN documentation of the SortExpression property. I've complained to MS about this crappy documentation at least more than a year ago. Though nothing has been done. So we've to 'guess' what SortExpression is. Well, it's the info as described above. At least that's what we learned from our own research. What else can we do?
So componentone decided to send the sort operator suffix as lowercase. For the objectdatasource, this doesn't matter, as it sorts on the client via the dataview anyway, but with custom controls like ours, it can be a problem.
If we change our test to be lowercase aware, it will work I think, as it then will pickup the desc and strip it off. Sorting on fields in a webgrid is indeed that lame, but as fields with a space in their name aren't possible, this 'works', but I'd have done it differently, especially because it's better to have a list of fieldnames with a sort direction indicator passed in separately per field, so no string voodoo has to be done. Oh well... perhaps one day the asp.net team will learn....
Question I've a question though: did you ask ComponentOne about this too?
I've not started looking into this in depth to see why and where this occurs, and I thought I'd start by asking if it's been encountered before, so I would still like to know why this occurs. Is this a bug or bodge in C1's implementation of data binding, is it a missing feature in the LLBLGenProDataSource2, or is it another failing in the MS implementation of data handling? Cheers, Jason
(Your control is from componentone, not componentART, which the other thread was about).
First of all, ComponentOne isn't the highest quality of things, and they definitely should pass the suffix as uppercase. As componentone is one of the most poorest supporters of their own customers, I don't have the illusion that if you complain to componentone, they'll fix it for you on time. So I'll add code to support case-insensitive suffix comparison in the datasource controls so componentone's code will work with the datasourcecontrol as well.
Componentart is a different matter, those people have never heard of inheritance and did an if/else check on what kind of control was bound and if it wasn't in the list (they checked on .net native controls), it failed. However, the DataSourceControl base class is there to avoid this crap, the grid should just work with that type and be done with it.
I'll attach a new build of the runtime lib when I'm done.
Joined: 07-Jan-2006
Hi Franz,
I realised the other thread was ComponentArt, but thought something in there may apply...
No, I'm afraid I still get the same error.
[ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: Invalid column name 'SlotName Desc'.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.]
I've placed the file in the GAC, checked it is loading that file in debug mode (knowing how easy it is to lose track of versions in the GAC), restarted IIS, deleted every old DLL, and the error still occurs.
Which module performs this function? I couldn't see where in the ORMSupportClasses class this is being performed.
Cheers, Jason
JSobell wrote:
Hi Franz,
I realised the other thread was ComponentArt, but thought something in there may apply...
No, I'm afraid I still get the same error.
[ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: Invalid column name 'SlotName Desc'.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.]
I've placed the file in the GAC, checked it is loading that file in debug mode (knowing how easy it is to lose track of versions in the GAC), restarted IIS, deleted every old DLL, and the error still occurs.
Which module performs this function? I couldn't see where in the ORMSupportClasses class this is being performed.
Please don't put the runtime libs in the gac, it's not useful nor necessary. The new build I attached has build nr 2.5.08.0204
the code is in: LLBLGenProDataSourceClasses.cs, routine ProduceSortExpressionFromString, in LLBLGenProDataSourceViewBase, line 4277.
(all datasource classes are in the same file, as a lot of code had to be moved around in these classes during development as there was no documentation)
The code you have doesn't have the fix. Below the fixed version of the routine:
/// <summary>
/// Produces the sort expression from string.
/// </summary>
/// <param name="fieldsList">The fields list.</param>
/// <param name="sortargument">The sortargument.</param>
/// <returns></returns>
private ISortExpression ProduceSortExpressionFromString( Dictionary<string, IEntityFieldCore> fieldsList, string sortargument )
{
ISortExpression toReturn = new SortExpression();
string[] sortElements = sortargument.Split( ',' );
// we ignore [] around fields, as fields with spaces in their names aren't possible.
foreach( string sortElement in sortElements )
{
string fieldName = string.Empty;
SortOperator sortOp = SortOperator.Ascending;
if( sortElement.EndsWith(" DESC", StringComparison.InvariantCultureIgnoreCase) )
{
sortOp = SortOperator.Descending;
fieldName = sortElement.Substring(0, sortElement.Length-5).Trim();
}
else
{
if( sortElement.EndsWith(" ASC", StringComparison.InvariantCultureIgnoreCase))
{
fieldName = sortElement.Substring(0, sortElement.Length-4).Trim();
}
else
{
fieldName = sortElement.Trim();
}
}
IEntityFieldCore field = null;
if( fieldsList.TryGetValue( fieldName, out field ) )
{
IFieldPersistenceInfo persistenceInfo = null;
if(field is IEntityField)
{
persistenceInfo = (IFieldPersistenceInfo)field;
}
toReturn.Add( new SortClause( field, persistenceInfo, sortOp ) );
}
else
{
// Sort on EntityProperty. This means that we can still sort on this property, with the name 'fieldname'.
// If the fieldname isn't present in any entity and SortMode is set to server side, this will go wrong, but that's the responsibility
// of the developer.
if(_sortingMode == DataSourceSortingMode.ServerSide)
{
toReturn.Add(new SortClause(new EntityField2(fieldName, new Expression()), new FieldPersistenceInfo(), sortOp));
}
else
{
toReturn.Add(new SortClause(new EntityProperty(fieldName), null, sortOp));
}
}
}
return toReturn;
}
I'm sorry this is cumbersome to test, as we don't have the particular grid to reproduce the problem and all grids we have here work properly.
Now, re-re-reading the code, I can't imagine how it is possible that 'Desc' ends up being in the field name to sort on.