How to get Identity value of Last Inserted Row
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.
I want to ask What is the Equivalent of @@Identity in MS ACCESS 2007.
Please If any one knows the solution .help me out
Thank you for taking the time to report an issue.
What's wrong... Please write below.
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.
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.
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.
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
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.
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.
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.