>We are using a cursoradapter to connect to a SQL Server database through ADO. The CA in question selects data from two related tables. The problem is that the SQL database has standard referantial integrity rules that states that data in the parent table must be present if an insert is to be allowed in the child table. Therefore, when we call TableUpdate on the CA, we need to control the order in which the inserts occur - one _must_ happen before the other, otherwise the update will fail.
>
>The help for the tables property of the CA says the following:
>
>"CursorAdapter.Tables [= cList]
>
>The cList parameter specifies a comma-delimited list of table names that displays the table names in the exact order you want them to appear in the SQL UPDATE, INSERT, and DELETE commands."
>
>However, this does not seem to work. No matter what order we specify in the tables list, the updates always occur in the same (and in this case, wrong) order (we can see this from the SQL Profiler).
>
>Any help would be greatly appreciated.
>
>Thanks,
>
>Eyvind.

Hi Eyvind,

I see exactly opposite behavior.

Here is the code:

SET MULTILOCKS ON
CLEAR 
CLOSE DATABASES all

LOCAL oConn as ADODB.Connection, oRS as ADODB.Recordset, oCom as ADODB.Command

oConn=CREATEOBJECT("ADODB.Connection")
oConn.Open("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=(local)")


oConn.Execute("Create table #table1 (f1 int)")
oConn.Execute("Create table #table2 (f2 int)")

oRS=CREATEOBJECT("ADODB.Recordset")
oRS.ActiveConnection=oConn
oCom=CREATEOBJECT("ADODB.Command")
oCom.ActiveConnection=oConn

LOCAL oCA as CursorAdapter
oCA=CREATEOBJECT("CursorAdapter")
oCA.Alias="test"
oCA.DataSourceType="ADO"
oCA.DataSource=oRS
oCA.SelectCmd="select * from #table1,#table2"
oCA.UpdatableFieldList="f1,f2"
oCA.UpdateNameList="f1 #table1.f1, f2 #table2.f2"
oCA.InsertCmdDataSourceType="ADO"
oCA.InsertCmdDataSource=oCom

oCA.CursorFill()
LIST 

oCA.Tables="#table1, #table2"
INSERT INTO test VALUES (1,2)
TABLEUPDATE(.F.)

oCA.Tables="#table2, #table1"
INSERT INTO test VALUES (3,4)
TABLEUPDATE(.F.)

RETURN

Here is the Sql Profiler trace:

SQL:BatchCompleted	Create table #table1 (f1 int)
SQL:BatchCompleted	Create table #table2 (f2 int)
SQL:BatchCompleted	select * from #table1,#table2
SQL:BatchCompleted	SET FMTONLY ON select f1,f1 from #table1 SET FMTONLY OFF
SQL:BatchCompleted	set implicit_transactions on
RPC:Completed	
	declare @P1 int
	set @P1=1
	exec sp_prepexec @P1 output, N'@P1 int,@P2 int', 
	N'INSERT INTO #table1 (f1) VALUES (@P1 )
INSERT INTO #table2 (f2) VALUES (@P2 )',
	1, 2
	select @P1	56	
SQL:BatchCompleted	IF @@TRANCOUNT > 0 COMMIT TRAN
SQL:BatchCompleted	set implicit_transactions off
RPC:Completed	exec sp_unprepare 1
SQL:BatchCompleted	SET FMTONLY ON select f2,f2 from #table2 SET FMTONLY OFF
SQL:BatchCompleted	set implicit_transactions on
RPC:Completed	
	declare @P1 int
	set @P1=2
	exec sp_prepexec @P1 output, N'@P1 int,@P2 int', 
	N'INSERT INTO #table2 (f2) VALUES (@P1 )
INSERT INTO #table1 (f1) VALUES (@P2 )', 
	4, 3
	select @P1
SQL:BatchCompleted	IF @@TRANCOUNT > 0 COMMIT TRAN
SQL:BatchCompleted	set implicit_transactions off
RPC:Completed	exec sp_unprepare 2	

Probably you configured the CursorAdapter to send updates through Recordset. If so, please refer to ADO documentation how to configure the Recordset object to send inserts in certain order. In this scenario, CursorAdapter doesn't use the Tables property, it modifies Recordset and the Recordset is in charge how it propagates changes to the backend.

Thanks,
Aleksey.