Have Geek Brain

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

What is the best way to get 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 are solutions specific to ODBC objects?

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

+1


a source to share


2 answers


You won't find ONE way that will work in all 3-inch motors. 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 sequence and insert the value into the table yourself.



Informix

So, in your code, you will need to know which database is currently configured to use the required code.

Another option is to have a stored procedure, insert, return the id to you. Then you don't need to make any changes to your code, the code calls the 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 base data.

+2


a source


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

 SELECT SCOPE_IDENTITY();

Also, don't use:



 SELECT @@IDENTITY;

This evil. Ok, this is really not evil, but it may not return the id you are looking for if you have triggers or whatever to insert another entry into your db.

+1


a source






More articles:


All Articles