Hi,
Here's what I know. The max function gives you the maximum value for that column. However, I use the @@identity to retrieve the value of the identity column with that row insertion so that if I want to I can go directly to the edit page in a redirect and pull up that just added row. You have to set the nocount value on to retrieve the identity. I don't remember the theory behind it except that SQL has a count of how many rows were affected with a transaction. You can see that when using SQL Query Analyzer. Notice the second tab after a transaction. It will tell you how many rows were affected. That functionality has to be turned off. Here's an example.
SQL = "Set nocount on; Insert into Table (Column1, Column2) Values (Value1, Value2);Select theID = @@identity;Set nocount off;"
Set oRS = oConn.Execute(SQL)
theID = oRS("theID")
This is in classic ASP.
Here's an example you can try in QA.
Create this table.
CREATE TABLE [Table1] (
[theID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[Column1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Column2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Then run this script in QA. You can see the value of the ID column in the just inserted row.
declare @theID integer
Set nocount on;INSERT INTO table1 (Column1, Column2) Values ('Richard', 'Jimmy');SELECT @theID = @@identity;SELECT @theID AS 'The ID';Set nocount off;SELECT * FROM Table1;
Its a good question, however, since running Select max(theID) from table1 gives you the same value but essentially you're getting that value in the same transaction using @@identity.
rstelma
|
