How to Create MASTER/DETAIL in MultiUser?

[handoko]

Hi, I need some help.
Here is the situation.
I want to make a multi user entry master/detail windows form.
Example: the tabels are "TTrans" and "TTransDtl"
TTrans fields are:
1. "IDTrans" = autonumber
2. "Remark" = string

TTransDtl fields are:
1. "IDTransDtl" = autonumber
2. "IDTrans" = integer64
3. "RemarkDtl" = string

The relationship is TTransDtl.IDTrans -> TTrans.IDTrans
I'd already make an entry for this form.
For single user it works fine.
But there is problem for Multi User .
Let me explain the problem:
I assume :
1. there is 2 users: UserA and UserB
2. LastAutoNumber for TTrans.idtrans is 100.

When UserA add new record to TTrans. TTrans.IDTrans will get
101.
While UserA have not save the record, UserB adds new record.
UserB also get 101 for TTrans.IDTrans. Because it is in the buffer. Altough I have refill the TTrans dataset before a user add a new record.

Then Both users add the detail records for TTRansDtl.
Each IDTrans of new records in TTransDtl will set to 101 for both Users.
When the sequence for saving is UserA then UserB.
UserA get the detail records for both users, UserA and UserB, but nothing in detail records of UserB.
Cause TTrans.IDTrans for UserB is changed to 102.

Can u help me, so UserB can get the right detail records?
Is my explanation clear enough, if not i'll explain more....

Thank you for time guys.
Sincerely,

Handoko Santoso
ATLANTIS COMPUTAINMENT.

 

[chiph]

Are you using transactions when you write to the database?

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[handoko]

No, i don't use transaction.
Is that necessary?

Thank you
Handoko

 

[chiph]

Yes. Very much so.

You need to do things in this order:
- Call BeginTransaction
- Insert the parent record
- Retrieve the parent's identity value
- Insert the child records using the parent's identity
- Call Commit

You can see a good example in the MSDN help under the "BeginTransaction" method. If you have the 2003 MSDN help installed on your local PC, use this URL:

ms-help://MS.VSCC.2003/MS.MSDNQTR.2003FEB.1033/cpref/html/frlrfSystemDataSqlClientSqlConnectionClassBeginTransactionTopic.htm

What happens is on the database side, when you start a transaction, all the changes made from that point forwards on that connection are recorded in the transaction log, not in the tables. When you call Commit, those changes are applied as one operation to the tables. Because of this, the other users won't see your changes until they are Committed, and thus, they won't be using the same identity value as you.

If an error occurs at some point before calling Commit, you would call Rollback, which undoes your changes.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first