Locking a table in the stored procedure

Hi,


I want to find out about locking a table. In a stored procedure, First we are selecting a maximum number and incrementing it and inserting into the table. The stored procedure is called many times in a minute. it is inserting the same number more than 1 time. I think this is happening because, before insert statement is running, the stored procedure is called again. I am trying to lock the table after select statement and release it only after the insert statement. Can anyone tell me where i can find the code for locks.

Thank you,

In order to use stored procedures, you must be running a database server, like SQL Server or Oracle. Instead of playing with an incrementing value, why not set up a field in the table with an Identity property (If SQL Server) or using a Sequence if Oracle?

I assume you are working with SQL Server correct? There have been tons of threads on this board regarding record locking..do a search :).

Here is one past thread to get you started regarding SQL Server transactions and row/record locking..it is similiar to the situation you have provided here. This is will give you a better understanding of what you have to do:

http://www.visualbasicforum.com/showthread.php?t=183487