ORM GetSqlQuery oddness

[Kris]

I have run into a problem with using the GetSqlQuery() in IObjectScopeProvider.

I have the following code that causes this error:
Query failed: System.NullReferenceException: Object reference not set to an instance of an object.
   at OpenAccessRuntime.Data.VariableLengthStringConverter.Write(DataHolder& data)
   at OpenAccessRuntime.Relational.RelationalQueryResult.SetParameters(RelationalCompiledQuery rcq, PreparedStatement stat, Object[] values)

IQuery q1 = sc.GetSqlQuery(@"SELECT PopulationID, LocationName, Direction, Distance FROM populations WHERE populationid IN  (SELECT populationid FROM populationswrittenreports WHERE  writtenReportID = ?)", typeof(Population), "VARCHAR writtenReportID");

         IQueryResult res = q1.Execute(new object[] { reportID });

         try
         {
             int count = res.Count;
             return DataDriver.Utilities.ToList<Population>(res.ToList());
         }
         catch
         {
             return new List<Population>();
         }

Now when I change the first two lines to below. the code executes and runs fine. I assume the SELECT IN clause is what is causing the error, seeing as that is the only change. Is there a way to accomplish this without putting all my "complex" sql code into stored procedures?

IQuery q1 = sc.GetSqlQuery(@"SELECT PopulationID, LocationName, Direction, Distance FROM populations ", typeof(Population), null);

IQueryResult res = q1.Execute();

thanks

[Ady]

Hi Kris,

I did try a similar scenario with the standard Northwind database by executing the following query - "select * from Orders where ShipVia in (select ShipperID from Shippers where CompanyName = ? )"
and I could succesfully get the results. Can you provide some information about the table definition, this might provide further insight into the problem. If you can provide a sample solution where I can reproduce the exception this would definitely save us some time.

Looking forward to resolving the issue soon.

Best wishes,
Ady
the Telerik team

[Ady]

Hello Kris,

I did try a similar scenario with the standard Northwind database by executing the following query - "select * from Orders where ShipVia in (select ShipperID from Shippers where CompanyName = ? )"
and I could succesfully get the results. Can you provide some information about the table definition, this might provide further insight into the problem. If you can provide a sample solution where I can reproduce the exception this would definitely save us some time.

Looking forward to resolving the issue soon.

Best wishes,
Ady
the Telerik team

[Kris]

I have opened a support ticket (# 438900) and attached a sample app to it. Please take a look and let me know, thanks.

[Ady]

Hello Kris,

 Could we close this thread as I've already answered to your problem via the support thread?

Greetings,
Ady
the Telerik team

[Kris]

Sure can. I will post your answer in case other developers run into the same issue. Thanks for the help

Answer:
It occurs since the type specified in the query is 'VARCHAR' and the actual column is of type 'uniqueidentifier'. You should replace the 'VARCHAR' with 'GUID' and then it will work

SELECT PopulationID, LocationName, Direction, Distance FROM populations WHERE populationid IN  (SELECT populationid FROM populationswrittenreports WHERE  writtenReportID = ?)", typeof(Population), "GUID writtenReportID"