Need a Way to Show Autonumber when Inserting

@jdeline,

Yes, it certainly explains a lot of the pitfalls most of us discovered the hard way ..

> Does anyone see any pitfalls or is it a decent idea?

Duplicates are one issue, security is another. Does your application have some sort of login mechanism or are you just requiring ad ID and PIN # to display the edit screen?

BRILLIANT! The last piece of the puzzle just clicked with me when I read original_somedude 's posting. Although, knowing this now, cf_dev2 's code example has this concept in it.

Thanks again everyone ...I think I'm ready to roll on this. All of you are very kind for taking the time to help.

Tune in later for ....PHOTO POSTINGS!! (scary music) Dum DUM Daahhhhhhh.

Good link, cf_dev2. http://mysecretbase.com/get_the_last_id.cfm should be required reading before someone posts on this forum. Not only does it explain what the problem, it shows how most "fixes" are unreliable.

Dan Bracuk wrote:
> We do something similar when creating temporary tables, just in case we
> get a duplicate name.

Always good to have a backup plan. Expect the unexpected.

Or you could Select the AutoID number where it = the UUID, then give users that.

That is of course assuming that they need more than the ID number to access content. Otherwise, if my ID is say, 1337, I am probably going to get curious and see what happens if I use 1336 or 1335.

You ppl are AWESOME! Thank you!!!! That works! Being a Newbie, I didn't know of a UUID. I laughed out loud when I read that it's a unique identifyer for every machine in existance! ...what a deal!

Anyway, there's one problem with using it. In my classified ad website, I ask the user to enter their ad number to edit it ...a 35 digit hex number? I don't think it'll be user friendly.

So here what my idea is: Select the MAX(ID) as newestID and add a random 4 digit number after it.

Does anyone see any pitfalls or is it a decent idea?

You may say that with a 4-digit random number, every 10,000 entries may have duplicate ad numbers. But it would only be every 10,000 WHERE simultaneous selections of the max(ID) occured, which may only happen every 1000 ads. And even if it does, the user selects a PIN# of their own to edit the ad so duplicate ad numbers wouldn't be a true problem for me, since there's also be a 4 digit pin number associated with the record.

Thanks again! -Tony

You can use something else for your primary key. You can use Cold Fusion to create a UUID and then use it.

Or, you can expand on the select max() query by having a really long where clause.

Thanks for the reply ...Yes, more than one person could query the max(id) at once and it wouldn't be reliable.

So, how do I "attribute the unique identifier to the client before saving to the database?"

That actually was my thought but I don't have any ideas on how to do this. I'm tempted to just ask the user to look up their newly-posted ad and write down the ad number but that would be very amatur-ish.

If it is an integer auto increment field, id, you can do the query

select max(id) as newestID
from tbl

However, how will you guarantee that someone else will not do a posting just before I get my number? The better strategy might be to attribute the unique identifier to the client before saving to the database.