ODBC Insert Statements

Databases
1

This is probably a simple question, but I’m not having much luck finding an answer. Using ODBC how do you get the last record ID of a record that was inserted?

2

You have to call the db-engine-specific function to return that value. Eg., for sqlite,

rs= db.sqlselect("select last_insert_rowid")
id = rs.idxfield(1).integervalue
3

Okay. This is SQL Server so I know how to do that.

4

Hi

A little late but it can serve With Sql Server 2008 >=

dim r as RecordSet = mdb.SQLSelect(“Insert Into dbo.Item (Description, Unit_Id) OUTPUT INSERTED.iItemId Values(‘Item’, 1)”)
MsgBox(r.IdxField(1).StringValue)

Mauricio

5

I am not certain that they can be used with ODBC, but on SQL Server, SELECT @@IDENTITY and SELECT SCOPE_IDENTITY exist for the purpose that you describe. SCOPE_IDENTITY is limited to the context of the current transaction, and @@identity is connexion wide if I remember correctly.

6

Hi Louis.

That’s right, the definition of @@IDENTITY and SCOPE_IDENTITY,

Can be done in several ways, this is shorter.

Mauricio