losing autonumbers in Access

[hangalot]

Hi all


I recently did a commerce site, and its fairly straight fwd. From swf i post to an asp page, which in turn communicates with a dll bla bla bla.

my problem is that, every now and again i lose an autonumber in my table that contains the orders. for example they will go from 112, 113, 115. and the order is not missing since i pick it up during testing, and know that i did not olose an order.

this is severly disconcerting however as can be imagined.

any ideas

[jaybee]

you're using access on a commerce site? good luck with that let's hope their product is an unpopular one cos if not your site will be in trouble .....

re yr prob, from what I remember I think autonumbers only claim to be unique not strictly sequential - does this matter?

[hangalot]

all that is needed.

that is the problem. the klient just logs their requests in it, and wish the id to serve as their consequential OrderId.

thnx

[jaybee]

you could generate the ID yrself using a maxID type setup...I don't use asp but in pseudocode:

[transaction]

[query]
SELECT max(ID) as maxID
FROM table
[/query]

[query]
INSERT INTO table VALUES(maxID+1,column1, column2)
[/query]

[/transaction]

[hangalot]

is busy doing.

[jaybee]

huh?

[freddycodes]

First off like jaybee said Access is not a database that should be used for an ecommerce site. Secondly an auto-incrementing column should not be used to keep track of orders. It should only be used as a key to uniquely identify a row in the table and that is it. If you need a sequential number you should do as jaybee stated and store a sequence in another table, where you increment that number everytime an order is placed. But since Access doesn't do table locking and that sort of stuff, hopefully more than one person doesn't order at the same time.

[hangalot]

really u guys r thinking 2 big. this is an inhouse commerce site 2 order our stationary from a printer, and 2 keep log of these orders and against what cost center it is allocated. no ssl, no transaction, nice simple log to ease a previous administrative irritation