Linq data mapping with Ms Access database: "Missing semicolon (;) at end of SQL statement."

Question

I've been using Linq data mapping with an Ms Access database. I create an OleDbConnection as usual and pass it to the DataContext.

This has worked fine until now, to retrieve data from tables based on complex queries, and even relations work to automatically populate a list of child entities in a 1-N relation.

However when I try to insert data using the following code:

    [Table(Name = "test_table")]
    public class test_item {
        [Column(IsPrimaryKey = true, IsDbGenerated = true)]
        public int field1;
        [Column]
        public int field2;
    }
    public void Test() {
        Table<test_item> tbl = this.GetTable<test_item>();
        test_item x = new test_item();
        x.field2 = 1222;
        tbl.InsertOnSubmit(x);
        this.SubmitChanges();
    }

I get the following error:

"Missing semicolon (;) at end of SQL statement."
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OleDb.OleDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader()
at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult)
at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries)
at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
at System.Data.Linq.ChangeDirector.StandardChangeDirector.DynamicInsert(TrackedObject item)
at System.Data.Linq.ChangeDirector.StandardChangeDirector.Insert(TrackedObject item)
at System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode)
at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)
at System.Data.Linq.DataContext.SubmitChanges()
at MyClass.Test() in C:\...\MyClass.cs:line 123

If I remove the IsDbGenerated flag, it does not crash, but in that case I am forced to specify the primary key (x.field1 = 55), but I would like it to be assigned automatically.

How can I avoid getting this exception?

Answer 1

I've seen this :). You seem to be piggybacking on the Linq2SQL's lack of connection type enforcement ;). Linq2SQL is not supported on Access and while most read queries and some insert queries work due to similarity of Access SQL and TSQL, other things are simply not doable and one such thing is insert with auto-identity columns. The reason is that Linq generates two queries as part of the same insert command (one to insert a record, second to retrieve the newly generated identity) and that doesn't sit well with Access. A workaround is possible but rather ugly, you need to subclass your DataContext and then create ALL problematic Insert* methods (in your case InsertTest_Table) and then use those methods to issue two consequtive commands inside a same transaction

void InsertTest_Table(Test_Table t)
{
    IDbCommand cmd;
    cmd = Connection.CreateCommand();
    cmd.Transaction = this.Transaction;
    cmd.CommandText = "INSERT INTO [Test_Table] ([Field2]) VALUES (@p0)";
    cmd.Parameters.Add(new OleDbParameter("p0", t.field2));
    cmd.ExecuteNonQuery();

    cmd = Connection.CreateCommand();
    cmd.Transaction = this.Transaction;
    cmd.CommandText = "SELECT @@IDENTITY";
    t.field1 = Convert.ToInt32(cmd.ExecuteScalar());
}

My suggestion, dump Access if you can and switch to SQLExpress (even .SDF is better)crea

Answer 2

It sounds like you are using Linq to SQL. Linq to SQL is designed for SQL Server, not Access.

Why not try with Linq to Entities?

I understand, but have never done it, that Access has the necessary ADO.NET provider, so it should work with EF.

Answer 3

Your code generation has left from SQL Server generation, for SQL Server it isn't necessary to have a semicolon for MS SQL Server. Try to modify partial methods for your classes, use this link - http://www.devart.com/linqconnect/docs/ExecuteDynamicMethods.html. Maybe it can help you.