Results 1 to 4 of 4

Thread: [RESOLVED] Theoretical Simple Database Question

  1. #1

    Thread Starter
    Member
    Join Date
    May 2008
    Location
    Lexington, KY
    Posts
    59

    Resolved [RESOLVED] Theoretical Simple Database Question

    Being that some of this is quite new to me, I have been struggling with a concept and would appreciate some input from you advanced programmers.

    The whole concept of downloading a database to a recordset and doing data manipulation is quite nice, but I was thinking about a multiuser situation and wanted to present the following scenario for your comments:

    Bob and Sue both actively update records in a database. It is a simple mailing list Database. They both arrive at 9:00am and have been entering/updating data all morning. Bob updates a record for John Smith and change John’s spelling to Jon. In the meanwhile (keep in mind this is all hypothetical), Sue has to change the same record for the same Mr. Smith record. Her change requires the zip code to be changed from 08088 to 08099. Having said that, since Bob and Sue both came in at the same time, and they both loaded the initial database into their recordsets at the same time, doesn’t that mean when Sue pulls up her version of the Smith record from the recordset for Mr. Smith that it will still show “John” instead of “Jon”? Additionally, when she saves the record with the new zip code, won’t it overwrite Bob’s previously saved record and chance it back to “John” from” Jon”?

    I am thinking about this the wrong way, or am I missing something here, or is there a better approach relative to VB and recordsets?


    In my old world of programming, we interacted directly with the database for each record. Thus we would find the record, lock it, update it, and unlock it, and move on.

    Thanks again for your input.
    Jeff
    "Maximum Newbocity has been achieved!"

  2. #2
    Fanatic Member Dnereb's Avatar
    Join Date
    Aug 2005
    Location
    Netherlands
    Posts
    863

    Re: Theoretical Simple Database Question

    Ussally A record is locked while editing .Edit and .Update set and releases this lock so changing the record simutaniously isn't possible (connected ituation, ADO, DOA)

    Having said that you can also change data disconnected (ADO.Net)
    To handle simutaniously edited records you need to write some error handling
    to determine what fields were changed and what to do with diffrent changes of the same field, ussually last modification count will do nicely

    And I'll recommend using a transaction in these situations.

    Hope it helps....
    why can't programmers keep and 31 Oct and 25 dec apart. Why Rating is Useful
    for every question you ask provide an answer on another thread.

  3. #3
    Frenzied Member
    Join Date
    May 2006
    Location
    Toronto, ON
    Posts
    1,093

    Re: Theoretical Simple Database Question

    This is called data concurrency and it can be a big issue in a multiuser environment. The easiest way to solve it is to put a lock on the record when someone begins editing it and take the lock off when they're done.
    (VB/C#) is clearly superior to (C#/VB) because it (has/doesn't have) <insert trivial difference here>.

  4. #4
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Theoretical Simple Database Question

    The other option is to place a lastedited field on each record (Date/Time) and when ever a record is asked for inclued that field. Now when you go to save the record that field in the database for that record must match the one you downloaded. If they are the same you save the record, update the lastedit field with the current date/time. If they are different you enform the user that they must refresh thier data and re-make any changes in order to save the record.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width