Best way to get database record ID?

J

joe_pool_is

Contributor
Joined
Jan 18, 2004
Messages
507
Location
Longview, TX [USA]
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.
 
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.

PHP: 
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.
 
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:
Code: 
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?
 
Back
Top