14

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.

AngryHacker
  • 59,598
  • 102
  • 325
  • 594
  • Possible duplicate of [Does SQLite support SCOPE\_IDENTITY?](http://stackoverflow.com/questions/304543/does-sqlite-support-scope-identity) – Steve Chambers Dec 20 '15 at 22:18

5 Answers5

17

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
Community
  • 1
  • 1
bendewey
  • 39,709
  • 13
  • 100
  • 125
  • 4
    btw, with the ODBC driver, you have to split your query into 2 calls. – AngryHacker Sep 19 '09 at 01:37
  • so what does Insert() return then? - I know I can look it up but it might be useful here for completeness, since that's how I found this post. – Adrian K Sep 09 '16 at 00:58
13

Run this query inside of your code:

SELECT SEQ from sqlite_sequence WHERE name='tablename'
user3237970
  • 141
  • 1
  • 3
3

I don't use VB, so I don't know the interface you're using with your database, but, as bendewey said, there is a function in the c API called sqlite3_last_insert_rowid() that will return the primary integer key for the last inserted row, so you could look for a similar function in your interface.

If that doesn't work, you can use the SQLite-specific query:

SELECT last_insert_rowid()
Kyle Cronin
  • 77,653
  • 43
  • 148
  • 164
2

You can call SELECT last_insert_rowid() after your insert

Assaf Lavie
  • 73,079
  • 34
  • 148
  • 203
-1

If use 

SELECT last_insert_rowid()

returns the last rowid, you can use :

SELECT last_insert_rowid() AS rowid FROM table_name LIMIT 1

for obtain the last rowid for an individual table

frogatto
  • 28,539
  • 11
  • 83
  • 129
sgarcia
  • 97
  • 4
  • 4
    This not what the [documentation](https://www.sqlite.org/lang_corefunc.html#last_insert_rowid) says: "The last_insert_rowid() SQL function is a wrapper around the sqlite3_last_insert_rowid() C/C++ interface function." which means that there is no such thing as the last `rowid` for an individual table. – Gwendal Roué Dec 15 '16 at 12:00