Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Anil Sharma
  Where is Anil Sharma?
 ludhiana
 India
 Anil Sharma
 To: Binod Binani
  Where is Binod Binani?
 Kolkata
 India
 Binod Binani
 Tags
Subject: RE: SQL Transactions
Thread ID: 365203 Message ID: 365329 # Views: 41 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Monday, December 24, 2012 8:47:47 AM         
   


> Dear Expert.
>
> Following is Dataupdate Routine for my part of a project.
>
>
** MyParentCRS --> Updatable CurSor For SQL Database
> ** MyChildCRS  --> Updatable CurSor For SQL Database
> 
> ** Parent Table****
> ** Update Existing Rows 
> Update MyParentCRS set;
>        MyParentCRS.MyField1 = TmpParent.MyField1 ,;
>        MyParentCRS.MyField2 = TmpParent.MyField2;
>  from  TmpParent ;
> Where  MyParentCRS.MyPkey = TmpParent.MyPkey
> ** Adding Fresh Rows
> Insert into  MyParentCRS (MyField1, MyField2) ;
>       SELECT MyField1, MyField2 FROM TmpParent ;
> where Not Exist (Select * from MyParentCRS where MyParentCRS.MyPkey = TmpParent.MyPkey)
> 
> **** Child Table ***
> ** Update Existing Rows 
> Update MyChildCRS set;
>        MyChildCRS.MyField1 = TmpChild.MyField1 ,;
>        MyChildCRS.MyField2 = TmpChild.MyField2;
>  from  TmpChild ;
> Where  MyChildCRS.MyPkey = TmpChild.MyPkey
> ** Adding Fresh Rows
> Insert into  MyChildCRS (MyField1, MyField2) ;
>       SELECT MyField1, MyField2 FROM TmpChild ;
> where Not Exist (Select * from MyChildCRS where MyChildCRS.MyPkey = TmpChild.MyPkey)
> 
> ** Finally Updating Datas to SQL Server
> 
> IF SQLnotConnected()
>    DO Something
>    RETURN .f.
> ENDIF
> LOCAL llUpdated
> 
> BEGIN TRANSACTION 
> DO case
> CASE NOT TABLEUPDATE(1, .f., 'MyParentCRS')
>      AERROR(SqlErr)
>      MESSAGEBOX(Sqlerr(1,2),16,'SQL Err')
>      RollBack
> CASE NOT TABLEUPDATE(1, .f., 'MyChildCRS')
>      AERROR(SqlErr)
>      MESSAGEBOX(Sqlerr(1,2),16,'SQL Err')
>      RollBack
> OTHERWISE
>      MESSAGEBOX('Data Updated to Server Properly! ')
>      m.llUpdated = .t.
>      END TRANSACTION 
> ENDCASE
-> 
> IF NOT llupdated
>   ** Since Database is not updated properly
>   Messagebox("Unable to Updated Records...Reverting.....")
>   =Tablerevert(.T., 'MyParentCRS')
>   =Tablerevert(.T., 'MyChildCRS')
>   Return .F.
> Else
> 	Return .T.
> Endif
>           

>
>
> it is working fine when there is no error in any one of the tables. But when
>
> updates fails I had seen that there is either records in Parent Table whose child
> table have no records or vise-versa.
>
> How to use 'Transactions' function in SQL Server.
>
> it wud be great help if i found some sample.
>
> Please.
>
>
>
>
>
>
> *Exchange of $1 Create only $1, But Exchage of one Ideas Makes different two Ideas*


You need to recheck your coding. Should use if instead of case.


Anil


Accounting Software

Web Development

Accounting, Inventory, Hotel Management, Restaurant Kot Billing, MLM, Excise Accounting and much much more!.
My Blog

ENTIRE THREAD

SQL Transactions Posted by Binod Binani @ 12/22/2012 6:04:21 AM
RE: SQL Transactions Posted by David Mustakim @ 12/22/2012 7:24:31 AM
RE: SQL Transactions Posted by Michel Levy @ 12/22/2012 1:09:08 PM
RE: SQL Transactions Posted by Binod Binani @ 12/24/2012 6:40:49 AM
RE: SQL Transactions Posted by Michel Levy @ 12/24/2012 9:53:06 AM
RE: SQL Transactions Posted by Anders Altberg @ 12/22/2012 1:52:03 PM
RE: SQL Transactions Posted by Michel Levy @ 12/22/2012 2:58:43 PM
RE: SQL Transactions Posted by Anders Altberg @ 12/22/2012 7:07:49 PM
RE: SQL Transactions Posted by Anil Sharma @ 12/24/2012 8:47:47 AM