Menu
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;
936
SQLite AUTOINCREMENT is a keyword used for auto incrementing a value of a field in the table. We can auto increment a field value by using AUTOINCREMENT keyword when creating a table with specific column name to auto increment.
AUTOINCREMENT guarantees that automatically chosen ROWIDs will be increasing but not that they will be sequential. Because AUTOINCREMENT keyword changes the behavior of the ROWID selection algorithm, AUTOINCREMENT is not allowed on WITHOUT ROWID tables or on any table column other than INTEGER PRIMARY KEY.
In the INSERT statement above, SQLite computes the values for all three columns. The RETURNING clause causes SQLite to report the chosen values back to the application. This saves the application from having to issue a separate query to figure out exactly what values were inserted.
If you don't specify the rowid value or you use a NULL value when you insert a new row, SQLite automatically assigns the next sequential integer, which is one larger than the largest rowid in the table. The rowid value starts at 1.
I found working query:
SELECT last_insert_rowid()
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
