How to return AUTO INCREMENT column value in SQLite using VB6

Question

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 primary key, this automatically makes it an auto-increment column, and it works great.

I would like to insert a row into a table and get a new value with 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 get a newly created EventTypeID without having to issue another request (select max (EventTypeID) from EventType)?

It seems I remember from VB6 days a long time ago that there was a way to do this.

Answer 1

Does SQLite support SCOPE_IDENTITY?

Check out the FAQ . The sqlite3_last_insert_rowid () function will do this. Caution triggers though

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

 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 

Answer 2

Run this query inside your code:

 SELECT SEQ from sqlite_sequence WHERE name='tablename' 

Answer 3

I do not use VB, so I don’t know the interface that you use with your database, but as bendewey said, there is a function in c API with sqlite3_last_insert_rowid() that will return the primary integer key for the last inserted row so you can search a similar function in its interface.

If this does not work, you can use a SQLite-specific query:

 SELECT last_insert_rowid() 

Answer 4

You can call SELECT last_insert_rowid() after inserting

Answer 5

If used

 SELECT last_insert_rowid() 

returns the last rowid , you can use:

 SELECT last_insert_rowid() AS rowid FROM table_name LIMIT 1 

to get the last rowid for a single table