How to deal with autoincrement problem...

[wakh]

Hi,

The problem here is very simple but I have not been able to find a solution for it. You guys must be aware of how the business documents like Receipt Vouchers, Invoices, Payment Vouchers etc work, all of them got voucher no# to identify them. Now the problem here is that assume we are using the autoincrement feature of SQL Server to increase the value of voucher no# by 1 everytime a new record is inserted.

And in the VB application a form is designed to enter all the required data. Once the user opens the form (say for example create_voucher_form) the next available voucher no# is displayed on the form. Now lets assume another user opens the same application on another computer and opens the same create_voucher_form, which will also have the same voucher no# as the one displayed in the create_voucher_form in the first user's application because the first user have not saved anything yet in that record.

This is where the issue arises. Now lets say the first user creates a voucher and saves it. The user on the other computer also creates a voucher and saves it, which will overwrite the previous users record, and in doing so the first user's update will be lost.

I hope the problem is clearly explained. What can be a possible solution for such a scenario?

Regards,
wakh

 

[Carlene Briar]

I suggest instead of using auto increment create the primary key yourself...
Or you try you save the sql insert query to create the auto number key for the row...but make a reference field to use so you can make a select statement... then update the row to fill it with the auto number key that was assigned to it using the reference id.

 

[cjard]

Did you see the date on the thread before you posted?