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

 

[BanditSteve]

patient: "Doc, it hurts when I do this."
doctor: "Stop doing that."

i.e. don't show a voucher number until it actually exists. If using an identity column , you can't show it in advance.

 

[wakh]

BanditSteve : Lolz, I know it can be avoided. This is to discuss with fellow programmers like yourself to see if we can find a solution for it.

I have tested many commercial softwares for the very same thing and those of them which use third party database solutions like SQL Server, Oracle etc suffer with the same problem. Even the latest product by MS for accounting suffers with the same problem. It can be shown if its identity column, you can take the max value, add +1 and show it on the form. Another way I have been handling this issue is by displaying it as soon as the voucher is created. "Last saved voucher#: 478" in the title bar or status bar.

Any other way around in doing it?

Regards,
wakh

 

[thirteentwenty]

I really shouldn't be chiming in on this sort of thing, but I just recently ran into the same thing, I got around it by grabbing the last entry of what ever table it was, (with the autonumber) and spitting it out into a textbox + 1

' This gets the last transaction number
NewTransID()
' this just adds 1 to it
Me.txt_inv_transID.Text = Me.txt_inv_transID.Text + 1

this is how I get the transaction number

    Public Function NewTransID()

        com = New OleDb.OleDbCommand("SELECT * FROM Prefs", con)
        da = New OleDb.OleDbDataAdapter
        da.SelectCommand = com
        dt = New DataTable
        da.Fill(dt)

        frm_invoices.txt_inv_transID.DataBindings.Add("Text", dt, "LastTransNo")

        da.Dispose()
        da = Nothing
        com.Dispose()
        com = Nothing
        frm_invoices.txt_inv_transID.DataBindings.Clear()

        Return ""

    End Function

Admittedly I suck at programming in general... and there are probably other (and better) ways of doing this.

 

[wakh]

thirteentwenty: Your answer suffers with the same problem which is highlighted in my first post. This topic is to find solution for that problem.

 

[cjard]

If the topic is to find a solution to that problem, then the problem is broken.

I have never seen a system (that I thought was well designed) that allows the user to click NEW, and they get a voucher ID there and then.
Voucher ID calc comes at the point of insertion, not before. If you can tell me how knowing the voucher ID will benefit the user, then I guess we can go ahead and find and answer to your question, but right now you should know that the best solution is: don't do it like that

 

[cjard]

Once the user opens the form (say for example create_voucher_form) the next available voucher no# is displayed on the form.

To what benefit? How many users do you know who will read a DB calcualted ID, and go:

"Oooh, wow! My support ticket has ID ABC123XY7932782! I can sleep better knowing that"

 

[BanditSteve]

The issue is not a problem with the identity column, it is doing its job exactly as intended. If you really need to display the next voucher #, you will have to reserve it in some way or use a GUID (ugh) . You could create the record with just a user identity to reference for whom it is reserved (assuming it will eventually be used), you could create a unique number type for each user, or keep a table of voucher numbers and users. All of these except the GUID require extra round trips to the DB.

The thing to do is weigh the requirements, user expectations, and database options. In this case, if voucher numbers must be sequential in chronology, then you must adjust user expectations to not see the next voucher number. If the voucher number must be sequential for each user, you will need to generate a set of numbers for each user. This last method is typically troublesome, requires more columns and queries and should be avoided if possible.

As far as it being a long term issue. Yeah, I've dealt with it for over twenty years and believe me it is primarily an issue with user expections needing to be adjusted to understanding the system capabilites and the impact of what seems minor to them as being a major system problem.

 

[JohnH]

The Id displayed in UI for new rows is generated by the DataColumn and is only temporary, it typically increments negative numbers to show user these rows hasn't been saved yet. When updating to DB this Id is not part of the Insert statement and DB assigns the row a new autoincrement Id. If you have set up data access properly this Id is returned to your app and displayed instead of the temp one. As you can see it doesn't matter how many clients are connected simultaneously since it is the DB that assigns new Ids for all new incoming rows.
Data Walkthroughs
Visual Basic How Do I Video Series - Forms over Data

 

[cjard]

As far as it being a long term issue. Yeah, I've dealt with it for over twenty years and believe me it is primarily an issue with user expections needing to be adjusted to understanding the system capabilites and the impact of what seems minor to them as being a major system problem.

I'd like to reinforce this point; as a computer programmer yousometimes have to mediate between what is reasonably possible on a computer system and what the user expects. Sometimes users expect too little, and you have to help them understand what extra assistance a computer can offer. SOmetimes users expect to much, and you have to manage their expectations.

Bending over backwards to satisfy every whim merely makes you liable to being kicked in the teeth when the solution realised proves to be less robust than needed. The hindrance and frustration a user feels when the process fails is far more pervasive than the initial reaction to a system that works slightly differently to the established human process.

 

[wakh]

cjard: I completely agree with you that voucher no# comes at the point of insertion. As for the applications that show it, I have seen many applications that show you the voucher no#. at the time of clicking NEW. For example Microsoft's Office Accounting Professional 2009 (latest version) does the very same thing. But it also suffers with the very same problem. Infact I was amazed to see such a serious flaw in professional accounting application by microsoft. (Imagine losing a payment voucher worth of $200,000* this way)

I have been doing it the same way i.e "not doing this way" as you said. The point of this topic is to see if there are any other work arounds discovered by fellow programmers. The reason to show it at the point of clicking NEW is that user's expectation is so, due to the fact that this is how they are used to doing it manually, which often requires adjusting. But after looking at all the replies I seem to have been doing it the right way.

BanditSteve: You are right too mate. The second option of reserving voucher nos#. for users which you mentioned is troublesome and is not worth the effort. So for us the best option is to avoid it and display it if required at the point of insertion. (that's how I did infact in my last application that I worked on for some business)

JohnH: You are right that autoincrement column is not part of the insert statement when executed (if included it throws an exception). However I don't understand how autoincremented ID will be returned back to the application? A fellow colleague of mine does it this way: first he executes the INSERT statement with all the data excluding the voucher no# column and in the very next statement he executes the SELECT statement with all the data taken from the form through which he gets the voucher no# to display to the user after the execution of the query. Which I feel is not the correct way, even though he is doing it from quite a long time without any problems. Could you say how you think it can be returned to the application?

Thanks to all the fellow programmers for time.

Regards,
wakh

 

[BanditSteve]

wakh, if you are using SQL Server look at:
SELECT SCOPE_IDENTITY()
and
@@IDENTITY
Please look at both as they behave differently.

We all enjoy a meaningful discussion of design ideas.

 

[cjard]

In Oracle we can do e.g.:

INSERT INTO tblVoucher(vid, name)
SELECT voucherIDSequence.NEXTVAL, VoucherName FROM dual
RETURNING vid INTO VoucherID


And when we make our parameterised query or storedprocedure with that param, right after we call it, interrogating the VoucherID parameter will reveal the ID the database calculated. ADO datasets do this automatically too

If you really must preallocate and show the user a voucher ID, then you must have a separate table loaded with voucherIDs. At the point that the user wants one, their userID is put next to the voucherID while they complete their edit. Essentially you use a table full of vouchers that have an "in use" flag, and each person gets an id (simultaneously making it "in use") until they are done with it and commit their transaction at which point the voucher ID is used up. If they cancel, the ID is returned to the pool..

All in, it's kinda lame and a bit of a headache.. Ive never seen it done but it's possible

 

[wakh]

BanditSteve: I didn't get what you mean, could you please elaborate? I just have access to SQL Server 2000 for now to check? And on the table design tab under properties section only Identity, Identity Seed and Identity Increment is there?

cjard: I don't know how it will really sound like, especially when you have listed your Primary DB as Oracle, but I don't really use it. Almost in all of my projects I used SQL Server. But your solution seems to make use of INSERT and SELECT both (just like my colleague does)? I once tried using ExecuteScalar method but it didn't work...? Maybe buddy JohnH got some other way in mind?

 

[BanditSteve]

Use SQL Server help to look them up - I assume you have Enterprise Manager installed - if not, you need to get it.

After your insert statement, do SELECT SCOPE_IDENTITY() and it will return the identity value used for your insert. Such as CommandText:

INSERT INTO mytable
(Col1, col2)
VALUES ('xyz', 321)
SELECT SCOPE_IDENTITY()

This will be an ExecuteScalar and will return the identity such as:

Dim returnValue As Integer = cmd.ExecuteScalar.

So if the last identity colulmn value was 1234 before the insert and you insert 1235 then it will return 1235.

Can you go to SQL Server 2005? Life is much better there in that the tools for building and testing queries are much easier to use.

Don't do it your friend's way, that is asking for trouble and unnecessary. SELECT SCOPE_IDENTITY() is there because it does exactly what is needed, gets the identity that is in the scope of insert.

If you need more help just show us what you are doing so we can give more specific help. Also, use query analyzer to test your SQL.