Generating Unique Alpha-Numeric ID's?

[DarynRoberts]

Hi all...Im hoping someone can help us. For our project, we need to obviously create unique ID's in our tables as one always has to. But we want to include alphabetic characters. For example, in our landlord subsystem, it should generate LL002. Currently we simply just loop through the collection, add one more, and then concatenate it with the "LL". However, this seems to work sometimes and not others...so it doesnt seem very stable. Is there any other way to do it? oh- we using visual basic 2005 thanks

Dazz

[nlarson11]

did you try taking the (max) from the table and then add 1 to it?

[Guffa]

DarynRoberts wrote:

However, this seems to work sometimes and not others...

Standard question #1: What do you mean by "not working"?

--- single minded; short sighted; long gone;

[DarynRoberts]

Yes we did try take the max out, but then the problem comes in when a record is deleted. For example, if we have ten records but number five is deleted, then since there is now nine records, the next ID will be number 10. But number 10 still exists. We then tried to simply generate a random number but there is something wrong with our block of code as only the 3 same numbers keeps getting generated.

Dazz

[Mycroft Holmes]

Heres an idea, assuming SQL Server Add 3 fields to the table IDNo int Identity(1,1) Prefix char(2) default 'LL' TableID varchar(20) Add a calc in your insert proc to add prefix & IDNo together and stick into TableID. I think the IDNo may only be generated by the commit of the insert transaction. You can probably do the last step with a trigger but I loathe triggers so would not recommend it. OR do a select top 1 on the id, increment and save with the prefix, caveat this will be nasty on a high use system.

[Guffa]

Perhaps you should reply the original poster instead of me...

--- single minded; short sighted; long gone;

[nlarson11]

If you use the Max(column) to insert all your records then the behavior that you speak of wouldn't happen. Meaning, if you delete a record, the key for each record has not been altered. So if you take the Max(column) for the new record the answer will be LL010 still and you add 1 to it. So you will be inserting the new record with the key of LL011. Select Max(KeyID) From Table 'returns LL010 add 1 and insert new with LL011 You would only be inserting LL010 again if the user deleted LL010 otherwise adding one will always result in a non-duplicate key.

[DarynRoberts]

Just wanted to say thanks to everybody who gave input. ciao.

Dazz

[DarynRoberts]

thanks dude. that helped

Dazz