Solved

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

Posted on 2014-03-07
3
200 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

707 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

17 Experts available now in Live!

Get 1:1 Help Now