LINQ-to-SQL with SQLite: syntax error near "SELECT" when inserting

Question

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

I'm pretty sure that SCOPE_IDENTITY() is specific to T-SQL (SQL Server). — GalacticCowboy
– GalacticCowboy, Commented Jun 4, 2010 at 16:38

Community · Accepted Answer · 2017-05-23 11:48:22Z

3

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)

edited May 23, 2017 at 11:48

CommunityBot

11 silver badge

answered Jun 4, 2010 at 16:09

Wysawyg

7228 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

LINQ-to-SQL with SQLite: syntax error near "SELECT" when inserting

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related