No positional parameters in query

Gradiation · **Joined:** Fri Apr 27, 2007 11:50 am **Posts:** 23

Hi Guys,

I have done a search on google and on here but don't seem to find an answer to my problem.

I am trying to map a collection using a custom query but i keep getting an error. Basicaly i have a User entity and Hotel entity. I wan users to have a list of most viewed and recently viewd hotels. I am doing this by creating to dynamic collections setup as follows:

NHibernate mapping:

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class name="Core.Domain.User, Core" table="`User`" lazy="false">

    <id name="Id" column="`UserId`" unsaved-value="00000000-0000-0000-0000-000000000000">
      <generator class="guid" />
    </id>

    <property name="Username" />
    <property name="Salt" />
    <property name="Firstname" />
    <property name="Lastname" />
    <property name="Email" />
    <property name="FailedLoginCount" />
    <property name="IsActive" />
    <property name="IsLockedOut" />
    <property name="CreatedDate" />
   <property name="ModifiedDate" />
    <property name="LastLoginDate" />
    <property name="LastLoginIp" />
    

   <bag name="RecentlyViewedHotels" access="nosetter.camelcase-underscore" lazy="true" generic="true">
     <key />
     <one-to-many class="Core.Domain.Hotel, Core" />
     <loader query-ref="GetRecentlyViewedHotels" />
   </bag>
     
   <bag name="MostViewedHotels" access="nosetter.camelcase-underscore" lazy="true" generic="true">
     <key />
     <one-to-many class="Core.Domain.Hotel, Core" />
     <loader query-ref="GetMostViewedHotels" />
   </bag>

  </class>

  <sql-query name="GetRecentlyViewedHotels">
   <return alias="h" class="Core.Domain.Hotel, Core"/>
   SELECT TOP 10 {h.*}
   FROM Hotel h
   LEFT OUTER JOIN UserHotelView uhv
   ON uhv.HotelId = h.HotelId
   ORDER BY uhv.LastViewedDate DESC
  </sql-query>
   
  <sql-query name="GetMostViewedHotels">
   <return alias="h" class="Core.Domain.Hotel, Core"/>
   SELECT TOP 10 {h.*}
   FROM Hotel h
   LEFT OUTER JOIN UserHotelView uhv
   ON uhv.HotelId = h.HotelId
   ORDER BY uhv.Count DESC
  </sql-query>
   
</hibernate-mapping>

But when i pull a user back and try to access either list i get the following error:

Code:

No positional parameters in query: SELECT {h.*}
FROM Hotel h
LEFT OUTER JOIN UserHotelView uhv
ON uhv.HotelId = h.HotelId
ORDER BY uhv.Count DESC 

Looking at the stack trace i see the following:

Code:

NHibernate.Impl.AbstractQueryImpl.SetParameter(Int32 position, Object val, IType type) +152

Which looks as though NHibernate is trying to set a parameter that doesn't exist. Do i need to add a certain parameter to my collection? or am i doing something wrong?

Many thanks

Matt

Gradiation · **Joined:** Fri Apr 27, 2007 11:50 am **Posts:** 23

Ok, my own fault, i missed the where clause for the user id so my queries are now:

Code:

<sql-query name="GetRecentlyViewedHotels">
   <return alias="h" class="Core.Domain.Hotel, Core"/>
     SELECT TOP 10 {h.*}
     FROM Hotel h
     LEFT OUTER JOIN UserHotelView uhv
     ON uhv.HotelId = h.HotelId
     WHERE uhv.UserId = :userid
     ORDER BY uhv.LastViewedDate DESC
  </sql-query>

  <sql-query name="GetMostViewedHotels">
   <return alias="h" class="Core.Domain.Hotel, Core"/>
     SELECT TOP 10 {h.*}
     FROM Hotel h
     LEFT OUTER JOIN UserHotelView uhv
     ON uhv.HotelId = h.HotelId
     WHERE uhv.UserId = :userid
     ORDER BY uhv.Count DESC
  </sql-query>

Which seems to have fixed that problem, however i am now getting the following error when i try to access the property:

Code:

Object reference not set to an instance of an object.

with the following stack trace:

Code:

[NullReferenceException: Object reference not set to an instance of an object.]
   NHibernate.Collection.Generic.PersistentGenericBag`1.get_Count() +83
   Web.Admin.Default.Page_Load(Object sender, EventArgs e) in C:\Store\Work\Website\Source\Code\Web\Admin\Default.aspx.cs:38
   System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15
   System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +33
   System.Web.UI.Control.OnLoad(EventArgs e) +99
   System.Web.UI.Control.LoadRecursive() +47
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1436

 

Anybody got any ideas?

Gradiation · **Joined:** Fri Apr 27, 2007 11:50 am **Posts:** 23

I've tried upgrading to the latest version of NHibernate by compiling the source in the trunk and still not fixed the problem.

Any help appreciated as i can't see how i am doing anything any different to the documenation.

Matt

wolli · **Posted:** Mon Sep 01, 2008 8:14 am

Asfaik you need

<load-collection alias="h" role="Core.Domain.Hotel, Core"/>

for collection loading not

<return alias="h" class="Core.Domain.Hotel, Core"/>

Gradiation · **Joined:** Fri Apr 27, 2007 11:50 am **Posts:** 23

Hey,

Thanks for your help. It looks like i need both, and i had to play with my sql statement aswell to the following:

Code:

<sql-query name="GetRecentlyViewedHotels">
   <load-collection alias="h" role="Core.Domain.User.RecentlyViewedHotels" />
   <return alias="h" class="Core.Domain.Hotel, Core" />
   SELECT TOP 10 {h.*}
   FROM ( SELECT h1.*, uhv1.UserId, uhv1.LastViewedDate FROM Hotel h1  LEFT OUTER JOIN UserHotelView uhv1 ON uhv1.HotelId = h1.HotelId ) as h
   WHERE h.UserId = ?
   ORDER BY h.LastViewedDate DESC
</sql-query>

<sql-query name="GetMostViewedHotels">
   <load-collection alias="h" role="Core.Domain.User.MostViewedHotels" />
   <return alias="h" class="Core.Domain.Hotel, Core" />
   SELECT TOP 10 {h.*}
   FROM ( SELECT h1.*, uhv1.UserId, uhv1.Count FROM Hotel h1  LEFT OUTER JOIN UserHotelView uhv1 ON uhv1.HotelId = h1.HotelId ) as h
   WHERE h.UserId = ?
   ORDER BY h.Count DESC
</sql-query>