I have been using the System.Data.SQLite provider for my application. When I try to create a new object and insert it into the database, however, I get a SQL syntax error near "SELECT".
Basically my code looks like:
//supplies is a DataContext connected to my DB
Table<Store> stores = supplies.Stores;
//...
Store newStore = new Store();
// A Store has an Id (pk autoincrement), Name, Number, and EntitySet<Usages>
newStore.Name = "New Store";
newStore.Number = 0;
//...
stores.InsertOnSubmit(newStore);
supplies.SubmitChanges();
but spread throughout some methods.
Is this a common/newbie problem? If so, what am I doing wrong? If not, where/how should I debug this? I am rather new at LINQ-to-SQL.
UPDATE: The log output of the last query generated before the error is this:
INSERT INTO [Stores]([Number], [Name])
VALUES (@p0, @p1)
SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]
-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [0]
-- @p1: Input String (Size = 0; Prec = 0; Scale = 0) [New Store]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.4926
There's an article here about how to show the SQL being generated which might give a better idea of what SQLite doesn't like: http://msdn.microsoft.com/en-us/library/bb386961.aspx
EDIT for log: From the SqlProvider(Sql2008) part, it looks like it's trying to use SqlServer 2008 syntax instead of SQLite syntax which would explain why it thinks the syntax is invalid.
EDIT again for further info: See this question for info on SQLite and Linq: LINQ with SQLite (linqtosql)