dcsimg
Results 1 to 3 of 3

Thread: Update a record by multiple users at a time

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Apr 2005
    Posts
    105

    Update a record by multiple users at a time

    Hi,

    I have a project for a money transfer compnay in VB6 and MS SQL 2000 and there are more than 20 Users working on the same project
    In one of my table there is a record to be updated by all users.

    I am using the following code

    "update LCStockMS set Month2 = " & StAmt & " where On_Year = '2007' AND C_Code = 'EBL' "


    Where StAmt is the amount to be settled to the currespondent on each transactions

    But I got wrong value when multiple users doing at a same time.

    So any body can suggest a solution to clear this problem

    With regards,
    Nasreen

  2. #2
    Frenzied Member moinkhan's Avatar
    Join Date
    Jun 2000
    Location
    Karachi, Pakistan
    Posts
    2,011

    Re: Update a record by multiple users at a time

    Quote Originally Posted by nasreen
    Hi,

    I have a project for a money transfer compnay in VB6 and MS SQL 2000 and there are more than 20 Users working on the same project
    In one of my table there is a record to be updated by all users.

    I am using the following code

    "update LCStockMS set Month2 = " & StAmt & " where On_Year = '2007' AND C_Code = 'EBL' "


    Where StAmt is the amount to be settled to the currespondent on each transactions

    But I got wrong value when multiple users doing at a same time.

    So any body can suggest a solution to clear this problem

    With regards,
    Nasreen
    Maybe record is locked by someone... and some of the users are not able to update the record..

  3. #3
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,873

    Re: Update a record by multiple users at a time

    20 users should never be trying to update the same record at the same time.

    You should architect your table structure to avoid this.

    20 users should be only, ever, inserting new records into a table. That's the way a multi-user system should be designed.

    That "insert only" table should have probably an identity column as the PK.

    Then you use SELECT SUM(...) FROM ... GROUP BY statements to aggregate activity on that table.

    Are you reading the amount into VB and then modifying it in VB then attempting to re-update that value - with 20 users doing this concurrently??

    Please explain what's happening prior to the update and what your end-goal is.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width