Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Anders Altberg
  Where is Anders Altberg?
 Uppsala
 Sweden
 Anders Altberg
 To: Binod Binani
  Where is Binod Binani?
 Kolkata
 India
 Binod Binani
 Tags
Subject: RE: SQL Transactions
Thread ID: 365203 Message ID: 365225 # Views: 48 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Saturday, December 22, 2012 1:52:03 PM         
   


> 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*

If you take the course that Michel Levy posted you shouöd first check up the server DB documentation about which kinds of serverside views are updatable; not all of them are.

-Anders

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