|
Subject:
|
database concurreny problem with vb6 and sql2000
|
|
Posted By:
|
shamimbd17
|
Post Date:
|
8/21/2006 6:03:54 AM
|
I have two tables one for transactions and another for balance
The table design:
Table1(AutoID(PK),AccountNo,Valuedate,DrAmt,CrAmt )
Table2(AccountNo(PK),Valuedate(PK),Balance )
ValueDate can be any order.Suppose you enter valuedate 12/08/06 then you can enter 20/08/06 also enter 15/08/06 etc.
Valuedate can be previous or future
In table1 one record add for per transaction
In table2
if the enter valueDate is not in the table for the entry Account then new record add with previous max(Valuedate) date balance with +- new amount. and also update all balance field greater than the entry valueDate balance for the entry AccountNo.
if the enter valueDate is in the table for the entry Account then update all balance field >= the entry valueDate balance
this is the scenario.
I use beginTransaction and CommitTransaction becoz of two table will be modified for per transactions
and use command object with sql query for insert and update.
Every thing is ok.
But this is a multi user mode. if more than one user update balance table(Table2) with same account at a time then
balance will not consistence. because suppose in 123 account valuedate 12/08/06 balance 500. Same time two user
entry for 123 account with valuedate 13/08/06 and 14/08/06 100 and 200 credit.After their insert 13/08/06 balance will be
600 and 14/08/06 balance will be 700. but it is wrong with 14/08/06 balance it must be 600+200=800
How can I apply that more than one user cannot update/insert for same account when transaction is in live?
but other Account entry also be possible.
The code Like---------
rs as new adodb.recordset
cmd as new adodb.connection
'cnn is my connection
on error goto err1:
cnn.beginTransaction
cmd.activeconnection=cnn
cmd.commandtype=adcmdtext
cmd.commandtext= someting for get max previous Valuedate Balance
set rs=cmd.execute
cmd.commandtext= insert in Table1 query
cmd.execute
cmd.commandtext= update Table2 query
cmd.execute
'if needed
cmd.commandtext= insert Table2 query
cmd.execute
cnn.committrans
exit sub
err1:
cnn.rollback
msgbox err.description
--------------
help me
shamim
|
|
