creating a record lock to ensure that only one user is updating records at a time

[rmvbgeek]

Help!
I have created an application using vb 6 that allows users (5) to create
orders by connecting to s sql database.
The only problem is that to often I am getting duplicate order numbers.

Here is the code:

adopagecounter.Open "select id,pagecounter,pagedate from Mops_counter",
db, adOpenDynamic, adLockOptimistic
dateformat = Format(adopagecounter.Fields("pagedate"), "ddddd")
If dateformat <> dateformat2 Then adopagecounter.Fields("pagedate") =
dateformat2: adopagecounter.Fields("pagecounter") = 1
mopscounter = adopagecounter.Fields("pagecounter")
adopagecounter.Fields("pagecounter") = mopscounter + 1
adopagecounter.Update
adopagecounter.UpdateBatch
adopagecounter.Close

I open the record set to get the next number
I then test the date of the record against the system date
If they do not match then the record counter is reset to one and the
date is reset to the system date.
I then make a variable equal to the pagecounter
After that I add one to the pagecounter and update the recordset and
then close it.

Through all of this something is left open and often times I get two of
the same order numbers.
I am at a loss of how this happens.
I would appreciate any ones input about this problem.
Thank You,
VBGEEK


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[MGFoster]

Use an SQL trigger to do the creation of the order number.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
rmvbgeek wrote:

Help!
I have created an application using vb 6 that allows users (5) to create
orders by connecting to s sql database.
The only problem is that to often I am getting duplicate order numbers.

Here is the code:

adopagecounter.Open "select id,pagecounter,pagedate from Mops_counter",
db, adOpenDynamic, adLockOptimistic
dateformat = Format(adopagecounter.Fields("pagedate"), "ddddd")
If dateformat <> dateformat2 Then adopagecounter.Fields("pagedate") =
dateformat2: adopagecounter.Fields("pagecounter") = 1
mopscounter = adopagecounter.Fields("pagecounter")
adopagecounter.Fields("pagecounter") = mopscounter + 1
adopagecounter.Update
adopagecounter.UpdateBatch
adopagecounter.Close

I open the record set to get the next number
I then test the date of the record against the system date
If they do not match then the record counter is reset to one and the
date is reset to the system date.
I then make a variable equal to the pagecounter
After that I add one to the pagecounter and update the recordset and
then close it.

Through all of this something is left open and often times I get two of
the same order numbers.
I am at a loss of how this happens.
I would appreciate any ones input about this problem.
Thank You,
VBGEEK