>Hi Fabio,
>
>SCOPE_IDENTITY() always returns 0 for me... and @@IDENTITY returns the wrong value (T2 table).
>
>If you have time, please just run the code below and then tell me if it happens with you too.
>
>First of all, create tables in SQL Server:
>
>
>USE tempdb
>GO
>CREATE TABLE T1 (
> Z_id int IDENTITY(1,1)PRIMARY KEY,
> Z_name varchar(20) NOT NULL)
>CREATE TABLE T2 (
> Y_id int IDENTITY(100,5)PRIMARY KEY,
> Y_name varchar(20) NULL)
>
>
>and then, create a trigger that inserts one record in T2 table each time we insert one record in T1 table.
>
>CREATE TRIGGER T1_trig
>ON T1
>FOR INSERT AS
> BEGIN
> INSERT T2 VALUES ('')
> END
>>
>So, everthing in SQL is ok... Now VFP code:
>
>nAutoRefreshConn=SQLSTRINGCONNECT("DRIVER=SQL Server;SERVER=(local);Trusted_Connection=Yes;database=tempdb")
>
>Local oCA As CursorAdapter
>
>oCA=CREATEOBJECT("CursorAdapter")
>
>oCA.Alias = "CATest"
>oCA.BufferModeOverride= 5
>oCA.DataSource= nAutoRefreshConn
>oCA.DataSourceType="ODBC"
>oCA.SelectCmd="select * from t1"
>oCA.CursorSchema="z_id I, z_name C(10)"
>oCA.UseCursorSchema= .T.
>oCA.Tables="t1"
>oCA.UpdatableFieldList="z_name"
>oCA.UpdateNameList="z_id t1.z_id, z_name t1.z_name"
>oCA.CursorFill()
>
>oCA.InsertCmdRefreshFieldList="z_id"
>oCA.InsertCmdRefreshCmd="SELECT SCOPE_IDENTITY()"
>
>* oCA.InsertCmdRefreshCmd="SELECT @@IDENTITY"
>* oCA.InsertCmdRefreshCmd="SELECT z_id From t1 Where z_id = @@IDENTITY"
>* oCA.InsertCmdRefreshCmd="SELECT z_id From t1 Where z_id = SCOPE_IDENTITY()"
>
>INSERT INTO CATest (z_name) VALUES ('Rodolfo')
>INSERT INTO CATest (z_name) VALUES ('Denise')
>INSERT INTO CATest (z_name) VALUES ('Carol')
>
>IF !TABLEUPDATE(.T.)
> ? "TABLEUPDATE is failed!!!"
> AERROR(aerrs)
> DISPLAY MEMORY LIKE aerrs
>ELSE
> LIST
>ENDIF
>SQLDisconnect(nAutoRefreshConn)
>Return .T.
>>
>If everthing runs like here, you will see something like that:
>
>Record# z_id z_name
>1 0 Rodolfo
>2 0 Denise
>3 0 Carol
>
>
>Then, if you run this code again, you will get:
>
>Record# z_id z_name
>1 1 Rodolfo
>2 2 Denise
>3 3 Carol
>4 0 Rodolfo
>5 0 Denise
>6 0 Carol
>
>
>Thanks a lot for help me!
Hi Rodolfo,
the use of a CA in automatic mode is all right for programs from "young marmots",
you hardly want to do something more complex you have to refer everything
(this is also worth for .NET)
In practice, all the models of access to the data that I have seen thin to they are wrong now,
and Orcas will be equal.
Well,
when VFP performs a TABLEUPDATE,
for every record it sends:
set implicit_transactions on
exec sp_executesql N'INSERT INTO t1 (z_name) VALUES (@P1 )', N'@P1 varchar(10)', 'the text '
IF @@TRANCOUNT > 0 COMMIT TRAN
set implicit_transactions off
and InsertRefresh
SELECT SCOPE_IDENTITY()
the sp_executesql creates a scope nested that it is released to the sp exit,
this ago him that SCOPE_IDENTITY() created by the INSERT inside the sp_executesql
is lost.
Then the SELECT SCOPE_IDENTITY() return the current connection's scope_Identity(),
and this is NULL ( VFP convert it to 0 )
Does this happen for every command VFP that contains a parameter!
Then this not fixes the issue:
InsertCmd = "INSERT INTO t1 (z_name) VALUES (?CATest.s_name)
A proof, go in Query Analyzer and send:
set implicit_transactions on
exec sp_executesql N'INSERT INTO t1 (z_name) VALUES (@P1 )', N'@P1 varchar(10)', 'Rodolfo '
-- this return NULL
SELECT SCOPE_IDENTITY()
IF @@TRANCOUNT > 0 COMMIT TRAN
set implicit_transactions off
-- this return NULL
SELECT SCOPE_IDENTITY()
Result,
@@IDENTITY works because it's scope is the connection.
In fact Aleksey has been very careful not to put SCOPE_IDENTITY().
@@IDENTITY works if there are no trigger or other insertions
SCOPE_IDENTITY() it works with the TRIGGER FOR (not with the INSTEAD)
but it is killed by the call VFP to sp_executesql.
Solution:
If you want to use triggers and give the inserted identity
you have to write you all the commands to hand without parameters.
If you want i can post a example that work.
Fabio
