Read number, increment and save without duplicates

Hi,

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'"
errorin = "DEMO o CERTIFICACION"
else 'productivo
txt_idk = " and CAF_Idk = '300'"
errorin="PRODUCTIVOS"
end if

    'FOLIOS // ESTAS YA ESTABAN COMENTADAS
    'SELECT SCRIPT - SUPERDOOPER 5
    'Created on: 5/14/2011 10:52:06 PM
    'By: Jorge Silberstein - www.interchile.com

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

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

' GET THE LAST FOLIO
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>"

rs.open sqlstring,conn,1

while not rs.eof
CNTfolio=CNTfolio+1

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

rs.movenext
wend
rs.close

' INCREMENT BY 1
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
response.write "REC_CAF_COMPLETO: " & REC_CAF_COMPLETO
'response.end

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

'response.end
%>

Open in new window


Thanks!
jsbxAsked:
Who is Participating?
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.
0
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.
0
jsbxAuthor Commented:
Hello,

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.

thanks
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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
0
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.
0
jsbxAuthor Commented:
I'm given packages of numbers, for example:

1 - 800
1500 - 1700
950 - 1200
etc...

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

thanks.
0
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.
0
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.
0
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?
0
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.
0
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?

thanks
0
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)"
0

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.
0
jsbxAuthor Commented:
thank you very much for the answer and excuse me for the delay, i totally forgot about this.
0
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
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.