Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Classic ASP Professional For advanced coder questions in ASP 3. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Professional section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 5th, 2006, 11:36 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default Get most recently inserted ID code

My objective is to get opinions on the method I use to get the most recently inserted ID. This ID is an auto genrated SQL Server integer primary key field.

SQL = "INSERT INTO...;"
conn.execute(sql)
sql = "SELECT TOP 1 ID FROM tbleName ORDER BY ID DESC;"
set getInfo = conn.execute(sql)

Is it possible for another insert to occur in between these two statements? Please indicate if your reply is from experience or best guess/asumption.

IMO: I believe it may be possible however I am yet to come accross someone who has actually seen it happen and I can not manage to make this occur (I have tryed)

Secondly does anybody have a suggestion for improvement? I do not wish to use an SP, I like my systems to be portable with Access with as little change as possible. I also dont mind the fact that I am executing two queries.

TYIA

Wind is your friend
Matt
__________________
Wind is your friend
Matt
 
Old March 7th, 2006, 07:09 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

mmmmmm, not even any opinions?

Wind is your friend
Matt
 
Old March 9th, 2006, 07:59 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

why not use @@identity? its supported in Jet 4 so u can use it for access and sql server
 
Old March 9th, 2006, 08:33 AM
Registered User
 
Join Date: Mar 2006
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Pravin kumar
Default

"Select @@IDENTITY"
will retrieve the latest inserted record.
use this query just after the execution of insert statement.

 
Old March 9th, 2006, 08:57 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

Thankyou for your replies. I was unaware of this syntax.

My question still remains:

"Is it possible for another insert to occur in between these two statements?" May I add weather using the method I use or this new 'Select @@IDENTITY' method

Wind is your friend
Matt
 
Old April 28th, 2006, 04:16 PM
Authorized User
 
Join Date: Sep 2003
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

why not using the seesion id to make sure the id was last created by this session?

 
Old April 29th, 2006, 02:26 AM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

;;why not using the seesion id to make sure the id was last created by this session?

I dont understand what question you are advising on. Sorry, I cant see how it relats to my primary un-answered question.

FYI : am not looking for another way carry out a process, I am looking for advise on the one illustrated above and input on the primary question.

Wind is your friend
Matt
 
Old April 29th, 2006, 06:30 PM
Authorized User
 
Join Date: Sep 2003
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

read this

http://www.aspfaq.com/show.asp?id=2174

Please next time try to search your question on google, you might find your answer.
 
Old April 29th, 2006, 10:19 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

You are interesing character. your orignal post said (got emailed):
-------------------start----------------------------
Okay,

I do not tried my self.

but if statment

sql = "SELECT TOP 1 ID FROM tbleName ORDER BY ID DESC;"

you may got the wrong id.

if statment

sql = "SELECT TOP 1 ID FROM tbleName WHERE SID='Session.SessionID'
ORDER BY ID DESC;"

That will not have any problem with it.


-------------------finish---------------------------

FYI NO IT WILL NEVER GET THE WRONG ID using my method, why do you think this? The ID field is an auto incrimenting integer (like I illustrated in the very first post)

My question was never how to get the most recently inserted ID it was asking if another insert could occur between the two statements. I do not wish to insult anybodys inteligence by repeating myself, its all clearly explained in my very first post. People seem to want to suggest different ways of getting this ID. Although I appreciate this, its clearly not what I am looking for.

Then you tell me to ask google. Have a look at how much time I spend here assisting others (probably you some time in the future) youve got a check....

;;;you might find your answer.
No I did not, thats why I posted it here. Can you find the answer? I think you have lost track of what this post is about and what the primary question is

Wind is your friend
Matt
 
Old April 29th, 2006, 11:21 PM
Authorized User
 
Join Date: Sep 2003
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You would better to asking the sql companies that question instant.

because it depends how the sql been written. would it processed which ever the request by time when request or it been processed request by each connection.

If it's proccesed request by the time when request, then it's possible to have another id.

If been processed request by each connection then you will have unique id.

so asking somewhere else.




Similar Threads
Thread Thread Starter Forum Replies Last Post
How to Grab ID of a recently "inserted item" rsearing ASP.NET 2.0 Professional 11 February 15th, 2007 03:45 PM
Last record inserted ID ADAC Programming VB Databases Basics 1 June 5th, 2006 02:41 PM
Face ID Code not working for me kencjohnson Excel VBA 0 December 29th, 2005 08:35 PM
Getting ID from inserted data in Access chrscote Classic ASP Databases 6 June 23rd, 2005 02:09 PM
View files which were recently modified marcin2k Access VBA 25 February 4th, 2005 10:46 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.