• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • Last Modified:

Windows databound form does not update when remote pc has form open

Software: VB.Net 2005 and SQL Server 2005

Windows application with several master/detail forms:

There are several computers using the same application with a remote database. If one pc has a form open and another opens the same form and enters data, it will not update.

Or, in other words if 2 users are working in the form at the same time. The form cannot be updated to the database.

Is there a fix for this?

2 Solutions
Don't bind the form display to the database. If you do, one connection is holding the data locked and others can't change it.

Instead, write your application so that it queries the database to obtain the data it needs, and then displays it locally. Once the application is ready to write the data back to the database it should read the data again to ensure someone else hasn't modified it in the meantime and then write. If the data has been altered, the application can warn the user......your choice of how, etc., as writer.


Jacques Bourgeois (James Burger)PresidentCommented:
The behaviour your encounter is quite typical of most good databases.

Think of it.

User A changes a date field while user B changes the same date field, but with another date. Usert A save its changes. User B changes its changes, overwriting what user A just did. User A goes away thinking that his change was OK, while User B overwrites it, not knowing that A made a changed betwteen the time B got the data and the time he updated it.

This would become a mess. There is at least one loser here, maybe both.

Welcome to the beautiful world of multi-user environments.

So most old systems will "lock" the data when one user starts working with it, preventing other users from unknowingly overwrite it.

Think of what it was before computers. User A got the index card (if you are old enough to know what an index card is) and started making changes to it. User B came to get the card and did not find it, because User A was working with it. User B had to wait until User A returned the card before he could make any further changes to it.

Computers cannot do better in such a situation, so they prevent User B from doing changes while User A is working on the card... Ooops! The record.

It's perfectly normal and logical that 2 users cannot change the same data at the same time.

Going around that limitation is a case by case scenario, and the way to handle it is different in each scenario.

The default, as explained, is usually to prevent User B from making changes while User A is making some.

In other scenarios, the last one to update wins. This is the easiest solution for the programmer, but not always the right one for the data.

In other scenarios, you establish a mechanism such as the one described by DcpKing. This is often the best solution, but this is the most difficult to implement, and there are many different mechanisms to implement it, depending on what the needs are, what the data is, and how the data is brought into the form (regular DataTable, Typed Dataset, Data Entity, LINQ, custom class, you name it).

The fix to this is something that you have to define yourself, depending on the needs of the particular table. And before doing that, you need to know where the lock comes from. This can be triggered in a stored procedure, in the code, while in a transaction, in the application in one of the users goes through another application such as Microsoft Access to edit the data.

This is what programming in a multi-user environment is all about.

Look at the "mother of all the modern databases", Amazon. When you post a command on Amazon, the changes are not made to the system as you go along. All the items that you order are put in a basket. Nothing is change in the database up to that point. When you are ready to order, you press Submit, and then can be told that the item that had 3 copies available at the time you added it to the basket is back order when you confirm the order.

Amazon simply records the required operations, but updates the database only when you are finished, after having checked what the current situation is at that moment.

That is what DcpKing what suggesting.

Unfortunately, there is not automated way of doing that. You have to thing carefully and code that according to the needs.
dave_skyAuthor Commented:
Incredible answers!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now