Solved

Need Server side alternative to Session Variables - to track "next available number"

Posted on 2011-03-10
15
301 Views
Last Modified: 2012-05-11
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?

Thanks.
0
Comment
Question by:megnin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
  • +1
15 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 35098193
You could use a control table in your database that tracks the next requisition number, which you would store in your table separately from the actual ID of the record. Each time you retrieve the next number you increment it so that the next call will always be one further on.

That way you keep the Identity column as an auto-incrementing number that the user never sees, and you store a specific requisition number against the record.
0
 
LVL 56

Accepted Solution

by:
HainKurt earned 500 total points
ID: 35098249
above solution will create many gaps on your original table...

instead, dont show orderid until it is submitted to db
in db create a stored proc and in this sp, either run a query to get max+1 (or use identity and insert with null as id and get identity on this table to find the inserted id - i suggest this)
once you insert the record, you will have the orderid and you can return this to page, and show it to user...
0
 
LVL 5

Expert Comment

by:mayankagarwal
ID: 35098277
then instead of a stroed procedure directly use the autoincriment type for ID as suggested by carl
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 1

Author Comment

by:megnin
ID: 35098279
Yeah, that's just about what a colleague and I were just discussing.  The control table would use auto incrementing "Identity" values and the main order table would not.  The main table would have to accept whatever order number was submitted.

We also discussed making the form create a new order record when the form is opened and when it's submitted do an UPDATE to insert the field values.  The problem I see with that is you'd have an empty record every time someone just looked at the form then closed it unless you could DELETE that record if it wasn't "Submitted".  Is that possible (or practical)?
0
 
LVL 5

Expert Comment

by:mayankagarwal
ID: 35098309
that possible byt that will leave the gaps in ur ids
0
 
LVL 1

Author Comment

by:megnin
ID: 35098315
HainKurt, I think your solution is best as long as the order "form" can be printed by the user with the number on it because the paper copy must go for signatures.
0
 
LVL 1

Author Comment

by:megnin
ID: 35098341
Oh, yes, an ID is not reused when it's deleted, so deleting it would be pointless.
0
 
LVL 5

Expert Comment

by:mayankagarwal
ID: 35098359
autoincriment the field and show it to user after subbmision is the best possible solution in this case
0
 
LVL 5

Expert Comment

by:mayankagarwal
ID: 35098378
one more thing can be done...
you do as you have suggested i.e. insert a row the update it and delete in case user cancel the request.  but while getting the new id search for the gaps and assign the gap id to the new record
0
 
LVL 56

Expert Comment

by:HainKurt
ID: 35098639
when you use autoincrement column in your table, there will be gaps only if the insert statement fails somehow... other than that there wont be any gaps... if gaps is not important us the one I suggested before...

if you DO NOT want any gaps, when you try to insert the record, find the min gap and use that one as new id for inserted record...

you can find the gap by

select min(id1)+1 as NewID from (
select t1.id id1, t2.id id2 from mytable t1 left join mytable t2 on t1.id+1=t2.id
) x
0
 
LVL 1

Author Comment

by:megnin
ID: 35099181
HainKurt, I'm so sorry.  I just looked throught all the comments and realized that you were the one who first suggested that solution.  Should I request Admin to do a point redistribution?
0
 
LVL 56

Expert Comment

by:HainKurt
ID: 35100839
Yes please...
0
 
LVL 1

Author Comment

by:megnin
ID: 35108074
I submitted the request yesterday at 6:33 PM.  I haven't gotten a response yet, but it's not yet 8:00 AM.
0
 
LVL 1

Author Closing Comment

by:megnin
ID: 35128591
This solution will work best for me.  Thank you very much.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question