Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Connection objects and last_insert_id() help !

Posted on 2004-04-17
3
411 Views
Last Modified: 2012-05-04
Hey everyone,

I'm ripping my hair out over this problem:

I have a Insert statement, and i want to retrieve the last_insert_ID() after it has executed so it goes like this:

SQLINS = "INSERT INTO family_albums (category,fam_ID,album_name,hidden,system_album,in_progress,user_ID) values(null,'"&request.cookies("access")("family_ID")&"','"&Upload.Form("Albumselect")&Upload.Form("Albumcreate")&"',1,0,1,"&request.cookies("access")("user_ID")&")"
objCmd.CommandText = sqlins
objCmd.Execute
RS1.open "SELECT LAST_INSERT_ID() AS `album_ID`" , "DSN=xxxx; uid=xxxx;pwd=xxxx"
session("varalbum_ID") = RS1("album_ID")
RS1.close

That returns a RS1("album_ID") of 0.

HOWEVER, when i do
SQLINS = "INSERT INTO family_photos (fam_ID,addedby_user,album_ID,file_name) values("&request.cookies("access")("family_ID")&","&request.cookies("access")("user_ID")&",LAST_INSERT_ID(),'"&file.ExtractfileName&"')"
objCmd.CommandText = sqlins
objCmd.Execute

the last_insert_ID() works (HOWEVER, I can't use it because it is in a loop and would increment itself (when i dont want it to)

I think i've answered my own question.. but i still dont know how to make it work.
I'm asuming the problem is that i'm opening a whole new connection object and it's not finding any last inserted id's.

So my question if the above is right, how would i do a "SELECT" on a objcmd and return a value in a RS("") fashion ?

Thanks for any insight!\

Chris
0
Comment
Question by:garfield1979
3 Comments
 

Accepted Solution

by:
jameswalt earned 200 total points
ID: 10850723
I've had this problem many times. Since an insert does not have any return values, we could always execute a query such as follows:
This is the main problem with using autoincrements as primary keys (that and the additional storage required)

CONNECT.EXECUTE INSERT STATEMENT
RS1.OPEN "SELECT Max(album_ID) AS lastID FROM family_albums"

Response.write RS1("lastID")


I hope this helps - it's pretty much what you supposed, but a more clear solution.
0
 

Author Comment

by:garfield1979
ID: 10850752
Thanks James.

That solution wil work, i'm at nerves end and i'm about to throw this system against the wall..

:-D

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10851342
The main problem with this approach is performance and in a multi-user environment you could pick up the wrong value.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question