Duplicate order nums in Orders

Hi all,
We have a third party application that allows users to take
customer orders. This application has all the bells and
whistles of the Titanic. Since we wanted our customers to
be able to place orders themselves, we built a smaller
light weight application to allow them to do just that.
This was built using VB6, SP5. The back end is SQL Server
2000.
When an order is taken an OrderNumber is assigned to it.
The problem that I am having is that sometimes the order
numbers duplicate. That is, two orders have the same number.
This only happens when the the main order system and the
small order app both insert a new order at almost the same
time (normally 1 second apart).
When developing the smaller order app I built the following
TSQL transaction to avoid this from happening:
begin transaction
declare @.newConfirm int
declare @.maxNum int
--here I get the new order num
select @.newOrderNum=CurrentSeqNum,@.maxNum=Maxim
um
from SeqNumbers where Id='OrderNumber'
--the order num cycles, so if it reached the limit start over
if @.newOrderNum = @.maxNum
update SeqNumbers set CurrentSeqNum=Minimum
where Id='OrderNumber'
Else
update SeqNumbers set CurrentSeqNum=CurrentSeqNum+1
where Id='OrderNumber'
--I insert the data pertaining to the order
insert into trnOrders (AccountId,Received,Shipped,Amount,Order
Number)
values('69M033',getdate(),'2005-06-13',2300,@.newOrderNum)
--I retrieve the order number
select @.newOrderNum as OrderNum
commit transaction
The above should protect the smaller app from getting duplicate
order numbers when more than one order is placed at the same time
using the smaller app.
How can I protect against there being duplicate order numbers due
to orders being placed at the same time by the other application? Any
ideas what could be causing this? Is the process that I am following
robust enough to protect against this?
Any advice is greatly appreciated, thanks!
SagaThe first thing is to put a primary key constraint in the order table to
avoid duplicated order numbers,o no matter how many applications are they
using to enter orders.
You can create another stored procedure to get the last number to be used.
create procedure dbo.usp_next_order_number
@.next_order_number int output
as
set nocount on
update dbo.SeqNumbers
set @.next_order_number = CurrentSeqNum = CurrentSeqNum + 1
where [Id]='OrderNumber'
return @.@.error
go
and call this sp from yours.
create procedure...
@.newOrderNum int output
as
set nocount on
begin transaction
declare @.newConfirm int
--declare @.maxNum int
--here I get the new order num
-- select @.newOrderNum=CurrentSeqNum,@.maxNum=Maxim
um
-- from SeqNumbers where Id='OrderNumber'
--the order num cycles, so if it reached the limit start over
-- if @.newOrderNum = @.maxNum
-- update SeqNumbers set CurrentSeqNum=Minimum
-- where Id='OrderNumber'
-- Else
-- update SeqNumbers set CurrentSeqNum=CurrentSeqNum+1
-- where Id='OrderNumber'
declare @.rv int
declare @.error int
exec @.rv = dbo.usp_next_order_number @.newOrderNum output
set @.error = coalesce(nullif(@.rv, 0), @.@.error)
if @.@.error != 0
begin
rollback transaction
raiserror('Error getting next order number.', 16, 1)
return -1
end
--I insert the data pertaining to the order
insert into trnOrders (AccountId,Received,Shipped,Amount,Order
Number)
values('69M033',getdate(),'2005-06-13',2300,@.newOrderNum)
-- --I retrieve the order number
-- select @.newOrderNum as OrderNum
commit transaction
go
You need to put more code in your sp to check errors.
AMB
"Saga" wrote:

> Hi all,
> We have a third party application that allows users to take
> customer orders. This application has all the bells and
> whistles of the Titanic. Since we wanted our customers to
> be able to place orders themselves, we built a smaller
> light weight application to allow them to do just that.
> This was built using VB6, SP5. The back end is SQL Server
> 2000.
> When an order is taken an OrderNumber is assigned to it.
> The problem that I am having is that sometimes the order
> numbers duplicate. That is, two orders have the same number.
> This only happens when the the main order system and the
> small order app both insert a new order at almost the same
> time (normally 1 second apart).
> When developing the smaller order app I built the following
> TSQL transaction to avoid this from happening:
>
> begin transaction
> declare @.newConfirm int
> declare @.maxNum int
>
> --here I get the new order num
> select @.newOrderNum=CurrentSeqNum,@.maxNum=Maxim
um
> from SeqNumbers where Id='OrderNumber'
> --the order num cycles, so if it reached the limit start over
> if @.newOrderNum = @.maxNum
> update SeqNumbers set CurrentSeqNum=Minimum
> where Id='OrderNumber'
> Else
> update SeqNumbers set CurrentSeqNum=CurrentSeqNum+1
> where Id='OrderNumber'
> --I insert the data pertaining to the order
> insert into trnOrders (AccountId,Received,Shipped,Amount,Order
Number)
> values('69M033',getdate(),'2005-06-13',2300,@.newOrderNum)
> --I retrieve the order number
> select @.newOrderNum as OrderNum
> commit transaction
>
> The above should protect the smaller app from getting duplicate
> order numbers when more than one order is placed at the same time
> using the smaller app.
> How can I protect against there being duplicate order numbers due
> to orders being placed at the same time by the other application? Any
> ideas what could be causing this? Is the process that I am following
> robust enough to protect against this?
>
> Any advice is greatly appreciated, thanks!
> Saga
>
>|||Thank you for your reply. I will test using your idea. One thing I
cannot
do is place the constraint on the table because this db belongs to the
application that was purchased, so I can't make this kind of
modification
to it without risking the functionality of the main application. We once
added a field to one of the tables and the main application stopped
working.
Apparently, it validates the tables in some way.
Thanks again
Saga
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
message news:033FC598-10FC-45F6-B1D0-74B57BF9B329@.microsoft.com...
> The first thing is to put a primary key constraint in the order table
> to
> avoid duplicated order numbers,o no matter how many applications are
> they
> using to enter orders.
> You can create another stored procedure to get the last number to be
> used.
> create procedure dbo.usp_next_order_number
> @.next_order_number int output
> as
> set nocount on
> update dbo.SeqNumbers
> set @.next_order_number = CurrentSeqNum = CurrentSeqNum + 1
> where [Id]='OrderNumber'
> return @.@.error
> go
> and call this sp from yours.
> create procedure...
> @.newOrderNum int output
> as
> set nocount on
> begin transaction
> declare @.newConfirm int
> --declare @.maxNum int
> --here I get the new order num
> -- select @.newOrderNum=CurrentSeqNum,@.maxNum=Maxim
um
> -- from SeqNumbers where Id='OrderNumber'
> --the order num cycles, so if it reached the limit start over
> -- if @.newOrderNum = @.maxNum
> -- update SeqNumbers set CurrentSeqNum=Minimum
> -- where Id='OrderNumber'
> -- Else
> -- update SeqNumbers set CurrentSeqNum=CurrentSeqNum+1
> -- where Id='OrderNumber'
> declare @.rv int
> declare @.error int
> exec @.rv = dbo.usp_next_order_number @.newOrderNum output
> set @.error = coalesce(nullif(@.rv, 0), @.@.error)
> if @.@.error != 0
> begin
> rollback transaction
> raiserror('Error getting next order number.', 16, 1)
> return -1
> end
> --I insert the data pertaining to the order
> insert into trnOrders (AccountId,Received,Shipped,Amount,Order
Number)
> values('69M033',getdate(),'2005-06-13',2300,@.newOrderNum)
> -- --I retrieve the order number
> -- select @.newOrderNum as OrderNum
> commit transaction
> go
> You need to put more code in your sp to check errors.
>
> AMB
> "Saga" wrote:
>