GenericADOException was unhandeled using Nhibernate CreateSQLQuery() method

Question

whats wrong with the following code?

public IList<T> GetPostsByUser(object UserId)
    {
        using (var session = sessionFactory.OpenSession())
        {
            using (var transaction = session.BeginTransaction())
            {
                var queryString = string.Format("SELECT C FROM {0} AS C WHERE UserId=:UserId", typeof(T));
                return session.CreateSQLQuery(queryString).SetParameter("UserId", UserId).List<T>();
                //return session.QueryOver<T>().List<T>().IsLike(UserId).List();
            }
        }
    }

Note: UserId is casted to object but its origin comes from System.GUID

Exception:

could not execute query [ SELECT C FROM FNHRepository.Entites.Post AS C WHERE UserId=@p0 ] Name:UserId - Value:3010cd36-539c-4b32-a0fb-976bca58ad38 [SQL: SELECT C FROM FNHRepository.Entites.Post AS C WHERE UserId=@p0]

Value of `@p0` should be enclosed in single quotes, like this: `'3010cd36-539c-4b32-a0fb-976bca58ad38'`. It will be added correctly if you are using `.SetGuid()` method instead of `.SetParameter()`. Not sure why you insist on having an object parameter, if you know that `UserId` will always be `Guid`. — Miroslav Popovic, Jun 24 '12 at 23:02
Baz1nga already provided a similar answer. You can accept his answer. — Miroslav Popovic, Jun 25 '12 at 08:51

score 0 · Answer 1 · answered Jun 25 '12 at 07:11

Note I am explicitly saying the parameter will be of type String this will make sure that Nhibernate wraps the string in single quotes ('') which is what the query expects. If you want to use SetParameter then your query needs to have the single quotes set i.e your query should be

string.Format("SELECT C FROM {0} AS C WHERE UserId=':UserId'", typeof(T));

thus Use this:

var queryString = string.Format("SELECT C FROM {0} AS C WHERE UserId=:UserId", typeof(T)); // you can use SetGuid too, which will again wrap it with quotes
return session.CreateSQLQuery(queryString).SetString("UserId", UserId).List<T>();

or

var queryString = string.Format("SELECT C FROM {0} AS C WHERE UserId=':UserId'", typeof(T));
return session.CreateSQLQuery(queryString).SetParameter("UserId", UserId).List<T>();

Also I would suggest you use typeof(T).Name instead of using the fully qualified name.

using string as paramter is not working. Please see the following error. `Parameter UserId does not exist as a named parameter in [FROM FNHRepository.Entites.Property C WHERE UserId=':UserId']`, Miroslav Popovic's answer was ultimate. — navule, Jun 25 '12 at 09:40

GenericADOException was unhandeled using Nhibernate CreateSQLQuery() method

1 Answers1