>Can anyone think of a reason I'm getting a null back from an SPT puling scope_identity after doing an SPT insert inside a VFP transaction.
>
>If I SPT for data, I get it, including the new record just inserted.
>
>lcsql = "SELECT SCOPE_IDENTITY() as new_id"
>
>lnreturn = SQLEXEC(lnhandle,lcsql,"result")
>
>* result.new_id is null
>* tried creating an sp to do the same thing
>
>lcsql = "exec sp_scope_identity"
>
>
>lnreturn = SQLEXEC(lnhandle,lcsql,"result")
>
>* still null
>
>But if lcsql = "SELECT id from mytable"
>
>I get all the pks, including the new one for the insert
>
>VFP help says
>
>SQLEXEC(m.lnConn, "SELECT SCOPE_IDENTITY()", "job_id")
>
> ? "ID for added Job is " + LTRIM(STR(job_id.exp))
>
>works, so the only difference I can think of is that i am inside a VFP transaction when I make the call.
>
>I can probably use candidate info in record to get the pk, but curious as to if this is a known issue and if there is a best practice work around
>
>TIA

Scope_Identity should be called right after the insert statement in the same connection, e.g. send this command:

insert into ...
select scope_identity()

Otherwise if you want to send command standalone, use select @@IDENTITY. This will not return correct ID if your table has a trigger which does insert into a table with identity field.