Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to return the value of AUTO INCREMENT column in SQLite with VB6

I have a table in SQLite:

CREATE TABLE "EventType" 
(
[EventTypeID] INTEGER PRIMARY KEY, 
[EventTypeName] VARCHAR(50) NOT NULL UNIQUE
);

Since EventTypeID is an integer and a primary key, that automatically makes it an auto-incrementing column, and that works fine.

I'd like to insert a row into the table and get the newly incremented value from VB6.

Dim oRs as Recordset
dim oCmd as new Command

oCmd.ActiveConnection = GetConnection()
oCmd.Source = "insert into EventType (EventTypeName) values ('blah')"
oCmd.Execute

Is there an automatic way to retrieve the newly created EventTypeID without having to issue another query (select max(EventTypeID) from EventType))?

I seem to remember from VB6 days long time ago, that there was a way to do that.

like image 954
AngryHacker Avatar asked Feb 10 '09 05:02

AngryHacker


People also ask

How can I get auto increment value?

The starting value for AUTO_INCREMENT is 1, which is the default. It will get increment by 1 for each new record. To get the next auto increment id in MySQL, we can use the function last_insert_id() from MySQL or auto_increment with SELECT. Creating a table, with “id” as auto-increment.

How does auto increment work in SQLite?

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.

How do I get the latest ID in SQLite?

SQLite has a special SQL function – last_insert_rowid() – that returns the ID of the last row inserted into the database so getting the ID of a new row after performing a SQL insert just involves executing the last_insert_rowid() command.

How do I create a column auto increment in SQLite?

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. The keyword AUTOINCREMENT can be used with INTEGER field only.


1 Answers

Does SQLite support SCOPE_IDENTITY?

Check out the FAQ. The sqlite3_last_insert_rowid() function will do it. Careful of triggers though

Not tested, but you should be able to send both statements in one call. It's been a while since I wrote any VB6. Also this is not SQL injection safe.

Dim oRs as Recordset
dim sSql as String
sSql = "INSERT INTO EventType (EventTypeName) VALUES ('blah'); SELECT last_insert_rowid() FROM EventType"
oRs.Open sSql oConn
like image 191
bendewey Avatar answered Oct 01 '22 18:10

bendewey