Transactions and VB

Transactions and VB

Post by Octavie van Haafte » Thu, 22 Feb 2001 21:38:49



Hai there,

I have this problem (It has to work at financial companies, anyway):

Multi user system:

Two users work with the same customer's data. Both users want to ADD a record
(let's say data of a contact person for the current customer) at the same time.

I want to prevent this. The customer shouldn't get two records with the same
contact person.

Isn't this one of the basics of transactions?
I can't get it done with the BeginTrans, CommitTrans and Rollback methods.

Anyone got an idea?

Thanx,
Octavie

 
 
 

Transactions and VB

Post by Adrian Forbe » Thu, 22 Feb 2001 22:08:31


Add a "locked" flag to the table and when someone requests a row or rows,
update the locked flag to true.  Never allow a user to select a row that is
locked.  When they update or release the data set the flag to false.

Another method is timestampting it.  Add a timestamp field and retrieve it
when you retrieve the data.  Before you save the data check that the
timestamp of the data is the same as what it was when you retrieved it.  If
it is, great.  If not then warn the user that someone has updated the data
since they retrieved it and give them the option of abandoning the save or
overwriting the changes.



Hai there,

I have this problem (It has to work at financial companies, anyway):

Multi user system:

Two users work with the same customer's data. Both users want to ADD a
record
(let's say data of a contact person for the current customer) at the same
time.

I want to prevent this. The customer shouldn't get two records with the same
contact person.

Isn't this one of the basics of transactions?
I can't get it done with the BeginTrans, CommitTrans and Rollback methods.

Anyone got an idea?

Thanx,
Octavie

 
 
 

Transactions and VB

Post by Octavie van Haafte » Thu, 22 Feb 2001 22:42:22


Updating is not the problem, I've got that one covered (timestamp field).

ADDING records is my problem. Two users adding the same record at the same time!

For example,

Two bank employees are updating your account balance. They both start a
transaction. They both add a (transaction)record with the amount of $1000,- to
your account. How does the system know, which addition is allowed and which one
isn't?? However, I think you don't mind getting $2000,-!!  ;-)

Octavie

-----Original Message-----

Add a "locked" flag to the table and when someone requests a row or rows,
update the locked flag to true.  Never allow a user to select a row that is
locked.  When they update or release the data set the flag to false.

Another method is timestampting it.  Add a timestamp field and retrieve it
when you retrieve the data.  Before you save the data check that the
timestamp of the data is the same as what it was when you retrieved it.  If
it is, great.  If not then warn the user that someone has updated the data
since they retrieved it and give them the option of abandoning the save or
overwriting the changes.



Hai there,

I have this problem (It has to work at financial companies, anyway):

Multi user system:

Two users work with the same customer's data. Both users want to ADD a
record
(let's say data of a contact person for the current customer) at the same
time.

I want to prevent this. The customer shouldn't get two records with the same
contact person.

Isn't this one of the basics of transactions?
I can't get it done with the BeginTrans, CommitTrans and Rollback methods.

Anyone got an idea?

Thanx,
Octavie

.

 
 
 

Transactions and VB

Post by <Doru Roman> » Thu, 22 Feb 2001 22:49:36


My question is how come that two bank employees do the same operation for
the same customer in the first place?
It does not seem possible.
I do not think that it is possible to control such a thing. How should the
application know whether there is a duplicate
operation over the same amount of money, or it is indeed an attempt to
deposit $1000 twice (a total of $2000) by free choice?
Can you elaborate on the matter?
 
 
 

Transactions and VB

Post by Craig Hun » Thu, 22 Feb 2001 22:57:18


Hello Octavie.

Maybe I missed something here, but couldn't you use a primary key to
accomplish this task?

--
HTH

Craig Hunt, MCSE, MCDBA
AISA Logic, Inc.
http://aisalogic.com

--Think when you breathe.



Hai there,

I have this problem (It has to work at financial companies, anyway):

Multi user system:

Two users work with the same customer's data. Both users want to ADD a
record
(let's say data of a contact person for the current customer) at the same
time.

I want to prevent this. The customer shouldn't get two records with the same
contact person.

Isn't this one of the basics of transactions?
I can't get it done with the BeginTrans, CommitTrans and Rollback methods.

Anyone got an idea?

Thanx,
Octavie

 
 
 

Transactions and VB

Post by Octavie van Haafte » Fri, 23 Feb 2001 16:15:02


Craig, the primary key is an automatically generated GUID...

To you all, thank you for all the efforts and time. Whether the examples given
are reality or not, in theory the problem could exist in any system.

I think I've found a solution of some kind:

The Connection object has a property called IsolationLevel. You can set this
property in conjunction with the BeginTrans method. If you set this property to
adXactIsolated any transaction on a table will be isolated (even reads).

I have to test it thoroughly, but at first sight it works fine.

Thanks,
Octavie

-----Original Message-----

Hello Octavie.

Maybe I missed something here, but couldn't you use a primary key to
accomplish this task?

--
HTH

Craig Hunt, MCSE, MCDBA
AISA Logic, Inc.
http://aisalogic.com

--Think when you breathe.



Hai there,

I have this problem (It has to work at financial companies, anyway):

Multi user system:

Two users work with the same customer's data. Both users want to ADD a
record
(let's say data of a contact person for the current customer) at the same
time.

I want to prevent this. The customer shouldn't get two records with the same
contact person.

Isn't this one of the basics of transactions?
I can't get it done with the BeginTrans, CommitTrans and Rollback methods.

Anyone got an idea?

Thanx,
Octavie

.

 
 
 

1. SQL Server transactions in VB apps.

The application is in VB6 using SQL Server 7.
I want to start a transaction ( called A ) on a database which may or may
not get committed. Before this transaction has been committed or rolled back
I want to start another transaction ( called B ). This, as far as I can tell
can only be nested within transaction A and not in parallel to it. Therefore
if transaction B is committed and transaction A is rolled back after B is
committed then transaction B will not be committed also. This is because
transaction B is not properly committed until the outer transaction is
committed.
However I would like to commit transaction B and make it possible to roll
back transaction A as if the two were in parallel and not nested inside one
another. Do anyone know if this is possible or of  a way round it?
The two transactions involve different tables. In order to run transaction
statements against the database I am using a connection object with a DSN
and running the appropriate SQL code.

Cheers,

      Adrian Roach.

Sorry for the dual posting.

2. float problem... anyone?

3. How to do a large Insert / Update Transaction from VB.

4. Using Troi's progress bar with runtime engine

5. transactions in VB

6. dbase 3 plus database unreadable in Visual dBase 5.0 - Conversion Program ??

7. Need help in designing transaction in VB 6

8. Public Role

9. Transaction on VB

10. Transactions with VB + Access + ODBC?!?

11. Transactions using VB 3.0/ODBC Q+E/Sql*NET TCP-IP/Oracle 7

12. Transactions in VB 4.0 16-Bit

13. Error beginning first transaction with Vb+Ado+Sql7