database independent last inserted row

insert into table2 (ID, email, tel) values ('id','email','tel')

will this not help?

select max(id) from table2

ashutosh9910 :
does max(id) guarantee than it is the id that is inserted by particular instance?  It just gives me the last id inserted by some instance.

vivekthangaswamy:
I am trying to perform this in application layer itself.
Will consider stored procedure if no other options exist.

Thanks anyway.

since id is the identity column as per your remark

Note:
My database inserts in general will be as below: table1 contains the column which is autogenerated(columnname : ID)

the max of id would always be the last inserted record.

Select max(id) woudl nto work because if you insert 10 records for example that have generated ID's 1 to 10 and then you delete the last 3 records, select max(id) will return 7 but the next identity could possibly be 11.

Creating a stored procedure will also not solve yur issue. Although the code that vivekthangaswamy has postd looks like it will work, this is not database independent it is Microsoft SQL specific.

I cannot think of a way that this can be done whilst remaining database independent without using a Data Access Layer that accesses an underlying class based on the database you are connecting to.This would basically jsut be hiding the database independence though as essentially you will still be calling database independent code to retrieve the identity value.

If anyone can come up with a better solution, i would be interessted to knwo though.

Regards,

Gary Francis

if you insert 10 records for example that have generated ID's 1 to 10 and then you delete the last 3 records, select max(id) will return 7 but the next identity could possibly be 11.

Until you insert Id 11 you have 7 as last row and max(id) would return 7

once you insert a new record id = 11 and max id would return 11 so whats wrong with that??

Could  u plz explain.. I guess this is what he wants.............

My apologies ashutosh9910. I wasn't reading the question/answers properly - that would work.

I have only really worked with access, sql and mysql - do all other databses (e.g. oracle) support the max(fieldname) command?

If they do, I would suggest that is the easiest way to get what is required.

Regards,

gAry Francis

yup works with ORacle as well.............

ashutosh9910:
Please clear my doubts regarding your solution as I am not an expert in sql.
As per your solution this should be my solution
SQL Line 1 :  insert into table1 (Password) values ('password');
--> asp.net code
SQL Line 2 :  select max(id) from table1;

Say the id inserted by one session is 20.
But between SQL Line, there might be few ASP.net code, which takes negligible time, but within that time, say few other sessions insert 5 more rows.
What will max(id) return. 20 or 25 ???

maXXXeE:
You asked a doupt to ashutosh9910 in previous post, in that situation the max ID concept fails. My solution will work fine on that situation, but i have given sample code for MS SQL server 2000, for more you can try for Oracle also.

even in that case max id should work fine and would be easier to implement by just making a small change to the table. Make another column containing UserId or something like AddedBy and filter the same accordingly.

Select max(id) from table2 where AddedBy = currentuser

thanx to all who have replied.
But if you read the question, i have mentioned "So, I have to move the id retrieval logic to application instead of the database".
Everyone provided me database solution.

Is it possible to use the transactions in asp.net to get over this problem?

ASP.Net does not use database independet code. Each type of database has a set of data access components that are all very similar but are specific to each data type e.g. SqlConnection will only work with sql, MysqlConnection will work with mysql OleConnection will work ole database/files etc.

For this reason there is no "one" command to find the identity value as you require different methods to retrieve this from each database.

The best "databse" solution that has been suggessted is the max() solution although as you have pointed out this is going to return incorrect values if records are added between when you record is added and the max() is called - which is a highly likely scenario if your db will be used with regular db calls.

There is only 1 real solution that I can think will work from an application stand point and that is to use a Data Access Layer where you can call generic commands - for example:

DAL objDal = new DAL(connectionString);
objDal.Connection.Open();
objDal.Connection,ExecuteCommand("insert into abc (x, y, z) values (fx, fy, fx)");
int ident = objDal.Connection.GetIdentityValue()

If you are going to go down this route you need to have some code that wil determine the type of db you are connecting to, and the associated sql commands that are supported by that database for thinsg like retrieving the identity. There are probably data access layers available to download alrady on the internet if you look around.

Regards,

Gary Francis

GaryFrancisLond :
Is it atleast possible to separate the queries from the code. Similar to what can be done in javaEE with hibernate?
That could solve my problem to some extent cos i dont have to make changes to the code.
 I am not looking for all in depth features of hibernate. Just something to separate the db queries from the code, preferable something which exists in asp.net 2 framework itself.
I hope you get the point.