Reputation: 61656
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.
Upvotes: 14
Views: 39209
Reputation: 2662
Since Version 3.35 you can use
Insert Into <yourtable> Default Values Returning <idcolumn>
Run the Insert with ExecuteScalar.
Upvotes: 3
Reputation: 141
Run this query inside of your code:
SELECT SEQ from sqlite_sequence WHERE name='tablename'
Upvotes: 13
Reputation: 97
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
Upvotes: -1
Reputation: 40265
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
Upvotes: 17
Reputation: 79163
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()
Upvotes: 3