Solved

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

Posted on 2014-03-07
3
201 Views
Last Modified: 2014-03-08
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?

thanks!
0
Comment
Question by:dave_sky
3 Comments
 
LVL 16

Assisted Solution

by:DcpKing
DcpKing earned 250 total points
ID: 39913848
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.

hth

Mike
0
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 250 total points
ID: 39914192
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.
0
 

Author Closing Comment

by:dave_sky
ID: 39914837
Incredible answers!

Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now