We help IT Professionals succeed at work.

Please help with the design of a program for Physical Inventory

Last Modified: 2010-05-18
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
end if
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.

Thanks !!

Watch Question

Top Expert 2007
This one is on us!
(Get your first solution completely free - no credit card required)
This one is on us!
(Get your first solution completely free - no credit card required)


OK, I think I've got what I need out of this.

I gather from your replies that writing out the data "frequently" should not be a problem, but you have provided an idea on how to manage the frequency.

I am also considering creating a form with text boxes instead of using a datagridview.

Thank you for taking the time to reply.

I would just listen for the CellValueChanged event and do the database update every time a change is made, as you hit on yourself. Realistically you shouldn't have any problems doing this with SQL Server. Even if you have one change being made every 10 seconds by someone in the data entry staff, this timeframe is an eternity to a high performance database, with updates occuring in < 1 second.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.