MS Access - Get new Autonumber value after DataAdapter.Update

[Guest]

Hello

I have MS Access table with autonumber PK field

I insert record and perform dataAdapter.Update(table).
Now I need to know value of AutoNumbered field.

Do I have to refresh whole table, by doing
= table.Clear();
- dataAdapter.Fill(table);

Or there is some other option to get new value from AutoNumber field

Thanks
Milan

 

[deko]

I have MS Access table with autonumber PK field

I insert record and perform dataAdapter.Update(table).
Now I need to know value of AutoNumbered field.

Do I have to refresh whole table, by doing
= table.Clear();
- dataAdapter.Fill(table);

Or there is some other option to get new value from AutoNumber field

You need to get the MAX value in the autonumber field (assuming the
autonumber is counting forward, otherwise use a MIN function). In Access
VBA, it would look like this:

DMax("Some_ID", "tblSomeTable")

In an ADO.NET DataSet, it would look like this:

int pid = (int)dtPrj.Compute(maxPrjID, null); //.NET 2.0 only

in the above example, maxPrjID is a string constant:

"MAX(Project_ID)"

You need to use whatever MIN or MAX function is available in your situation.

 

[Jim Rand]

Assuming you have configured your data adapter with the following INSERT statement:
INSERT INTO Categories(CategoryName, Description) VALUES (?, ?)

And now you need the value of the autoincrement key (CategoryID):

Add the following event handler in the class constructor:

daCategory.RowUpdated += new OleDbRowUpdatedEventHandler(daCategory_RowUpdated);

Which is then defined as:

private void daCategory_RowUpdated(object sender, OleDbRowUpdatedEventArgs e)

{

if (e.StatementType == StatementType.Insert)

{

OleDbCommand cmd = new OleDbCommand("SELECT @@IDENTITY",daCategory.SelectCommand.Connection);

e.Row["CategoryID"] = (int) cmd.ExecuteScalar();

}

}

 

[Guest]

Thanks deko

 

[Guest]

Thanks Jim

 

[Otis Mukinfus]

Hello

I have MS Access table with autonumber PK field

I insert record and perform dataAdapter.Update(table).
Now I need to know value of AutoNumbered field.

Do I have to refresh whole table, by doing
= table.Clear();
- dataAdapter.Fill(table);

Or there is some other option to get new value from AutoNumber field

Thanks
Milan

Very simple. SELECT @@IDENTITY in a command immediately following the insert
command will return the last auto number assigned.

If you need help setting up the commands let me know.

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com

 

[deko]

Assuming you have configured your data adapter with the following INSERT

statement:
INSERT INTO Categories(CategoryName, Description) VALUES (?, ?)

And now you need the value of the autoincrement key (CategoryID):

Add the following event handler in the class constructor:

daCategory.RowUpdated += new
OleDbRowUpdatedEventHandler(daCategory_RowUpdated);
Which is then defined as:
private void daCategory_RowUpdated(object sender, OleDbRowUpdatedEventArgs e)
{
if (e.StatementType == StatementType.Insert)
{
OleDbCommand cmd = new OleDbCommand("SELECT
@@IDENTITY",daCategory.SelectCommand.Connection);
e.Row["CategoryID"] = (int) cmd.ExecuteScalar();
}
}

So what you're saying is there's a RowUpdated property of the OleDbDataAdapter
(also of the SqlDataAdapter, I assume. Perhaps even of the DataSet?). And you
can wire an event up to that property. And that event can contain code which
can execute a Command object. And I assume that SELECT command looks for a PK.
So it is actually an OleDbCommand that is used to get the newly added PK --
after you've identified both the type of SQL statement that's just been executed
and the row that has just been changed.

But you now have to test every statement that updates a row in the database, not
to mention creating and executing a command object to retreive the PK value.

I'm wondering if this is any better than simply finding the max value in the PK
column.

 

[Jim Rand]

If you are using SQL Server, you can just add an extra statement at the end
of the insert statement

For example:

INSERT INTO Customer (CompanyName, ContactName, ContactTitle, Address, City,
Region, PostalCode, Country, Phone, Fax) VALUES (@CompanyName, @ContactName,
@ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone,
@Fax); SELECT CustomerID, CAST(TS AS INT) AS TS FROM Customer WHERE
(CustomerID = @@IDENTITY)

and the data adapter will pull the primary key from the server.

Finding the Max value might be a little tricky if other users are also
inserting rows.

I think that Microsoft added @@IDENTITY to Jet to facilitate .NET
development against an MDB.

Assuming you have configured your data adapter with the following INSERT
statement:
INSERT INTO Categories(CategoryName, Description) VALUES (?, ?)

And now you need the value of the autoincrement key (CategoryID):

Add the following event handler in the class constructor:

daCategory.RowUpdated += new
OleDbRowUpdatedEventHandler(daCategory_RowUpdated);
Which is then defined as:
private void daCategory_RowUpdated(object sender, OleDbRowUpdatedEventArgs e)
{
if (e.StatementType == StatementType.Insert)
{
OleDbCommand cmd = new OleDbCommand("SELECT
@@IDENTITY",daCategory.SelectCommand.Connection);
e.Row["CategoryID"] = (int) cmd.ExecuteScalar();
}
}

So what you're saying is there's a RowUpdated property of the OleDbDataAdapter
(also of the SqlDataAdapter, I assume. Perhaps even of the DataSet?). And you
can wire an event up to that property. And that event can contain code which
can execute a Command object. And I assume that SELECT command looks for a PK.
So it is actually an OleDbCommand that is used to get the newly added PK --
after you've identified both the type of SQL statement that's just been ex ecuted
and the row that has just been changed.

But you now have to test every statement that updates a row in the database, not
to mention creating and executing a command object to retreive the PK value.

I'm wondering if this is any better than simply finding the max value in the PK
column.

 

[deko]

INSERT INTO Customer (CompanyName, ContactName, ContactTitle, Address, City,

Region, PostalCode, Country, Phone, Fax) VALUES (@CompanyName, @ContactName,
@ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone,
@Fax); SELECT CustomerID, CAST(TS AS INT) AS TS FROM Customer WHERE
(CustomerID = @@IDENTITY)

That looks like the way to go. It's not intuitive that the second command gets
the CustomerID of the record inserted by the first, but if that's the way it
works, great.

Finding the Max value might be a little tricky if other users are also
inserting rows.

good point. I don't even know if there is a MAX function that could be run from
managed code against an MDB (or any other database for that matter).

 

[Otis Mukinfus]

If you are using SQL Server, you can just add an extra statement at the end
of the insert statement

For example:

INSERT INTO Customer (CompanyName, ContactName, ContactTitle, Address, City,
Region, PostalCode, Country, Phone, Fax) VALUES (@CompanyName, @ContactName,
@ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone,
@Fax); SELECT CustomerID, CAST(TS AS INT) AS TS FROM Customer WHERE
(CustomerID = @@IDENTITY)

However, if you use @@IDENTITY you will get the last identity for global scope
in the database installation. which could be from an insert in a table
different than the table you just inserted into (if someone inserted into
another table a couple of ms after you did.

It is better to use
return Scope_Identity()
which will return the generated PK from the table you inserted into.

Here is a stored procedure that does this:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[sp_Book_Insert]
@Title varchar(100),
@Comments varchar(1000),
@DateRead datetime = null
AS
INSERT INTO
Book(
Title,
Comments,
DateRead
)
VALUES(
@Title,
@Comments,
@DateRead
)
RETURN SCOPE_IDENTITY()

[snip]

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com