SQL Server and Delphi... Delete Records in Cached Update

I have a master-detail cached update transaction. the master table uses dbedit boxes while my detail table uses a dbgrid.

My system allows both creating and editing transaction. The problem is with editing the transaction.

1.) I successfully retrived the master-detail tables.
2.) I tried to delete all the detail records then add new detail records onto it.
3.) When i applied updates, the system just hanged! Everytime I repeat this process, it hangs.

When I checked the current activity log of my sql server, the delete sql statement is highlighted. If i'm not mistaken, the deletion caused the sytem to hang. What's more confusing is that, when i transport it to ms access, it caused no problem. Ms Access allows deleting of details records. Why does Sql Server not allow this? anyone encountered this.. Please help!

onioncubeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jamiguelCommented:
You have two options to divide the operation in two parts.

Option 1
    Apply updates just after of delete operation and after finish whole operation.
   
Option 2
   Don't use Applyupdates, use a BeginTransaction and CommitTransaction for  database integrity. Call this operations before the first operation and just after of finish the transaction


Note: check size of database transaction log and increase it if you database has a lot of operations.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
onioncubeAuthor Commented:

Option 1
    Apply updates just after of delete operation and after finish whole operation.

I use save and cancel buttons. that means, i will only apply updates when the user clicked save button. If i apply updates right after the delete operation, would that mean that if the user cancelled the transaction, he/she won't be able to revert the deleted record (since it is already applied)?
     
Option 2
   Don't use Applyupdates, use a BeginTransaction and CommitTransaction for  database integrity. Call this operations before the first operation and just after of finish the transaction

I have begintransaction and committransaction on and before my applyupdates? Can you give me a sample code of this?

Note: check size of database transaction log and increase it if you database has a lot of operations.
0
yk030299Commented:
listen
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.