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
Charles Hankey
Though a good deal is too strange to be believed, nothing is too strange to have happened.
- Thomas Hardy
Half the harm that is done in this world is due to people who want to feel important. They don't mean to do harm-- but the harm does not interest them. Or they do not see it, or they justify it because they are absorbed in the endless struggle to think well of themselves.
-- T. S. Eliot
Democracy is two wolves and a sheep voting on what to have for lunch.
Liberty is a well-armed sheep contesting the vote.
- Ben Franklin
Pardon him, Theodotus. He is a barbarian, and thinks that the customs of his tribe and island are the laws of nature.
