Hi to everyone.

I have an vb app that generates a taxi tickes.
This tickets are generated in different modules (or stands).

I am using SQL SERVER as database.

The tickets are identified by a unique number ( folio ).

The problem is the concurrency, when many tickets are generated
at the same time.

Actually, i make a stored procedure that generated the ticket.
The table of the ticket have a restriction in the number field ( unique restriction).


The stored procedure is like this:

Create Procedure CreateTicket
as

DECLARE @intNextFolio int,
. . . . bla bla bla

SET @errInsertar = 2323
SET @serieActual = (SELECT SERIE FROM CONFIGURATION)



WHILE (@errInsert <> 0)
BEGIN
/* Load the actual folio and increments by one */
SET @intNextFolio = (select actualfolio from CONFIGURATION) + 1

/* Insert the ticket with the next folio */
INSERT INTO Tickets . . . . bla bla bla


/* Check that if there is an error in the insert */
SET @errInsert = @@ERROR

/* No errors, insert success */
IF @errInsert = 0
BEGIN

/* Update the configuration with the last inserted folio */
UPDATE CONFIGURATION SET FOLIO = @intNextFolio
SET @@NewFolio = @intNextFolio
END


END /**** WHILE ****/




I believe that the SQL server with handle the error, but when an error hapens, vb stops and send a message that say somethig like this "Error in an IX_Folio, there a duplicate record (or something)" and the aplication fails.

Questions:
1) What can i do ?
2) Which other way recommends to handle the concurrency ?
3) What can i do to make that the SQL Server handles the error and continue, without sending it to the vb app ?
4) i think the best way to handle this is locking the tables, and unlock the table when the record is inserted, but i do not how.


Thanks