troubleshooting Question

database independent last inserted row

Avatar of maXXXeE
maXXXeE asked on
ASP.NET
16 Comments2 Solutions463 ViewsLast Modified:
Hi

I have a small web application, which inserts data into a database. A column in one of the table is an auto incremented id. Once I insert a row, I need to get the id just inserted to continue with other tables. So far so good, nothing complex and it is something I have done it before. In mysql there is a function LAST_INSERT_ID() get this. I always use database specific function to do this to avoid problems and better thread safety.

But the other day my client instructed me to keep the code database independent. Because in few months time he is planning to change his hosting server, and go in for MSSQL or Oracle for his other applications, so this app will be moved too.

Question:
So, I have to move the id retrieval logic to application instead of the database.
What is the best database neutral way to get the last inserted auto incremented Id in a thread safe manner in ASP.NET (1.1 or 2.0)?

Note:
My database inserts in general will be as below: table1 contains the column which is autogenerated(columnname : ID)
insert into table1 (Password) values ('password');
-> I have to get my last inserted id here
insert into table2 (ID, email, tel) values ('id','email','tel')
ASKER CERTIFIED SOLUTION
GaryFrancisLond

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 16 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 16 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros