SubSonic SQL CE Error

subsonic

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

Best Answer

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.

Related Solutions

Inserting a new record pattern in SubSonic 3

First and foremost: the t4 templates are there for you to change as needed with SS3. That was the main idea with using T4 - I don't want to back you into my silliness :).

To the question at hand - I think this might be a bug in our templates that refuses to stick a value into the PK field:

    ISqlQuery BuildInsertQuery(T item) {
        ITable tbl = _db.FindTable(typeof(T).Name);
        Insert query = null;
        if (tbl != null) {
            var hashed = item.ToDictionary();
            query = new Insert(_db.Provider).Into<T>(tbl); ;
            foreach (string key in hashed.Keys) {
                IColumn col = tbl.GetColumn(key);
                if (!col.IsPrimaryKey) {
                    query.Value(key, hashed[key]);
                }
            }
        }
        return query;
    }

In this, our check should actually be...

                if (!col.IsPrimaryKey && !col.AutoIncrement) {
                    query.Value(key, hashed[key]);
                }

In this way, the non-identity will be inserted. But in reading your issue here, it sounds to me like you're not trying to insert into a non-identity.

The email you sent me doesn't say anything about PKs as identity - your PK was a thing called "NAme" which is a string type and not and identity (auto-increment).

I'm wondering about when I cannot get around this issue--when I have to add a new record to the database and have an identity as my primary key.

This is what SubSonic assumes - that your PK is an IDENTITY column. If you ONLY have an IDENTITY column, we can't help you because this is a deadlocked table in that you can't insert any value into it, therefor you can't tick the IDENTITY column. Your only recourse at this point is to SET IDENTITY INSERT="off", which defeats the purpose.

Hopefully this will answer your question? If I'm not getting it - can you do this for me:

One sentence: what can't you do and what's the error
What did you expect

Thanks Will and I hope I'm not being thick.

SubSonic with MS SQL 2008

I've not used SQL2008 myself, but I know there is still a bug in SubSonic 2.1 and 2.2 in handling the new date only and time only data types (it sees them as strings).

Best Answer

Related Solutions

Inserting a new record pattern in SubSonic 3

SubSonic with MS SQL 2008

Related Topic