I've got a Purchase Requisition web form that pulls the top Requisition Number from the database;
TopReqID = reader("ReqID")
and puts the next available Req Number in a label on the page;
lblPurchaseRequisitionNumber.Text = TopReqID + 1
That number, TopReqID + 1, with then, of course be the next ReqID key value entered once the form is submitted.
The ReqID is the Identity Key in the Requisitions table.
*The problem is that while someone has the form open and displaying the "next available Req Number", which will be printed for signatures, someone else may open the form to submit a Purchase Req and it will grab the very same "next available Req Number" since the first one has not been submitted yet.
I need something like a Session Variable, but that the Server is aware of so that each time the form is opened it gets a different number.
Here is a scenario. The last Req # in the DB is 29. User A opens the form and Req # 30 is printed on the form. Currently if User B opens the form before User A submits his, then User B will have Req # 30 also. Not good.
What should happen is when User A opens the for he gets Req #30, then User B opens the form and gets Req # 31, User C opens his form at the same time as the others and gets Req #32.
User A eventually submits his form and record 30 is added to the table.
Users B and C abort without submitting the form at all.
Next day (whenever) any user opens the form, he gets #31
I do see the issue where User A submits #30, User B aborts and User C submits what he thinks is #32 but it goes into the DB as Identity record #31. Dang. Hmmm, also if User C submits his first then the record numbers will be out of order. I guess I need a better solution. Any ideas?