Update a record by multiple users at a time

[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

[moinkhan]

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..

[szlamany]

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.