Best way to get database record ID?

[joe_pool_is]

After populating a database with new/updated information, what is the best way to get the ID number for that record?

 

Background: I am using an Access database; the ID number is the Primary Key; and I want to store this to use in my next set of options.

[JRichmond]

Here's a couple links with info on this subject...

http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B815629

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20803257.html

 

I hope these help! :)

[joe_pool_is]

Cool. Thanks.

[Derek Stone]

Execute an identity select after inserting the record.

SELECT @@IDENTITY

[VBAHole22]

How about Oracle?

 

In Oracle I am using the following to accomplish this but I feel like it is not the correct way to get it done.

 

Dim strTranInsert As String = "INSERT INTO " & TransactionTable & " (TRAN_ID," & _
       " DG, TRAN_STARTTIME, DG_PATH) VALUES (TRAN_SEQ.NEXTVAL, '" & DGName & "', to_date('" & strStartTime & "','DD-MON-YYYY HH24:MI:SS'), '" & DGPath & "')"
       Dim strFetchID As String = "SELECT TRAN_SEQ.CURRVAL from " & TransactionTable
       Dim objTranInsertCmd As New OracleCommand(strTranInsert, OracleConn)
       Dim FetchID As OracleNumber 'The Sequence number assigned by Oracle

       Try
           OracleConn.Open()
           objTranInsertCmd.ExecuteNonQuery()
           objTranInsertCmd.CommandText = strFetchID    'Fetch back the unique id
           FetchID = DirectCast(objTranInsertCmd.ExecuteOracleScalar, OracleNumber)
       Catch ex As Exception
           Me.txtError.Text = "Error fetching id from transaction table: " & ex.Message
           blSuccess = False
       Finally
           OracleConn.Close()
       End Try

 

The reason I think this is the wrong way to go about it is because there is a time lag (albeit a short one) between the time the insert occurs and the id value is fetched. I should be alright because I am in a single user environment but this could be an issue if many records are being dumped in by many users.

 

For those unfamiliar, a sequence is Oracle's equivalent to the identity or autonumber field in MS.

[joe_pool_is]

Derek: Wow! That's short. I like it!

 

VBAHole22: You version looks like it might work too, but I'm going with "short and sweet" first! :)

[joe_pool_is]

Derek:

I've run into something with your version. I left the full SQL string off (because it is long and boring), but the rest looks like this:

Dim SQL as String = "INSERT INTO Table1 (...) VALUES (...)"
Dim Reader1 as OleDb.OleDbDataReader
Command1 = New OleDbCommand
Command1.Connection = New OleDbConnection(strConnection)
Command1.Connection.Open()
Command1.CommandText = SQL
Command1.ExecuteNonQuery()
Command1.CommandText = "SELECT @@IDENTITY" ' get ID of record
Reader1 = Command1.ExecuteReader
Command1.Connection.Close()

There is no Exception thrown, but on my Reader1 properties, I have:

 

Reader1

Depth - 0

FieldCount - 1

HasRows - True

IsClosed - False

Item - <cannot view indexed property>

RecordsAffected - 0

 

What do I need to read in? How do I get my ID number off of this data reader?