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.
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.
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.
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.
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.
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
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