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

 

[wakh]

BanditSteve: That's really useful. I know my colleague's way is not at all good, i.e incase of duplicate records it will be a big problem. I'll let him know about the SCOPE_IDENTITY, @@IDENTITY and INDENT_IDENTITY.

I am not programming anything at the moment, but this is the issue I have dealt with a couple of times before so thought of discussing it. Thanks..!!

I'll have a look at SQL Server 2005 or maybe 2008 version. The reason I am using SQL Server 2000 is because of its simplicity.

 

[JohnH]

However I don't understand how autoincremented ID will be returned back to the application?

Check out the help links I posted, it explains how to start easy with data access "the right way". Doing this does everything for you. Later you can inspect the generated code to discover how they really do everything. You will for example see they use multiple Sql statements for InsertCommand, one Insert and one Select to get the identity.

 

[cjard]

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)?

No, it's actually just one op in Oracle

You see a values based INSERT looks like this:

INSERT INTO table(field1, field2)
VALUES 'one', 'two'


But we cannot say in oracle:

INSERT INTO table(field1, field2)
VALUES mySequence.NEXTVAL, 'two'


Because sequences cannot be accessed in that way, we must run a select. Oracle is a bit more logical than SQL server in this way. SQLServer has 2 inserts -> INSERT INTO (for values) and SELECT INTO (for insert from query)

In oracle we have a table called DUAL, that is one row, one value. Kinda like a dummy table. Because it only has one row, anything selected from it returns only one row:

SELECT 'hello', 'world' from dual

results
--------
hello|world

Note that i'm deliberately selecting constants here. Nothing in SQL states you have to select columns from a table. Because we have to use sequences (autonumber in Oracle) with a SELECT, what we do is:

SELECT mySeq.NEXTVAL, 'some', 'other', 'values' FROM dual

and we get one row out of this query. This could be used with an insert query instead of INSERT..VALUES, we can insert the whole row we get:

INSERT INTO table(ID, Name)
SELECT mySequence.NEXTVAL, 'John Smith' FROM DUAL


Note i'm putting them on separate lines for clarity, but they are one statement. Multiple statements are separated by semicolons. In english the above statement means:
"Insert into the table

, columns [id], [name], the values returned on the rows of the query: select ... from dual (which will only return one row and is hence suitable for operation similar to insert..values)

-


If you know that Oracle's INSERT INTO x SELECT * FROM y is the same as SQLS's SELECT INTO x * FROM y it starts to make more sense

Oracle's to me, makes more sense, because SELECT is a read operation, INSERT is a write. You cant read into anything.. REmember that every SELECT in SQL i just producing a data block that can be pushed into something else:

SELECT a, b FROM 
(
  SELECT a.a, b.b FROM a INNER JOIN b ON a.id = b.id
)

SELECT INTO blurs that distinction a little.. but now you know what INSERT INTO..SELECT FROM is for, you can hopefully see that it is not two queries, but only one (atomically completed.. it does require a select to be run) with the goal of inserting a row of values.

Oracle added the RETURNING because it helps to passing back generated IDs. While you COULD run an SQL to say:


INSERT INTO table(ID, Name)
SELECT mySequence.NEXTVAL, 'John Smith' FROM dual; <-- note the semicolon!! this means 2 statements are here
SELECT mySequence.CURRVAL INTO :id from dual


It is a BAD idea for the same reason that @@IDENTITY is bad: it is global across the DB. (Actually i think oracle prevented this already; you cannot select the current sequence value without having a valid scope for it, and a single select statement is not a valid scope)


OK, so hopefully you know all about IDs now, and how each DB does it

 

[BanditSteve]

Quote: "The reason I am using SQL Server 2000 is because of its simplicity."

You will find that 2005's Server management Studio is much easier to use than 2000's Enterprise Manager. If you want t0 wait and jump to 08, that is fine. I usually wait at least 6 months after release for any MS technology, preferably to SP 1.

cjard is right in that SQL Server sometimes uses stupid syntax that is more clear in Oracle and he his examples apply to SQL Server nicely.

I'm not sure what JohnH's concern about the 'right' way is. Surely, you don't want an MS only point of view. Otherwise we would just turn to MSDN everytime we need help!!

You will want to make maximum use of stored procedures and SqlClient.SqlParameter, especially if doing web apps, as they are your last defense against SQL Injection attacks and provide fast, robust queries.

Explore your resources at places like 'SQL Server Central', 'SQLTeam', etc.. You will see many real-world solutions to problems never anticipated by the MS folks.

 

[JaedenRuiner]

Well, I would have to agree with cjard that the user's awareness of the Auto-incremented ID number is superfluous at best, however, there would be a way to allow the user to see their new ID and not have conflict, but it would be very convoluted and shall we say, somewhat self-defeating to the idea of the Identity auto-increment. Either way, I'll present my solution, which if anything should incur a few snickers...

First:
For each Table that utilizes an Identity Field you wish to display create a secondary column that is a "Bit" field, simply 0/1 binary. This is a "Saved" flag, indicating the ID is actually a valid entry.

Second:
When Necessary to "Creates New" in the Create_Voucher_Form, (Allowing that All other Voucher Fields are able to be DBNull) actually create the entry into the DB, getting the valid ID number. THis would mean that user one clicks new and the ID is 3, and the other user clicks new and that ID is 4 automatically, because the new actually creates and saves the new record even before any other data is entered.

Third:
Build into the DB either a Stored Proc/Func that seeks the last valid ID, by way of that Bit field we implemented in step 1. This finds that Record and selects it for Editing.

Fourth:
Link it all together so that any time a Record Is "Canceled" the Fields are blanked and the "Saved" bit field is reset to 0, but when they are Saved that field is updated to 1. Now any time a user wishes to "create" a new entry, it first uses the Stored Proc/Func to find if there are any records that should be used First, and instead of "creating" it "updates" that record, setting the bit field to 1 upon saving, and thus if no record is currently an "Open ID", it then automatically creates the new id, as well when the record is saved, event though a new ID was automatically created it first scans the db again with the Stored Proc/Func to verify that no previous IDs were canceled, and leaves the current created id with SavedBit 0, and updates the sequentially earlier ID's record with the column values, flagging the savedbit to 1.

Example Flow:

User 1 Adds New Record: ID = 3
User 2 Adds New Record: ID = 4
User 3 Adds New Record: ID = 5

User 1 Saves Record: ID = 3, SavedBit = 1
User 2 Cancels Record: ID = 4, SavedBit = 0
User 3 Saves Record: Updates to ID = 4, SavedBit = 1, ID = 5, SavedBit = 0

The programmatic aspects of this solution like I said are convoluted, painstaking, and frankly unnecessary in light of my agreement with cjard about the importance of the Identity Field, but it would work with what you've suggested. Most likely there would be a lot of Extensions and overloaded methods in the Partial DataSet class in order to handle some of the data checking and such, as well there would be a lot more interactivity between dataset and database, constantly checking and rechecking everything, but in the end it would effectively bypass that issue of a displayed ID without the situation of overwriting multiple user entries.

 

[wakh]

JohnH: OK, I'll have a look at your posted links tomorrow when I have some free time.

cjard: Thanks for your very nice explaination...!!

BanditSteve: Sure, I'll have a look at those places. Many organisations follow that policy, i.e wait for atleast SP-1 before making a switch.

JaedenRuiner: Thanks, I did have that kind of way in mind, but frankly its too much of a trouble to go about doing in that way. I have also seen some commercial applications that use this approach to some extent, but they have their own storage mechanisms and don't use third party products like Oracle and SQL Server. What they simply do is mark the field as "taken" once the user clicks NEW, and when another user clicks NEW on different machine it simply takes up the next available ID, even if the previous user discards and quits without saving anything into the ID which was marked as "taken". Essentially they don't loop to see if any ID's were left unused. So in this way alot of ID's remain unused.

Anyways I think we have concluded the best option, i.e to not display it until the point of insertion.

Regards,
wakh

 

[JohnH]

I'm not sure what JohnH's concern about the 'right' way is. Surely, you don't want an MS only point of view. Otherwise we would just turn to MSDN everytime we need help!

Yeah, all the people posting here that starts data access with "Dim cmd As New OleDb.OleDbCommand" is really worrying. Why does that happen? Bad learning books? Or perhaps they just search the internet for "vb.net data access" and the first thing they find is bloated code snippets that start out like that. Use the Visual Studio for all its worth, that it is free for starters is a mystery for all what it provides. The visual designers is as important for data access as for UI design. My "point of view" regarding using visual designers for development is not related to MS is any way. It's about using the right tools for the job.

MS "point of view", if that is the term, is the most important one regarding documentation of the .Net libraries that they wrote and we all use. Most of the questions asked at these forums would be solved if the person had read and understood the help documentation. Of course beginners by no chance has had the time to read much yet (or find where to start reading), and even advanced developers can't know everything. On the other hand parts of the documentation is lacking and really bad, and sometimes when you have read it you still need help to connect the pieces. The MS organization also provide lots of useful articles, walkthroughs, videos etc, so does many other parties. Many beginners don't know where to start and need help to find these resources.

 

[cjard]

So in this way alot of ID's remain unused.

Consideration should always be given to the balance:

Cost of having holes in IDs
Cost of filling holes in IDs


Holes are cheap.. One integer? One long? Holes can be necessary too; they tell us a user cancelled, and they maintain chronologicity (so long as things arent cached like Oracle sequences can be).. Once a mechanism is invented to fill in holes you lose reliance that your records were inserted in the order of ORDER BY id ASC. If you, once a year, run a process to close all the gaps, you can end up with customers ringing up saying "yes, my id for my order is..."

So typically, having holes is way more useful and cheaper than filling them in


@BanditSteve, regarding the debate with John over Microsoft's mailing address (Microsoft HQ, [only] One [way, the] Microsoft Way, Redmond, WA - clever, huh? very subliminal), John was (in fairness) simply pointing out the very micro concept of doing data access properly, as per the horse's mouth.. You then dragged it out to a little more macro with e.g. "SQLTeam solving problems MS never thought of" - I'm sure they do, but youre talking about code at a much more abstract level than low down dirty database access.. The visual designer makes an excellent job of making a "shared DAL" just like it makes an excellent job of layout on shared/reusable forms!
If youre looking for an MS biased or less-than-even metered contributor, I'd humbly suggest that John is perhaps the worst candidate for that - day in, day out the guy gives great, correct, justifiably sensible advice. While I'm not saying youre wrong in any way, I'm sure you can appreciate from your 20+ years experience that your experience from even as recent as 5 years ago is phasing out in a fast paced world such as IT. I'm still on VS2005; it's 3 years old and I'm starting to feel it, mainly cause I can't answer newbie questions about LINQ.

 

[wakh]

JohnH: I went through those videos you pointed out earlier, they are targetted at mere starters and covering topics like creating a database, connecting to a database etc by using wizards. Its of no use to what we are discussing here. Regarding the visual designer for DAL, frankly I am not a big fan of wizard approach. It ofcourse got alot of advantages, but still I prefer to code DAL manually.

cjard: I agree that sometimes having holes is way more useful and cheaper than filling them in. And about experience phasing out, that's the biggest drawback of staying in the IT field. It is sometimes interesting to dig into the new stuff every so often, but that's only if you have chosen to stick with a specific area. Whereas if are trying to keep yourself involved in more than one specific area then its way too difficult and troublesome to keep yourself updated.

 

[cjard]

Regarding the visual designer for DAL, frankly I am not a big fan of wizard approach. It ofcourse got alot of advantages, but still I prefer to code DAL manually.

Just curious: Do your lay your forms out by hand too, or do you use the wizard (forms designer) ?

 

[JaedenRuiner]

Cjard, that's easy: Both

It is often useful to use the designer to build a form, but forms are not always static, and thus it is sometimes easier to design once, and then copy all the necessary "coordinate" (and other visually descriptive) data and shift that from the InitializeComponents() method into a new location for when it is necessary. As well, there are situations even a wizard can't be used on.

Personally, my issue with wizards is that they are static, because, for example, if you wish to edit the DataSet generated code, you can only extend the classes, because if you update the "designer" it regenerates the code instead of building off of what is already there. All my code parsing engines i've written are full-parity : they go both ways. If I can generate the code, my engine must (by personal rule) be able to parse the code back, and the VB designers don't do that.

*chuckle* - but eh, nothing is perfect so we all learn to hack whatever language base we're using and move on to the next one.

as for the advancements in IT, as long as it remains within the software realm, (DB, web back end, app dev, code libraries) the new technologies in software are pretty easy to pick up...all save for one thing: they tend to try and make these language development interfaces more...idiot proof, attempting to help everyone with every miniscule detail, which, not to be rude, brings a lot of tech-school grads into the market that haven't really had a truly hard-core computer logic course. My first instructor taught me to learn the base of all language, the rest is syntax.

Cheers

 

[cjard]

I'm sure you already know this, but the classes designed by wizards/visual aids are usually partial, meaning you can put persistent code changes in your own area, in a partial class and the compiler will combine the classes during build. This usually solves most problems of wanting to add functionality to designer generated code. In the cases where you really want to completely supplant some designer code you can override any defective implementations (which may involve editing designer generated code to make them overridable, but it's a smaller change than pasting code into the designer file and the compiler will remind you if you forgot)

 

[wakh]

Just curious: Do your lay your forms out by hand too, or do you use the wizard (forms designer) ?

Lolz, ofcourse designer and code both. Usually the designer dominates. That's the reason we are here otherwise I guess we might have been found lurking on some C++ community?
As we are on it now, I would say that I really love that language but time and real life responsibilities doesn't permit me to play with it. I guess we all love somethings for which we don't manage to take out time.

Anyways, I do programming just as a hobby, my mainstream field is Networking. That's the reason I mentioned earlier that its very difficult to keep yourself updated if you delve in more than one specific field.

 

[JaedenRuiner]

That's the reason we are here otherwise I guess we might have been found lurking on some C++ community?
As we are on it now, I would say that I really love that language but time and real life responsibilities doesn't permit me to play with it. I guess we all love somethings for which we don't manage to take out time.

That's why you use C/C++ for Game Mod Development and VB.Net for a paycheck.

 

[wakh]

Lolz, very well said.