Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > Bugs & Issues> Error in SQL generated during sort and GetMultiElementCollectionVia method
 

Pages: 1
Bugs & Issues
Error in SQL generated during sort and GetMultiElementCollectionVia method
Page:1/1 

  Print all messages in this thread  
Poster Message
like2175
User



Location:
Camberley, United Kingdom
Joined on:
27-Mar-2006 16:16:06
Posted:
83 posts
# Posted on: 15-Aug-2007 16:38:32.  
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:
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:

Code:

[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



Code:

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:

Code:
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

  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14639 posts
# Posted on: 15-Aug-2007 17:11:35.  
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.


  Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.