SubSonic SQL CE Error

Question

Hi There,

I have an old c# project which currently runs on a SQL CE database. I decided yesterday that I would like to use SubSonic to generate a DAL for me as I have used SubSonic a lot in my other projects (with a standard MS SQL database backend) and I love it's ease of use.

Everything seems fine, I can create and delete records but as soon as I update a record using the Save() method an error it thrown:

Example:

Person person = new Person();
person.Name = "Robert";
person.Save();  // Works fine, record is saved

person.Name = "Robert - Updated";
person.Save();  // Fails with error below

"There was an error parsing the query. [ Token line number = 1, Token line offset = 61, Token in error = SELECT ]"

When I update a record by creating a new Query and setting the QueryType to Update, it also seems to be working as expected.

Any ideas?

Thanks

Answer 1

Try to get hold of the generated SQL to see what gets executed.

My guess is that SubSonic escapes column or table names with [ and ], which is fine for a full-blown SQL Server, but its CE counterpart does not support that.

Answer 2

My guess is that Subsonic is trying to execute several SQL statments in a single command - this is not supported by SQL Compact