- Home
- LLBLGen Pro
- Bugs & Issues
Error in SQL generated during sort and GetMultiElementCollectionVia method
Joined: 27-Mar-2006
I have 3 entities Webpage ---< Webpage_Element >-- Element
Webpage has WebPageID as PK Element has ElementID as PK WebPage_Element links the 2, allowing a webpage to have many Elements and allowing Elements to appear on multiple Webpages
I want a list of Elements ordered by Webpage_Element.Disp_Order
The last line of this code:
Dim elements As New ElementCollection
Dim sorter As ISortExpression = New SortExpression( _
New SortClause(WebpageElementFields.DisplayOrder, SortOperator.Ascending))
wp.SetCollectionParametersElementCollectionViaWebpageElement(0, sorter)
elements = wp.GetMultiElementCollectionViaWebpageElement(True)
errors with:
[OracleException: ORA-00904: invalid column name]
Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure) +303
Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src) +45
Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior) +3701
Oracle.DataAccess.Client.OracleCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +35
SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) +253
[ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: ORA-00904: invalid column name. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.]
SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) +406
SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, ITransaction containingTransaction, IEntityCollection collectionToFill, Boolean allowDuplicates, IEntityFields fieldsUsedForQuery) +697
SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.PerformGetMultiAction(ITransaction containingTransaction, IEntityCollection collectionToFill, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPredicate selectFilter, IRelationCollection relations, Int32 pageNumber, Int32 pageSize) +416
SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetMulti(ITransaction containingTransaction, IEntityCollection collectionToFill, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IEntityFactory entityFactoryToUse, IPredicate selectFilter, IRelationCollection relations, Int32 pageNumber, Int32 pageSize) +68
SD.LLBLGen.Pro.ORMSupportClasses.EntityCollectionBase`1.GetMulti(IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relations, Int32 pageNumber, Int32 pageSize) +194
SD.LLBLGen.Pro.ORMSupportClasses.EntityCollectionBase`1.GetMulti(IPredicate selectFilter, IRelationCollection relations) +84
Fluor.GD.DAL.EntityClasses.WebpageEntity.GetMultiElementCollectionViaWebpageElement(Boolean forceFetch, IEntityFactory entityFactoryToUse) in D:\Data\vss\Gradop\GradOpSolution\DAL\EntityClasses\WebpageEntity.vb:471
Fluor.GD.DAL.EntityClasses.WebpageEntity.GetMultiElementCollectionViaWebpageElement(Boolean forceFetch) in D:\Data\vss\Gradop\GradOpSolution\DAL\EntityClasses\WebpageEntity.vb:448
main.AddElementsToPage(WebpageEntity wp) in D:\Data\vss\Gradop\GradOpSolution\GradOp\main.aspx.vb:66
main.BuildPage() in D:\Data\vss\Gradop\GradOpSolution\GradOp\main.aspx.vb:31
main.Page_Init(Object sender, EventArgs e) in D:\Data\vss\Gradop\GradOpSolution\GradOp\main.aspx.vb:17
System.Web.UI.Control.OnInit(EventArgs e) +2072044
System.Web.UI.Page.OnInit(EventArgs e) +9
System.Web.UI.Control.InitRecursive(Control namingContainer) +321
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +692
Method Exit: CreateSelectDQ
Method Enter: DaoBase.ExecuteSingleRowRetrievalQuery
Method Exit: DaoBase.ExecuteSingleRowRetrievalQuery
Method Exit: DaoBase.PerformFetchEntityAction
Method Enter: DaoBase.PerformGetMultiAction
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query:
Query: SELECT "GD"."ELEMENT"."ELEMENT_ID" AS "ElementId", "GD"."ELEMENT"."ELEMENT_TYPE" AS "ElementType", "GD"."ELEMENT"."DISP_ORDER" AS "DisplayOrder", "GD"."ELEMENT"."POSITION_LEFT" AS "PositionLeft", "GD"."ELEMENT"."POSITION_TOP" AS "PositionTop", "GD"."ELEMENT"."WIDTH" AS "Width", "GD"."ELEMENT"."HEIGHT" AS "Height", "GD"."ELEMENT"."CSS" AS "Css", "GD"."ELEMENT"."START_DATE" AS "StartDate", "GD"."ELEMENT"."END_DATE" AS "EndDate", "GD"."ELEMENT"."LAST_EDIT_DATE" AS "LastEditDate", "GD"."ELEMENT"."LAST_EDIT_BY" AS "LastEditBy" FROM "GD"."WEBPAGE" "LPA__1", "GD"."WEBPAGE_ELEMENT" "LPA_W2", "GD"."ELEMENT" WHERE "LPA__1"."WEBPAGE_ID"="LPA_W2"."WEBPAGE_ID" AND "GD"."ELEMENT"."ELEMENT_ID"="LPA_W2"."ELEMENT_ID" AND ( ( "LPA__1"."WEBPAGE_ID" = :WebpageId1)) ORDER BY "GD"."WEBPAGE_ELEMENT"."DISP_ORDER" ASC
Parameter: :WebpageId1 : Int32. Length: 0. Precision: 9. Scale: 0. Direction: Input. Value: 1.
Method Exit: CreateSelectDQ
The thread 0xdb0 has exited with code 0 (0x0).
Method Exit: CreatePagingSelectDQ: no paging.
Method Enter: DaoBase.ExecuteMultiRowRetrievalQuery
A first chance exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException' occurred in SD.LLBLGen.Pro.ORMSupportClasses.NET20.DLL
It appears that ordering by "GD"."WEBPAGE_ELEMENT"."DISP_ORDER" fails because the table has been aliased. Changing the ORDER BY Statement as follows fixs the problem:
SELECT "GD"."ELEMENT"."ELEMENT_ID" AS "ElementId",
"GD"."ELEMENT"."ELEMENT_TYPE" AS "ElementType",
"GD"."ELEMENT"."DISP_ORDER" AS "DisplayOrder",
"GD"."ELEMENT"."POSITION_LEFT" AS "PositionLeft",
"GD"."ELEMENT"."POSITION_TOP" AS "PositionTop",
"GD"."ELEMENT"."WIDTH" AS "Width",
"GD"."ELEMENT"."HEIGHT" AS "Height", "GD"."ELEMENT"."CSS" AS "Css",
"GD"."ELEMENT"."START_DATE" AS "StartDate",
"GD"."ELEMENT"."END_DATE" AS "EndDate",
"GD"."ELEMENT"."LAST_EDIT_DATE" AS "LastEditDate",
"GD"."ELEMENT"."LAST_EDIT_BY" AS "LastEditBy"
FROM "GD"."WEBPAGE" "LPA__1",
"GD"."WEBPAGE_ELEMENT" "LPA_W2",
"GD"."ELEMENT"
WHERE "LPA__1"."WEBPAGE_ID" = "LPA_W2"."WEBPAGE_ID"
AND "GD"."ELEMENT"."ELEMENT_ID" = "LPA_W2"."ELEMENT_ID"
AND (("LPA__1"."WEBPAGE_ID" = :webpageid1))
ORDER BY "LPA_W2"."DISP_ORDER" ASC
llblgen 2.0.0.0 Final 15 June 2007 Self-service
Oracle 9.2 ODP
Am I doing something wrong? Graham
Joined: 21-Aug-2005
I guess you will need to fetch the elements as follows: elements.GetMulti(...);
Passing the required filters and SortExpression. And pass the needed Relations in the relationCollection parameter to filter on the related entities and sort on a related entity as well.