7

In my project I use System.Data.SQLite. Database has table Tags, which contains autoincrement primary field ID (type Integer). When I write:

using (SQLiteCommand command = conn.CreateCommand())
{
   command.CommandText = "insert into Tags(name) values(@name) returning into @id";
   command.Parameters.Add("@id", DbType.Int32).Direction = ParameterDirection.Output;
   command.ExecuteNonQuery();
}

Visual Studio said that the operation is not supported. How to fix it?

Error occurs on line:

command.Parameters.Add("@id", DbType.Int32).Direction = ParameterDirection.Output;
BartoszKP
  • 34,786
  • 15
  • 102
  • 130
Anton Kandybo
  • 3,678
  • 4
  • 23
  • 31
  • Where's the working code? I have the same problem, but I find "SELECT last_insert_rowid()" to be annoyingly vague. How is @name set? Where does "SELECT last_insert_rowid()" go? Aside: What functionality have you added to Calibre? ;) – hoytster Feb 17 '12 at 23:52
  • I think, you can't to set @name. You key must have name 'Id'. "SELECT last_insert_rowid()" work with last insert. – Anton Kandybo Feb 22 '12 at 08:16

3 Answers3

9

I found working query:

SELECT last_insert_rowid()
Anton Kandybo
  • 3,678
  • 4
  • 23
  • 31
  • How does that work? I get syntax error:INSERT STATEMENT INSERT INTO Orders (NAME) VALUES (@P0) SELECT last_insert_rowid() – sproketboy Sep 23 '11 at 14:46
2

SQLite 3.35.0 and newer supports RETURNING clause:

The RETURNING clause is designed to provide the application with the values of columns that are filled in automatically by SQLite.

The code could look like:

INSERT INTO Tags(name) VALUES(@name) RETURNING ID;
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
-1

I advice to use Stored Procedure.

IN SQL Server has @@IDENTITY system Variable . it returns the last autoincrement value

CREATE PROCEDURE SPGetLastAutoInc @name varchar, @lastAutoResult INT OUTPUT AS

INSERT INTO Tags(name) values(@name)


SET @lastAutoResult = @@IDENTITY

-- Return the number of all items ordered.
RETURN lastAutoResult 
GO
AEMLoviji
  • 3,217
  • 9
  • 37
  • 61