Re: SCOPE_IDENTITY() returns NULL
From: Tumurbaatar S. (nospam_tumurbaatar_at_datacom.mn)
Date: 11/15/04
- Next message: PZ: "Sequential Dates and Grouping"
- Previous message: Aaron [SQL Server MVP]: "Re: Dynamic SQL"
- In reply to: avnrao: "Re: SCOPE_IDENTITY() returns NULL"
- Next in thread: Richard Wilde: "Re: SCOPE_IDENTITY() returns NULL"
- Reply: Richard Wilde: "Re: SCOPE_IDENTITY() returns NULL"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 15 Nov 2004 22:01:17 +0800
My program uses Delphi ADO VCL components:
...
Command.Text = "INSERT...";
Command.Execute();
Query.Text = "SELECT SCOPE_IDENTITY()";
Query.Open();
...
I traced with SQL Profiler and it seems that between above
Execute() and Open() there many internal commands are
going on between a server and my program. May be they're
resetting SCOPE_IDENTITY.
Thanks!
"avnrao" <avn@newsgroups.com> wrote in message
news:eaUyNwwyEHA.3996@TK2MSFTNGP10.phx.gbl...
> you have to capture SCOPE_IDENTITY() just after the insert statement and
> place it into a local variable.
> can you post your code??
>
> Av.
> http://dotnetjunkies.com/WebLog/avnrao
> http://www28.brinkster.com/avdotnet
>
> "Tumurbaatar S." <nospam_tumurbaatar@datacom.mn> wrote in message
> news:#MyygrwyEHA.4044@TK2MSFTNGP10.phx.gbl...
>> My program executes INSERT on a table with an identity column and
>> SELECTs back a new record id with "SELECT SCOPE_IDENTITY()".
>> This works without an error. After it, the program inserts a new record
>> into a detail table using the id value obtained in the previous step.
>> All above INSERTs are executing within one explicit transaction.
>> But SQL raises an exception that NULL value was sent for id.
>> I've traced my program and found that SCOPE_IDENTITY() really
>> returns NULL.
>> In the BOL I found this:
>>
>> ...SCOPE_IDENTITY() will return the IDENTITY value inserted in T1, which
> was
>> the last INSERT that occurred in the same scope. The SCOPE_IDENTITY()
>> function will return the NULL value if the function is invoked before any
>> insert statements into an identity column occur in the scope.
>>
>> That is not clear for me. Anybody can explain this?
>> Thank you
>>
>>
>
>
- Next message: PZ: "Sequential Dates and Grouping"
- Previous message: Aaron [SQL Server MVP]: "Re: Dynamic SQL"
- In reply to: avnrao: "Re: SCOPE_IDENTITY() returns NULL"
- Next in thread: Richard Wilde: "Re: SCOPE_IDENTITY() returns NULL"
- Reply: Richard Wilde: "Re: SCOPE_IDENTITY() returns NULL"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
