<Help!!> I need to retrieve an autonumber from microsoft access using visual C#

[precado999]

I am working on my senior project and I am using C#.net and Microsoft Access to create a program that lets the user store information into a database. I have many tables but the primary table that mostly everything connects to is the customers table. Well I know how to insert, update and delete using C#. But I need to know how to retrieve the primary key from the customers table which happens to be an autonumber. I kind of understand that I need to use SCOPE_IDENTITY or @@IDENTITY. I just have no idea how. I have tried a couple ways without any success.

[tgreer]

Do your Insert Query, the do a SELECT @@IDENTITY Query immediately afterward. It's best to do both in a stored procedure with the Identity as an OUT parameter.

Try this link: http://www.netomatix.com/adonetauto.aspx

[precado999]

Ill give it a shot.

[tgreer]

Let us know. Remember to mark the thread solved if this answered your question.

[bcasp]

I've always run into issues with using the IDENTITY property in Access. If your using an autonumber, you could always just do something like:

SELECT MAX(autonumber_column_name) AS alias FROM table_name

Do that after your insert and it should return the last autonumber generated.

[imartinez]

Hi

If your DB going to be affected by several procedures or triggers at the same time into your session, you have to use SCOPE_IDENTITY().

Example:

CREATE PROCEDURE prTable_Insert
@value1 varchar(16)

AS
INSERT INTO Table(field1) VALUES (@value1)
SELECT SCOPE_IDENTITY()
GO

This going to return the last value inserted by this store procedure, but if you use @@IDENTITY this going to return the last ID inserted in your sesion, and this value could be another inserted by another process or trigger out of this procedure executed in your actual session.

Good luck!!!

[precado999]

thanks I have figured it out thanks to all the replys.