Quickest Way To Return A Primary Keys Value.

[AFterlife]

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.

 

  'returns customerID's
   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 customerID's
       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

Edited March 22, 2005 by AFterlife

[HJB417]

what db are you using, a good database would have a function that returns the last inserted id such as mysql's LAST_INSERT_ID() or mssql's @@IDENTITY.

[AFterlife]

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?

[HJB417]

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 it's DBNull.Value or a null reference) if the insert statement failed.

[AFterlife]

What if 2 people have the same name?

[AFterlife]

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?

[HJB417]

What if 2 people have the same name?

 

change the db schema and add the first and last name as a concatonated unique index

[AFterlife]

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. I've never seen it done that way.

[HJB417]

What if 2 people have the same name?

does your database scheme allow this, or not?

[AFterlife]

SELECT @@ IDENTITY AS NEWID Works great. Thanks for that.