Solved - Updating data automatically

Posted on 2007-07-20
Last Modified: 2013-11-26
I am writing a VB.NET database application, using the drag-and-drop tools to create both list and detailed bound forms.

The problem is, to write data back to the database, I have to click on the "Save" button.

What I want is for this to happen, whenever the user modifies/deletes/inserts data - I dont want for them to have to click on the Save button (in fact, I want to remove it).

Is it possible to do this easily?

Question by:fweeee
    LVL 19

    Expert Comment

    the question is, at what point does the applicatoin know the perso is finished entering the data?
    what if they pause for a second to drink coffee? should it save, what if there is a typo? what if they accidentally spill the coffee on the keyboard and wipe it with a rag, do you want it to save while they are wiping it?


    Author Comment

    Basically, for list views, when the user leaves a record it will be updated.

    For detailed views, I'd like to (sometimes) update the data as soon as they leave a field.
    LVL 19

    Expert Comment

    why not set it to save as soon as they highlight any field?
    like the first portioin of the subroutines is or something?
    it won't save as they are finished typing something, but as soon as they select some new field to type in it will save the previous changes.
    LVL 34

    Accepted Solution

    A basic concept on which VB.NET/ADO.NET's data handling model is built is disconnection.  So, it provides application level storage for the data that is being worked upon (datasets, datatables) and means for intermittent communication between the application and the database (connections, dataadapters and tableadaapters, datareaders, commands with ExecuteNonQuery).  The basic idea is that _all_ necessary data can be brought over to the app when it starts, with the connection to the database lasting only so long as is necessary to do that.  The data is then worked on in the app without any further reference to the database: the app and the database are disconnected.  Then when the app closes the data can be sent back to the database, with a connection lasting only so long as is necessary to achieve that.  The model - with its .RowState flags for datarows, and how its dataadapters and tableadapters work in checking those - means that only data which has been changed is sent back.

    In practice, there can be all sorts of reasons for departing from that "pure" model.  Having read through your question

    I think one of those reasons applies here.  In a multi-user environment it is usually desireable for other users to know pretty quickly what changes this user has made to the data they, too, may be working on.  So in principle I see why your aim is frequently to update.  But there are questions of degree.

    Every connection between an application and a database includes an overhead.  With a connected model, it occurs once.  With the disconnected model, it occurs every time any changed data is sent back to the database - and, given that in a multiuser environment this user also needs to know what changes other users have made, it will also be needed to refresh this app's data.  The overhead is very small, and there are methods for managing it, but with very frequent updating - both ways - between the database and the app it can mount up.  There are concurrency issues.  If two users are working on the same record there has to be a policy, and implementation of that policy, for deciding which user's changes will be accepted.  There's nothing special about that, but it does have implications for how often changes should be made.  Take a record with 3 fields.  Users A and B are working on it.  User A changes field 1 whilst user B is changing field 2.  If the saves are done with "each change" then, at best, the final record will be an amalgamation of the changes made by the two users: it will be, as a whole, not what either User expected it to be.

    The point of all that - and it is background - is to highlight that committing changes back to the database "as soon as they leave a field" - even if it is only "(sometimes)" - is fairly substantially at variance with the model that the tools you are using is designed to implement and, for my money, there would have to be pretty compelling reasons to "work round" that model to that extent.  It is, as weellio has pointed out _possible_.  Each of the controls will have some event into which you can hook to put updating code: such as Leave or, in a datagridview, CellLeave.  But the code that would be necessary in such an Event helps to illustrate the variance between what the app is doing and the model on which the tools it is using is based.

    Basically, all the code that is needed to send changes back to the database is




    and you can put that code anywhere.  (I am assuming that the adapters are configured with the necessary commands, but if you've used the wizards that should be a safe assumption).  But that will only work if the changes that the user has made have already been "committed" from the GUI in which the user is working to the datatable.  And that only happens (automatically) when the user changes to a new record.  So, if you just put the code above in some event that fires when one field in a multi-field record has been changed, it won't work.  Now, you can force the changes back to the datatable, so that the above methods will work, by using something like


    first.  But my point is simply that the very fact that it is necessary for you to do that might suggest that Updates are being made more frequently than the model envisages.

    As I say, the questions here are ones of degree, not principle.  So if you feel that, in your circumstances, such frequent updates are necessary there is no rule against making them and it is possible to code to do so.  But for general purposes my recommendation would be only to update, at the very most, when a changed record had _automatically_ been committed to the datatable.  And, in your setup, the Event that would fire when that happened would be



    Author Comment


    Thanks for your answer. I'm aware that the data model rather different to what I am used to working with (disconnected rather then connected). That is why working out how and when to update data to the database is one of the first things we are trying to sort out in this project. Updating it line by line will probably be fine - I guess the users will just have to learn that the data will only be updated when they leave a record.

    Your answer was right on the money - I put the code:
    in the event:
    and it is working exactly how we want it to.

    I also added the code:
    to the FormClosing event, to ensure that last record got saved even if the user didnt leave the record.

    I'm glad it was this easy - when I had to do a similar thing in C# (I was investigating this earlier) it took a dedicated function to do it correctly (never erroring).

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
    For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK ( for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    728 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

    24 Experts available now in Live!

    Get 1:1 Help Now