Topics Search

How to get Identity value of Last Inserted Row

How to get Identity value of Last Inserted Row
Views: 57
I am having a table with one of the Column as SerialNo whose datatype is Autonumber.

whenever I Insert a row I want to get the SerialNo of that row.

In Sql Server , there is @@Identity so if we want last inserted row Serialno we write like this.

Select @@Identity

I want to ask What is the Equivalent of @@Identity in MS ACCESS 2007.

Please If any one knows the solution .help me out
Sponsored Links:

Similar posts...


Identity Column in SQL Server View

Identity Column in SQL Server View Icon
Access 2007 front end with SQL Server 2005 Express back end.

Using SQL Server Profiler, I have observed the following:

When an Access datasheet form is bound to a table (or view containing one table), with an Identity (Access Autonumber) column, and a new record is inserted via the bound form, Access uses sp_executesql to insert the new values into the table (or view).
If I look at design view of the table (or view) in Access, the identity column appears as an autonumber.
Since Access recognizes the Identity (Autonumber) column, it uses SELECT @@Identity to retrieve the new identity value.

update with trigger - need current row identity

update with trigger - need current row identity Icon
I have an encryption function that changes a particular value with a given row identifier. I would like to automate this with a trigger during updating. Is it possible to get the identifierof the row that was just updated through a trigger? If so, I could then encrypt the un-encrypted values as they are being updated.

select @@identity returns 0

select @@identity returns 0 Icon
I have been doing some reading, but without success in figuring this out. How do I group two queries an insert and then an @@Identity so that @@identity won't return 0, but will return the primary key, which is an autonumber?

I am running access 2007 and am trying to run this query in response to a control button being pressed so it is in vba.

Retrieve the value of an Autonumber field after an INSERT

Retrieve the value of an Autonumber field after an INSERT Icon
I've read the tutorial from pablo about retrieving the number of the last inserted record. My question is : is @@IDENTITY working if my database is under ms access 2000 ?

Disconnected DataSet not recognizing seed value of SQL Server table

Disconnected DataSet not recognizing seed value of SQL Server table Icon
I have a parent table and a child table. The Parent table has an identity column as the primary key. The dataset does not recognize the current state of the identity column, I.e. even though there is no data in the table, the seed may be at 11, or some other random number due to deletes, etc.

So when I insert into the parent dataset, and view what the value is during runtime it always starts at 1 even though when it commits (via Update(datatable)) back it might be 11 or 12.

The problem for me is, I want to leverage the correct identity value during the process to insert a child row into the child dataset.

Of course, it uses the same values that are in the Parent dataset, which is 1,2,3. etc and then when I go to commit the child data to the database, it errors on foreign key constraint because its trying to insert a 1 when it should be 11.

My question is this: Is there an way to capture the correct identity value from the database? How can I get around this?

I am using ADO.NET 2.0 and SQL Server 2005 sp2.

Insert column into a table only when match found, row by row

Insert column into a table only when match found, row by  row Icon
I have a question, I am comparing two columns from different tables.There are common figures in the two tables.I have compared the two tables using a query.I want the findings from the query to be inserted to the first table, in a new column.Though when inserted in the first table, I need the new column to match the adjacent row.

For example:

Table A contains figures = A,B,C
Table B contains figures = A,F,c

The match will be figure A,C

I want to compare the match table with Table A and insert that information in a new column.
Not just inserting it cause I know how to do that but I need the figures to align with the compared row

RETURN @@IDENTITY and SELECT @@IDENTITY in SQL stored procedure

RETURN @@IDENTITY and SELECT @@IDENTITY in SQL stored procedure Icon
I have a number of stored procedures for inserting records and at the and I return the ID of the newly created record for the calling code.

I'm curious as to why 'RETURN @@IDENTITY' seems to return NULL whereas 'SELECT @@IDENTITY' returns exactly what I want.

What's the difference? What is the SQL 'RETURN' statement supposed to be used for, if not this?

My troublesome stored procedures follow the same basic pattern below.

Stuck on Access "CurrentDB" variable

Stuck on Access "CurrentDB" variable Icon
I've written some fairly fancy Access/VB apps in my time, but it's been a while, and I guess I've even forgotten my ABCs.

I'm making an Access 2002 database. Here's the problem.

1. The DB has person, organization, position, and pop tables (pop is short for"person, organization, or position"). Every person, organization, and position is a pop. Every pop is identified by an AutoNumber PK.

Every person is identified by person_id, a numeric PK that is not AutoNumbered.

2. Whenever a user creates (for example) a new person, what needs to happen is (in this order): (a) Before the person row is inserted, a row is added to the pop table, and so it gets an AutoNumber PK value.

(b) The PK value assigned to the new pop row is grabbed and assigned to person_id for the new person row. (c) The new person row is inserted.

3. I'm trying to implement this by attaching a before insert event procedure to the form that creates persons. Here is one version of the procedure:


4. when I try to insert a person through the form, I get"Runtime error 13: type mismatch". It sticks on the line that includes the CurrentDB variable. When I set a watch on it, it says the variable has no value.

using dbSeeChanges

using dbSeeChanges Icon
I have had to upsize an Access 2003 BE to SQL Server 2005. This means for each table in SQL Server that has an IDENTITY field I will need to add the optional parameter dbSeeChanges to each recordset used.
What I would like to know is, does it matter if I use the dbSeeChanges option on tables that don't have an IDENTITY field?
Also can it be used on local access tables?

The reason I ask is that it is easier to change all OpenRecordset commands than just the ones that use the IDENTITY field.

Sort records while they get inserted in the table

Sort records while they get inserted in the table Icon
When I try to insert a record from Access form in the table then it doesn't appear in the table in the right order. E.g if it is the 6th record then it should appear in the 6th row but it appears in between other records. CAn anyone please tell me how to change the order in which records get inserted in the tbale.