Hi, all.

Here is something we have been chasing down.

Tehe code bellow reproduce a situation in our framework when a diffgram comes from the business tier to the data component, and we need to loop trough the diffgram by hand (not needed in this example, but in certain cases to update the child tabls foreign keys).

Information is passed between tiers as XML (using XmlAdapter), and the problem is when we use ADO to update the database with a CursorAdapter.

The problem is that each time we issue a TableUpdate, the transaction ends on the SQL Server, so we don't have a chance to commit or rollback conditionally at the end.

Any hint would be greatly appreciated.

Close Databases all 
Set Multilocks On 
Clear 

Public cConnString As String

*** ### SET FOR YOUR SERVER ###
***
cConnString = [Provider=SQLOLEDB.1;Integrated Security=SSPI;" ;
 + "Persist Security Info=False;Initial Catalog=Northwind;Data Source=SaliasMovil]

* Manually generate the diffgram. 
* Emulates the diffgram coming from the biz/user tier.
Local lcMyDiff As String 
lcMyDiff = GenDiffGram()  && See UDF GenDiffGram() code below.

Public oConn As ADODB.Connection 
Local oComm As ADODB.Command
Local oCA As CursorAdapter, oXA As XMLAdapter

oConn = CreateObject( 'ADODB.Connection' )
oConn.Open(cConnString)

oComm = CreateObject( 'ADODB.Command' )
oComm.ActiveConnection = oConn

oXA = CreateObject( 'XMLAdapter' )
With oXA
	.LoadXML( lcMyDiff )  && The XML Diffgram.
	.Tables( 1 ).ChangesToCursor()
EndWith 

oCA = CreateObject( 'CursorAdapter' )
With oCA
	.FetchSize = -1
	.Tables = [Customers]
	.KeyFieldList = [CustomerID]
	.UpdatableFieldList = [CompanyName]
	.UpdateNameList= [CustomerID Customers.CustomerID, ] ;
	 + [CompanyName Customers.CompanyName]
	.CursorAttach( [cCustomers] )
	.UpdateCmdDataSourceType = "ADO"
	.UpdateCmdDataSource = oComm
EndWith 

oConn.execute( 'BEGIN TRANSACTION' )

Select cCustomers
Scan

	? Recno(), CustomerID, CompanyName, ;
	 'TranCount before TableUpdate: ' ;
	 + Transform(Execute( 'SELECT @@TRANCOUNT' ))
	
	If TableUpdate(0,.F.)
	Else 
		= AERROR( laErrorArray )
		MessageBox( Transform(laErrorArray( 1 )) + ' ' + laErrorArray( 2 ) )
	EndIf 
EndScan

? 'TranCount after all TableUpdates: ' ;
 + Transform(Execute( 'SELECT @@TRANCOUNT' ))

* If I want to use one of the following commands, an errors occurs because
* there is not an active transaction.

* oConn.execute( 'ROLLBACK TRANSACTION' )
* oConn.execute( 'COMMIT TRANSACTION' )		

Return

*------------------------
*------------------------
*------------------------

Procedure GenDiffGram

Local oConn As ADODB.Connection 
Local oRS As ADODB.Recordset 
Local oCA As CursorAdapter, oXA As XMLAdapter

oConn = CreateObject( 'ADODB.Connection' )
oConn.Open(cConnString)

oRS = CreateObject( 'ADODB.Recordset' )
WITH oRS 
	.CursorLocation 	= 3 &&adUseClient
	.LockType 			= 3 &&adLockOptimistic
	.ActiveConnection 	= oConn
EndWith

oCA = CreateObject( 'CursorAdapter' )
With oCA
	.DataSourceType = 'ADO'
	.DataSource = oRS 
	.FetchSize = -1
	.Tables = [Customers]
	.Alias = [cCustomers]
	.SelectCmd = [select customerid, companyname ] ;
	 + [from customers where country = 'Brazil']
	.CursorFill()
	.CursorDetach()
	CursorSetProp('Buffering',5,'cCustomers')
EndWith 

* Loop trought the brazilian customers (9) with 
* CustomerID starting with "Q" (2 out of 9)
Select cCustomers
Scan for Left( CustomerID, 1 ) = "Q"
	
	* Just to make a change in the records, adds
	* or removes a string to the name
	* (so we can run the sample multiple times)
	
	If Left( CompanyName, 2 ) = "# "
		replace CompanyName with Substr( CompanyName, 3 )
	Else
		replace CompanyName with "# " + CompanyName
	EndIf 
EndScan 

Local lcMyDiff As String 
oXA = CreateObject( 'XMLAdapter' )
With oXA
	.AddTableSchema( 'cCustomers' )
	.PreserveWhiteSpace = .T.
	.IsDiffgram = .T.
	.ToXML( 'lcMyDiff', '', .F., .T., .T. )
EndWith 

USE in cCustomers

*StrToFile( lcMyDiff, 'MyDiff.Xml', 0 )
Return lcMyDiff

*------------------------
*------------------------
*------------------------

Procedure Execute( tcSQLStat As String ) As String
	Local oRS as ADODB.RecordSet, lcRetVal As String 
	oRS = oConn.Execute( tcSQLStat )
	lcRetVal = oRS.Fields(0).Value
	Return lcRetVal
EndProc 

Thanks to all,