Nullable Types and IQuery - Getting must have value error

eddlsda · **Posted:** Wed Jan 23, 2008 1:17 pm

How do you set the value on a query for a replaceable parameter where the type is a nullable type (take your choice int?, DateTime?, etc.). I have beat my head against the wall trying to make this work. In conventional dynamic sql I would just set DBNull.Value for the parameter. But nothing I do has worked. I have tried the SetInt32, SetParameter, etc. Any suggestions would be good. Here is the query

Code:

IQuery query =
session.CreateSQLQuery("exec prc_EligibilityLookup-sp :memberId :productId :firstName :lastName :dateOfBirth :gender")
.SetString("memberId", eligibilitySearchCriteria.HealthPlanInsuranceMemberId)
.SetString("firstName", eligibilitySearchCriteria.FirstName)
.SetString("lastName", eligibilitySearchCriteria.LastName)
.SetEnum("gender", eligibilitySearchCriteria.Gender)
.SetInt32("productId", (int)eligibilitySearchCriteria.ProductId)
.SetDateTime("dateOfBirth", (DateTime)eligibilitySearchCriteria.DateOfBirth);

Note that the int and DateTime are nullable types.

thanks!

mkb137 · **Joined:** Fri May 18, 2007 1:41 pm **Posts:** 8

try

Code:

.SetParameter( "dateOfBirth", eligibilitySearchCriteria.DateOfBirth, NHibernateUtil.DateTime )

eddlsda · **Posted:** Wed Jan 23, 2008 3:28 pm

thanks, I tried SetParameter with various other types including the nullables type but did not try it with their types. I'll give it a shot and let you know if that works.