ID of last row

[wessamzeidan]

Hi, how can I get the id of the row I just added to a table in a database...

 

Thanks

[Joe Mamma]

Hi, how can I get the id of the row I just added to a table in a database...

 

Thanks

 

what are you using for ID's?

I use GUID's. I generate the guid before I insert the row, therefore I have the GUID for usage in child tables.

 

What is your relational primary key? not the surrogate ID field you may have put a primary key constraint on.

 

for example, I have course table with a CourseID and teacher table with a teacher ID, I have a courselisting table(CourseID, TeacherID, RoomID, Period) with an additional field CourseListingID. In this a simple case, the relational primary key, contrary to popular belief is (CourseID, TeacherID, RoomID, Period) , while CourseListingID is a generated surrogate key (as i said I prefer GUID's) with a unique constraint on it so it can be used as a foreign key in a child relationship, Ie studentschedule (StudentId, CourseListingID)

 

At insert time, I generate a guid for a CourseListingID, and try to insert it along with the known (CourseID, TeacherID, RoomID, Period) relation. if I succeed I can now use CourseListingID to schedule students by inserting it with a studentID into studentschedule. if it fails I check the error code for primary key violation and select from courselisting the previously assigned CourseListingID for the (CourseID, TeacherID, RoomID, Period) relation and use that.

 

Relational Data, by definition, should not be concerned with where in the table the data is or the order in which it was entered.

 

Joe

[wessamzeidan]

The table that I'm inserting in has a primary key as autonumber, so the database generates the id. Now I need a way to get this generated id.

 

Thanks

[Joe Mamma]

The table that I'm inserting in has a primary key as autonumber, so the database generates the id. Now I need a way to get this generated id.

 

Thanks

(I assume you are using access due to your using the term autonumber, but this will apply accross all platforms.)

 

Ok, the autonumber may have a primary key constraint on it, but it is not the relational primary key, in most cases. if there is a relation in the row that identifies a unique combination, that is really your primary key and should have the primary key constraint on it to prevent duplicate data to be inserted. Still put a unique constraint on the AutoNumber (I Prefer autonumber:replicationID's for a number of reasons, 'purely religious', I assure you and I have already been chastized for 'religious diatribe' ;) ). Insert the data, then select the Guid for the unique combination you just entered.

 

If there is no unique combination of data for the relation, then I dont use an autonumber: replicationID but a Number:ReplicationId and I generate a Guid and insert that with the data. I then have the inserted GUID to be used after the fact for, perhaps, inserting into a table as a foreign key that is a child relation.

 

If you still do want to use the ID of the last record inserted, there are some ways to accomplish that, but they violate RDBMS theory and would lead to some involved, non-relational techniques to guarantee you got the desired ID.

 

Joe

[foz]

ok not sure if this is what you need, but if you're looking to get the id of the last row entered and the id is an autonumber this should work.

 

select top 1 * from tbl_name order by id desc

 

foz

[Joe Mamma]

ok not sure if this is what you need, but if you're looking to get the id of the last row entered and the id is an autonumber this should work.

 

select top 1 * from tbl_name order by id desc

 

foz

 

there are (at least) two reasons this should not be done:

 

1. Without some expensive resource allocation, there is no guarantee that the row returned will be the last one entered. (I'll give you a moment to think about this) Remember, relational, by definition, is time/space independent. Don't use spreadsheets for a database, don't use a database like spreadsheets.

2. The order by statement is most likely going to be against the grain of the index.

 

Yes, this approach can be forced to work, but it is not proper relational technique and could be the difference between getting a job and not.

 

Its the equivalent to driving screws with a hammer. It works but its messy and over time it might not hold.

 

Joe Mamma

Edited March 8, 2004 by Joe Mamma

[karimgarza]

Add a CreationUser and CreationDate Field to get PrimaryKey

 

Hi, how can I get the id of the row I just added to a table in a database...

 

Thanks

 

 

Do the following:

 

1)you are going to add the following two fields to the table (I have it in all my tables) CreationUser and CreationDate. Creation User is a string and CreationDate is a DateTime.

 

Once you have those two fields in the table you want the id from you have to do the following:

 

2)) Insert the new record the way you were doing it before, but this time include the a username in the username field and the current time (down to the seconds or milliseconds if possible, you can use NOW).

 

3)Query the table like this for example "SELECT myTableID FROM myTABLE WHERE CreationUser = [myUSER] AND CreationDate = [the date that you created the record with]

 

That is it. This will also help you to mantain a log of Who is inserting rows and at what date and time.

 

if you need me to explain this further do not hesitate to ask.

 

Good Luck,

Karim

[Nerseus]

Try one of the following links. Essentially, use @@IDENTITY in Access to get the last AutoNumber field that was inserted.

 

http://xtremedotnettalk.com/showthread.php?t=83166&highlight=%40%40IDENTITY+access

http://xtremedotnettalk.com/showthread.php?t=82479&highlight=%40%40IDENTITY+access

 

-Nerseus

[Joe Mamma]

Do the following:

 

1)you are going to add the following two fields to the table (I have it in all my tables) CreationUser and CreationDate. Creation User is a string and CreationDate is a DateTime.

[snip]

That is it. This will also help you to mantain a log of Who is inserting rows and at what date and time.

 

this for all intents and purposes is no different than changing the auto number to a replicationID as they are generated based on the Time/Machine relation. Physically, though, you have added another index (CreationUser, CreationDate) which, unless audit trail is a design spec is unnecessary and hinders performance. Also, using an ID that is a number:ReplicationID, you reduce by half the number of database commands executed as you dont have to select the last ID entered, you already have it.

 

Joe Mamma

[foz]

there are (at least) two reasons this should not be done:

 

1. Without some expensive resource allocation, there is no guarantee that the row returned will be the last one entered. (I'll give you a moment to think about this) Remember, relational, by definition, is time/space independent. Don't use spreadsheets for a database, don't use a database like spreadsheets.

2. The order by statement is most likely going to be against the grain of the index.

 

Yes, this approach can be forced to work, but it is not proper relational technique and could be the difference between getting a job and not.

 

Its the equivalent to driving screws with a hammer. It works but its messy and over time it might not hold.

 

Joe Mamma

 

cheers for the tip!:D

[Joe Mamma]

Try one of the following links. Essentially, use @@IDENTITY in Access to get the last AutoNumber field that was inserted.

 

http://xtremedotnettalk.com/showthread.php?t=83166&highlight=%40%40IDENTITY+access

http://xtremedotnettalk.com/showthread.php?t=82479&highlight=%40%40IDENTITY+access

 

-Nerseus

Is this guaranteed to get the actual identity that was generated by your insert?

Theoretically, couldnt another user execute an insert before your event is processed, thereby this solution would return the second users identity?

 

True, this can be guaranteed by wrapping a transaction around the entire process, but that is a resource hog and if performance is an issue, then this isn't really desireable. This solution also involves two database actions, the insert and the select. If this can be avoided, then it should be avoided.

 

joe mamma

[PlausiblyDamp]

@@IDENTITY returns the last auto numbered value inserted into a table on the current connection - it is not affected by other connections accessing the same table

[Joe Mamma]

@@IDENTITY returns the last auto numbered value inserted into a table on the current connection - it is not affected by other connections accessing the same table

Ahh, interesting.

 

I wonder if in a SQL server connection pooled situation this holds true.

Still it involves an extra select statement that is not necessary with generating a GUID.

 

joe

[wessamzeidan]

Thanks alot for all you guys for the help....I've solved the problem by using the information just inserted to get the id back, but I think using the @@IDENTITY would be better, I'll give it a try

[PlausiblyDamp]

Guids are also larger than a simple integer when used as a unique identifier, also the code above uses SELECT @@IDENTITY were @@IDENTITY is really a varaible so no extra physical DB access is required to retreive the value.

 

Actually using a guid you would have to insert the record, get a default constraint generate the guid and then select it anyway if you needed it....

[Nerseus]

GUIDs can be good, but they're hard to read which usually equates to harder to maintain for developers. Pros/Cons to both approaches.

 

As for SQL Server, there are two ways to get the last ident: @@IDENTITY and SCOPE_IDENTITY(), which I've had to use quite often for various reasons (procs calling other procs which do inserts and the "outer" proc need or don't need the @@IDENTITY). SQL Server can pool connections but it still knows about each machine making the request (if the connection strings are exactly the same). Normally you shouldn't have to worry about using @@IDENTITY.

 

I have yet to find a way in Access to do the INSERT and select the @@IDENTITY. I guess if you want performance, go with MSDE.

 

-nerseus

[talahaski]

Guid?

 

Can you use a GUID in a Access database?

 

I would assume you would need to place it in a Access text field, defined to some large length?, and make that field your primary key.

 

 

What VB.net code will auto generate a GUID?

[talahaski]

Nevermind, I answered it myself.

 

You can use a text field of length 36, then use the guid.newguid.tostring() method to get a new random number

[Joe Mamma]

Nevermind, I answered it myself.

 

You can use a text field of length 36, then use the guid.newguid.tostring() method to get a new random number

In access, Use Datatype of Number/Autonumber with Size: ReplicationID.

 

To generate Guids in .Net, use the Static/Shared Guid.NewGuid method.

 

I have no problem using them in any database flavor.

I love em.