CODEWITHSUNDEEP
.netsqlodbc
brasskazoo
brasskazoo

Reputation: 78818

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

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.

Upvotes: 1

Views: 2184

Answers (2)

jvanderh
jvanderh

Reputation: 2955

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.

Upvotes: 2

Esteban Araya
Esteban Araya

Reputation: 29664

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.

Upvotes: 1

Related Questions