Quickest Way To Return A Primary Keys Value.

A

AFterlife

Well-known member
Joined
Dec 21, 2003
Messages
73
I have a function i use in Windows forms applications for returning the primary key of an auto incrementing identifier. Im making an ASP application...and i want this to be as quick as possible.

Whats the quickest way to return the number of the primary key ,of a record that was just inserted into a database?

Here is an example of a function i used in a windows form app. But a completely different context. Since i dont need to store them.

Code: 
   returns customerIDs
    Private Function getCustomerID(ByVal strFirstName As String, ByVal strLastName As String, ByVal dtDate As Date) As Integer
        Dim strSQL As String = "SELECT FirstName, LastName, CustomerID, LastUpdate FROM tblCustomer WHERE LastUpdate = " _
        & "#" & dtDate & "#" & " AND  FirstName = " & "" & strFirstName & "" _
         & " AND LastName = " & "" & strLastName & ""
        Static intIncrementID As Int32
         auto increment the temporary holding area of customerIDs
        intIncrementID += 1
        dsCustomers.Tables.Add("CustomerID" & intIncrementID)
        daCustomerID = New OleDb.OleDbDataAdapter(strSQL, cnCustomers)
        daCustomerID.Fill(dsCustomers.Tables("CustomerID" & intIncrementID))
        Return dsCustomers.Tables("CustomerID" & intIncrementID).Rows(0).Item("CustomerID")
    End Function
 
Last edited by a moderator:
what db are you using, a good database would have a function that returns the last inserted id such as mysqls LAST_INSERT_ID() or mssqls @@IDENTITY.
 
Well..before this ive used Access. For this project im using MS SQL. Im not familiar with stored procedures. Maybe thats what i need to use?
 
use an execute method that returns a result such as ExecuteScalar or ExecuteReader and use a command text similar to INSERT INTO foo(FirstName, LastName) VALUES(foo, bar); SELECT @@IDENTITY;

ExecuteScalar will return the value you want, and ExecuteReader will do the same. I believe the the value will be null (I forgot if its DBNull.Value or a null reference) if the insert statement failed.
 
Ahhh...Do you take the count in the array and subtract by one getting the last one? Would that then be the last inserted record?
 
I like to use incrementing numbers for the unique value as the CustomerID since the value will be stored in a couple other tables as well. I think it would cause a bunch of problems if i did that.The number indicates the unique customer. Which will be in the orders table, package table, etc...etc...Maybe im just not following you. Ive never seen it done that way.
 
Back
Top