Create a SKU # 2

[A1Pat]

Hi all,

I'm working on creating a sku# that combines text and auto-incremented number from db.

When a new product is added to db, new ID is auto created. At the same time, I want a sku, for ex: XXX+ID, is auto created as well. As u can see, the SKU # = XXX and ID#. My problem is ID# only be created after saved into db and I can't seem to find a way to create SKU w/o ID at the time added.

Could someone show me how? Thank you!!!

 

[ChrisHirst]

2 ways I can think of quickly;

1/ insert the new record, query the db for MAX(id), create your SKU, update the record.

2/ query the db for MAX(id), increment the variable, create the SKU, insert the record.

both are capable of getting the wrong id if multiple users are updating the db and two records are added simultaneous ly. There will be more reliable methods but at 8PM on Friday it's the best I can up with.


Chris.

Indifference will be the downfall of mankind, but who cares?

http://www.candsdesign.co.uk

A website that proves the cobblers kids adage.

http://www.cram-system.com

Nightclub counting systems

So long, and thanks for all the fish.

 

[fattyfatpants]

you could use a MAX function and add 1 to it


rsRecordset.open "SELECT MAX(Id) AS MaxId FROM table"

intId = MaxId + 1

strSKU = XXX & intId

 

[Genimuse]

Much, much safer is to use an application variable.

In the global.asa file, in the application on start section, open the database and select the Max(ID) as suggested above. Then set an Application variable -- let's call it Application("SKU") -- to that value.

Now when you need a new one, call:

Application.Lock
Application("SKU") = Application("SKU") + 1
MySKU = Application("SKU")
Application.Unlock

Then write the record with MySKU as your SKU.

This will ensure that you never create a duplicate ID.

 

[vbkris]

>>both are capable of getting the wrong id if multiple users are updating the db and two records are added simultaneous ly. There will be more reliable methods but at 8PM on Friday it's the best I can up with


a workaround would be to use auto generated id by database itself and get the id by:
select @@identity as Id

the above sql will give the automatically generated id.

Known is handfull, Unknown is worldfull

 

[ToddWW]

I have never created a duplicate ID with this method. I put this in an include file that is called at the top of every relevant page.

'''''''''''
'UNIQUE ID'
'''''''''''
dim uId
dim mvar
dim dvar
mvar = Trim(Cstr(Month(Now)))
dvar = Trim(Cstr(Day(Now)))
IF Len(mvar) = 1 THEN
	mvar = "0" & mvar
END IF
IF Len(dvar) = 1 THEN
	dvar = "0" & dvar
END IF
uId = Right(Cstr(Session.SessionID),3) & Year(Now) & mvar & dvar & Timer
uId = Replace(uId,".","")

Basically it uses the format SSSYYYYMMDDTTTT

Where:

SSS is the last three characters of the users Session ID.

YYYYMMDD is today's date

TTTT is the ASP Timer method which returns the number of seconds since midnight to the hundredth of a second. It will place a decimal in there which I remove.

Basically, in order to create a duplicate ID here, (2) or more users would have to access that code at the exact same hundredth of a second on the same day. To account for that rare possibility, I place that last three characters of the users SessionID in front.

It creates an ID that can be from 12 to 20 characters long.

The potential problem with using the Application Variable is that it could cause problems if you restart IIS or restart your web server. You could manage that, but it might be painful.

ToddWW

 

[Genimuse]

There's no problem with the application variable at all. In the On Start it's pulling the current highest number from the database and adding one to it, so before the very first page is served (after restart) it's automatically got the highest number. You could unplug the machine at random intervals and you'd still never end up with the same number.

Plus you can control the format of the number -- it's not that it's just unique, it's also sequential and can start as small as you want.

It's one thing to generate a unique ID, and your method sounds good, but that's a different thing than an incrementing number.

(@@identity is another good solution if you don't need the number until the insert is complete.)

 

[ToddWW]

Agreed. Great point. I like your method better.

 

[Genimuse]

Thinking about it, though... you're right, you'll want to use sessions for your users, and make them start over if their session expires/is invalid. Here's the scenario: User A gets the page, gets a new ID. User is slow or steps away from his computer. Server crashes, restarts. User B requests the page, gets the same session ID as User A, and boom, conflict.

If you require a current session and the server crashes the old session IDs will be automatically invalidated.

So there is a problem with it.

 

[ToddWW]

In that scenario, yes, it's possible for two users to get the same Session ID. However, appending the Timer value to the end of the unique ID ensures that the ID will still be unique since Timer returns the number of seconds to the 1/100th of a second from midnight.

So the first user might get an ID like this.

ABC-20040925-18523.14

Where ABC represents part of the session ID and 18523.14 represents the number of seconds since midnight.

The second user, even though the session ID might be the same would get a different ID like this.

ABC-20040925-18582.33

Since he got his ID at a different time than the first user.

The only way that the ID's would be the same is if User #1 got his ID, the system crashed, User #2 logs in and gets the same Session ID and retrieves his ID. This would all have to take place in the same 1/100th of a second. Very unlikely if not impossible.

ToddWW

 

[Genimuse]

Sorry, I was referring to my method, not yours. Mine has the flaw (and the fix).