Read number, increment and save without duplicates


Every time I make an invoice i select the last number(folio) from a database  with avaliable numbers and increment by one that number right away. It works 99% of the time, however sometimes I get two invoices with the same number. What is worng in my code that let that happend?


if trim(ucase(CF_ESTADO)) = "I" or trim(ucase(CF_ESTADO)) = "C" then
txt_idk = " and CAF_Idk = '100'"
else 'productivo
txt_idk = " and CAF_Idk = '300'"
end if

    'Created on: 5/14/2011 10:52:06 PM
    'By: Jorge Silberstein -

set conn=server.createobject("adodb.connection")
' **********************************************
' CONECTION STRING *****************************
     <!--include file="Sp5/vamos_usuario.asp"-->
' **********************************************
' ********************************************** vamos

set rs=server.createobject("adodb.recordset")

sqlstring="SELECT TOP 1 folio,id_folio,folio_final,rec_caf_completo from Z_HIBRID_CAF where CAF_TD='" & tipo_dte & "' and CAF_RNGH>=rango_actual " & txt_idk & " order by CAF_RNGD"
response.write sqlstring & "<br><br>" sqlstring,conn,1

while not rs.eof

folio = rs("rango_actual")
id_folio = rs("id_folio")
folio_final = rs("caf_rngh")


sqlstring = "update Z_HIBRID_CAF set rango_actual = rango_actual + 1 where id_folio ='" & id_folio & "'"
Set RS = Conn.Execute(sqlstring)

set rs = nothing

response.write sqlstring & "<br><br>"
response.write "folio:" & folio
response.write "tipo_dte: " & tipo_dte

if cntfolio=0 then
response.cookies("msg")="Error - No hay folios " & errorin & " de " & tradoc(tipo_dte) & " 'disponibles' o 'en uso'."
response.redirect "caja2.asp?id_compra=" & id_compra
end if


Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
You're running into a race condition.  The better way to do that is to set invoice number as an identity column.

When you create a new invoice, insert as you normally would (not specifying a number for the invoice number).  To get the invoice number for that query after you run the insert do a select @@Identity.
Paul MacDonaldDirector, Information SystemsCommented:
"What is worng in my code that let that happend?"
Presuming for a second that there isn't more than one system hitting that table at once, is it possible someone (or some process) is deleting records, thereby allowing duplicates to be created?

BY FAR your best bet is to stop manually incrementing that column, and start using the auto-increment field capability of SQL Server.
jsbxAuthor Commented:

Auto Increment is not possible, i have to read the next number from a table and mannaully increment by one. The system is being hitted by several pc's at the same time.

I was hopping some kind of: lock -> select -> update -> Unlock method and since the table is very small it will not be noticed.

IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Paul MacDonaldDirector, Information SystemsCommented:
"Auto Increment is not possible"
Why not?

We insert a record, then read the identity column of the record that was just inserted.  This sidesteps all the problems you're having, and it doesn't matter how many systems are hitting the database at once.  You can even create a separate table to hold the incrementing value

INSERT INTO DisposableNumber (AnyColumn) VALUES (1)
SELECT  @invoice_number  = @@Identity
DELETE FROM DisposableNumber
Kyle AbrahamsSenior .Net DeveloperCommented:
Auto Increment is not possible, i have to read the next number from a table and mannaully increment by one. The system is being hitted by several pc's at the same time.

This is precisely why you WANT auto-increment.
jsbxAuthor Commented:
I'm given packages of numbers, for example:

1 - 800
1500 - 1700
950 - 1200

I have to autoincrement until i reach the last one, 800
then i will have to start with 950 until i reach 1200
then 1500 to 1700... etc...

So in my understanding i'm forced to discover which package is "in use", get a number from there and increment one.

Can i use auto increment for this help with syntax is needed

Scott PletcherSenior DBACommented:
You need to lock the table when you read the last value before you increment it (that is, add a "WITH (NOLOCK)" hint to the code that gets the last rango_actual value).  It's best to do all in a single SQL statement, to keep the locked time as brief as possible, but you can use separate statements as long as they are in the same transaction.  That is only way to guarantee that two such reads/increments cannot happen at the same time.
Dave BaldwinFixer of ProblemsCommented:
I use a unique customer code as part of my invoice ids.  If each invoice is for a different customer, that will take care of the problem.  I also use Date/Time as part of the invoice id.  There are a lot of ways to get around the unique id problem.
Scott PletcherSenior DBACommented:
@Dave Baldwin:
Couldn't the same customer place two orders at the same time, esp. a (very) large customer, possibly even from different parts of the country?
Dave BaldwinFixer of ProblemsCommented:
Well of course, Scott.  But if that were the case, I suspect this problem would have already been solved.  There are many ways to solve this problem, that's what I'm suggesting.  And since I don't know the askers actual situation, I'm not suggesting a single solution because I have no way of knowing whether it would work.
jsbxAuthor Commented:
@ScottPletcher what you suggest in a single statement is right what i need.

Can you please give me a code example of how to use "WITH (NOLOCK)" and "Single statament" that will increment one?

Scott PletcherSenior DBACommented:
I only know the SQL side, not the coding side, but something roughly like this, based on what you've posted above:

sqlstring = "update Z_HIBRID_CAF set rango_actual = rango_actual + 1 where id_folio = (SELECT TOP (1) id_folio from Z_HIBRID_CAF WITH (TABLOCK) where CAF_TD='" & tipo_dte & "' and CAF_RNGH>=rango_actual " & txt_idk & " order by CAF_RNGD)"

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
I know th subject of using IDENTITY columns has been clarified, but just to emphasize: Using an IDENTITY column for an invoice number is not a good idea as in some countries (I suspect the author comes from one of those) for legal reasons you cannot have any missed numbers.  Since 2012 the IDENTITY column can have gaps every time your restart the server, unless the counter is reset.

So we need to be careful about recommending solutions that use the IDENTITY column for anything other than surrogate keys and especially not for invoice numbers.
jsbxAuthor Commented:
thank you very much for the answer and excuse me for the delay, i totally forgot about this.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.