Finding Auto Increment Insert ID

[John Smith]

Hello all:

Is there a way to determine the auto increment id from a newly inserted
record in the database?

Thanks,

John

[wackyphill@yahoo.com]

Re: Finding Auto Increment Insert ID


Only way I know is the use a stored procedure to do the insert. Have
the SP return the auto ID.

In SQLServer:

insert (blah) values (bleh)
RETURN @@IDENTITY

In the parameters for your SQLCommand obj. Have one whose direction is
returnValue instead of input.

This way you can retrieve the ID. But only after the record was
inserted into the DB.

No other way that I know of. Love to hear other people's suggestions
for alternate methods though.

[John Smith]

Re: Finding Auto Increment Insert ID

wackyphill@yaho o.com wrote:[color=blue]
> Only way I know is the use a stored procedure to do the insert. Have
> the SP return the auto ID.
>
> In SQLServer:
>
> insert (blah) values (bleh)
> RETURN @@IDENTITY
>
> In the parameters for your SQLCommand obj. Have one whose direction is
> returnValue instead of input.
>
> This way you can retrieve the ID. But only after the record was
> inserted into the DB.
>
> No other way that I know of. Love to hear other people's suggestions
> for alternate methods though.
>[/color]
Thanks for the tip. Someone else suggested that you could run a second
query after the insert which selects the new id such as:

"SELECT id FROM my_table ORDER BY id DESC"

Then the first record returned is the id in question. Of course this
has the extra overhead of a second query, but it works across all
database technologies.

John

[wackyphill@yahoo.com]

Re: Finding Auto Increment Insert ID

Also if other people access the DB another record could be inserted in
b4 your query runs.

[John B]

Re: Finding Auto Increment Insert ID

John Smith wrote:[color=blue]
> wackyphill@yaho o.com wrote:
>[color=green]
>> Only way I know is the use a stored procedure to do the insert. Have
>> the SP return the auto ID.
>>
>> In SQLServer:
>>
>> insert (blah) values (bleh)
>> RETURN @@IDENTITY
>>
>> In the parameters for your SQLCommand obj. Have one whose direction is
>> returnValue instead of input.
>>
>> This way you can retrieve the ID. But only after the record was
>> inserted into the DB.
>>
>> No other way that I know of. Love to hear other people's suggestions
>> for alternate methods though.
>>[/color]
> Thanks for the tip. Someone else suggested that you could run a second
> query after the insert which selects the new id such as:
>
> "SELECT id FROM my_table ORDER BY id DESC"
>
> Then the first record returned is the id in question. Of course this
> has the extra overhead of a second query, but it works across all
> database technologies.
>
> John[/color]

HMMM,
Maybe SELECT MAX(id) MaxId FROM my_table

Then the ONLY record returned would be the id in question

JB ;)

[John B]

Re: Finding Auto Increment Insert ID

John B wrote:[color=blue]
> John Smith wrote:
>[color=green]
>> wackyphill@yaho o.com wrote:
>>[color=darkred]
>>> Only way I know is the use a stored procedure to do the insert. Have
>>> the SP return the auto ID.
>>>
>>> In SQLServer:
>>>
>>> insert (blah) values (bleh)
>>> RETURN @@IDENTITY
>>>
>>> In the parameters for your SQLCommand obj. Have one whose direction is
>>> returnValue instead of input.
>>>
>>> This way you can retrieve the ID. But only after the record was
>>> inserted into the DB.
>>>
>>> No other way that I know of. Love to hear other people's suggestions
>>> for alternate methods though.
>>>[/color]
>> Thanks for the tip. Someone else suggested that you could run a
>> second query after the insert which selects the new id such as:
>>
>> "SELECT id FROM my_table ORDER BY id DESC"
>>
>> Then the first record returned is the id in question. Of course this
>> has the extra overhead of a second query, but it works across all
>> database technologies.
>>
>> John[/color]
>
>
> HMMM,
> Maybe SELECT MAX(id) MaxId FROM my_table
>
> Then the ONLY record returned would be the id in question
>
> JB ;)[/color]

Just to clarify, the reason I posted this was because I have seen some
VERY nasty shiite happen when some codedonkey goes "hmm, how do I get
the next id? I know 'SELECT * FROM MyTableWith5000 00RecordsAnd20F ields
ORDER BY id desc'"

Why is the program so slow after its been in production for a month?

And this was EVERYWHERE.

Monkeys

JB

[Cletus Van Damme]

Re: Finding Auto Increment Insert ID

"John Smith" <ohnnyj77@hotma il.com> wrote in message
news:e7%23GKn7a FHA.3184@TK2MSF TNGP15.phx.gbl. ..[color=blue]
> wackyphill@yaho o.com wrote:[color=green]
>> Only way I know is the use a stored procedure to do the insert. Have
>> the SP return the auto ID.
>>
>> In SQLServer:
>>
>> insert (blah) values (bleh)
>> RETURN @@IDENTITY
>>
>> In the parameters for your SQLCommand obj. Have one whose direction is
>> returnValue instead of input.
>>
>> This way you can retrieve the ID. But only after the record was
>> inserted into the DB.
>>
>> No other way that I know of. Love to hear other people's suggestions
>> for alternate methods though.
>>[/color]
> Thanks for the tip. Someone else suggested that you could run a second
> query after the insert which selects the new id such as:
>
> "SELECT id FROM my_table ORDER BY id DESC"
>
> Then the first record returned is the id in question. Of course this has
> the extra overhead of a second query, but it works across all database
> technologies.
>
> John[/color]

If you're going to try to do something like the SELECT statement you
mentioned above, at least minimize the performance import by using the
following:

SELECT TOP 1 id FROM my_table ORDER BY id DESC

However I'd recommend skipping that approach altogether, as I believe
someone else mentioned, it's possible someone else could insert a row before
your second query runs, giving you an eroneous result. I'd recommend the
following approach instead (this assumes your DB is SQL Server):

- put your SQL all within a stored procedure like so:

CREATE PROCEDURE my_proc

(
@val1 varchar(100),
@val2 varchar(100),
@id int OUTPUT
)
AS

INSERT INTO my_table (col1, col2)
VALUES (@val1, @val2)

SET @id = SCOPE_IDENTITY( )

GO

Within your C# code then, use a SqlCommand and pass in 3 parameters, two as
ParameterDirect ion.Input and one as ParameterDirect ion.Output to catch the
new ID value. I'd say this is the most reliable way to do what you are
looking for. No it's not portable across different DB platforms but I'm
sure you can do the same sort of thing on any mature DB system (Oracle,
Sybase, DB2, etc...).

CVD

[Jeff Louie]

Re: Finding Auto Increment Insert ID

My understanding was that SELECT @@identity would return the last
identity for the given _connection_ and any scope and SCOPE_IDENTITY( )
returns the last identity for the given connection and given scope which
could be of interest if the database used triggers. So you should be
able to wrap the insert and select scope_identity into a transaction and
get the valid ID as long as the connection is not being shared. A stored
procedure might be better solution, however.

Regards,
Jeff

*** Sent via Developersdex http://www.developersdex.com ***

[cody]

Re: Finding Auto Increment Insert ID

thats what locks and transactions are good for.


<wackyphill@yah oo.com> schrieb im Newsbeitrag
news:1118191794 .315831.241890@ o13g2000cwo.goo glegroups.com.. .[color=blue]
> Also if other people access the DB another record could be inserted in
> b4 your query runs.
>[/color]