This is probably an unusual request, but I would be grateful for some advice on proper design of a program I need to write.
We will be doing a physical inventory of every item in our facility (about 20,000) next month. I have been tasked with writing a program in which data entry people will enter the counts from tags returned by count teams. After all the counts have been entered the program will provide various reports detailing $ impact, missed items, etc. After all the counts have been approved, they will be pushed into our ERP system.
This is a mission critical application, since we will shut down production, and everyone will be counting. I cannot afford to tell everyone "whoops, my program failed, let's start over..." :)
The front end will be VB .net, the back end will be SQL Server 2000.
My intent is to provide a screen with a datagridview for data entry. The dgv will have columns such as Ticket number, Item Number, Qty counted, etc. During the data entry process, users will be entering new lines on the dgv, and from time to time they may possibly change a previously entered line by scrolling up and re-entering a single field.
The fundamental problem I'm wrestling with is how, and when, do I write data from the dgv to the SQL database.
My first inclination would be to bind the dgv to the SQL table with a dataset, and then provide an "Update" button that will have code similar to (psudo code only):
if ds.haschanges = true then
One problem with this scenario is that I can forsee someone entering 5,000 lines and not hitting the "update" button, then we get a power blip and all the data entry is lost...
Another option would be to call a subroutine that would write out each row during the RowLeave event. This would keep the data secure, but would result in a lot of writes to the database. In my application I don't think this will be a problem, as we will only have about 15 data entry clerks.
There is probably a better solution to this question, so I would be happy to entertain all ideas.