Can I get the primary key of an inserted row, using ODBC?

Question

What is the best way to retrieve the primary key of an inserted row, when using ODBC objects in .NET?

For example (VB):

Dim command As OdbcCommand = gOdbcConn.CreateCommand()
command.CommandText("INSERT INTO auhinode (node_key, node_desc) VALUES (0, 'New Node')")
...
Dim result As Integer = command.ExecuteNonQuery()

I've seen a couple of other suggestions here, but I'm wondering if there's solutions specific to the ODBC objects?

Edit: The reason we're using ODBC, is because we support 3 different databases - SQL Server, Oracle, Informix.

Answer 1

Assuming the db is a SQLServer db, all you have to do is:

 SELECT SCOPE_IDENTITY();

Also, don't use:

 SELECT @@IDENTITY;

It's evil. Ok, it's not really evil, but it may not return the ID you're looking for if you have triggers or anything else that would insert another record in your db.

Answer 2

You are not going to find ONE way that will work in all 3 db engines. They each have different ways of getting the ID of the row you just inserted.

select scope_identity(); in sql server.

In oracle you need to use a sequence and insert the value in the table yourself.

Informix

So in your code you are going to have to know what database is currently configured to use the required code.

Another option is to have a stored procedure do the insert, get the ID an return to you. Then you don't need to make any changes in your code, the code calls a stored procedure that returns the ID but you have different versions of the stored procedure for each db engine, each with the same name, and include them in your scripts to create your database.