asked on

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!

Kyle Abrahams, PMP

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 MacDonald

"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.

jsbx

ASKER

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

Paul MacDonald

"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 Abrahams, PMP

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.

jsbx

ASKER

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.

Scott Pletcher

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 Baldwin

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 Pletcher

@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 Baldwin

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.

jsbx

ASKER

@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

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

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.

jsbx

ASKER

thank you very much for the answer and excuse me for the delay, i totally forgot about this.