473,320 Members | 1,713 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Finding Auto Increment Insert ID

Hello all:

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

Thanks,

John
Nov 17 '05 #1
8 5426

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.

Nov 17 '05 #2
wa********@yahoo.com wrote:
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.

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
Nov 17 '05 #3
Also if other people access the DB another record could be inserted in
b4 your query runs.

Nov 17 '05 #4
John Smith wrote:
wa********@yahoo.com wrote:
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.

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


HMMM,
Maybe SELECT MAX(id) MaxId FROM my_table

Then the ONLY record returned would be the id in question

JB ;)
Nov 17 '05 #5
John B wrote:
John Smith wrote:
wa********@yahoo.com wrote:
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.

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

HMMM,
Maybe SELECT MAX(id) MaxId FROM my_table

Then the ONLY record returned would be the id in question

JB ;)


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 MyTableWith500000RecordsAnd20Fields
ORDER BY id desc'"

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

And this was EVERYWHERE.

Monkeys

JB
Nov 17 '05 #6
"John Smith" <oh******@hotmail.com> wrote in message
news:e7****************@TK2MSFTNGP15.phx.gbl...
wa********@yahoo.com wrote:
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.

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


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
ParameterDirection.Input and one as ParameterDirection.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
Nov 17 '05 #7
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 ***
Nov 17 '05 #8
thats what locks and transactions are good for.
<wa********@yahoo.com> schrieb im Newsbeitrag
news:11**********************@o13g2000cwo.googlegr oups.com...
Also if other people access the DB another record could be inserted in
b4 your query runs.

Nov 17 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: csomberg | last post by:
SQL 2000 I thought I would throw this out there for some feedback from others. I'd like to know if you feel using MS auto-increment field is a good solution these days or should one grow their...
2
by: Tom | last post by:
I am trying to store information into a table that has an auto increment field. There is currently no data in the table. Using the code below I cannot insert data into the table. I get an error...
5
by: Pauloviè Michal | last post by:
hi all, I have problem with SERIAL field type (or sequence functionality). I have table with three columns - ID, IDS, NAME. I want auto-increment IDS grouped by ID. Example: 1, 1, Ferdo 1, 2,...
2
by: alexs | last post by:
Chaps, I'm starting to play with db2 V9.1 and am writing a stored procedure to manage accounting records from oiur RADIUS server. I've got an XML aware table with an auto increment primary...
1
by: rhepsi | last post by:
hii all, i have created a database with a table with number of fields....in msaccess ive set primary key as SNo: and its datatype as auto number... How to set the auto increment ???? ...
0
chumlyumly
by: chumlyumly | last post by:
Hello scripters - OS: Mac OSX Language: PHP w/ MySQL database I've created an insert page where a user inputs his info, which then goes to four different tables in a MySQL database. The...
2
by: skipwater | last post by:
I have two db each have the same table called manufacturers and same fields. db1 has a field called man_ID which is a auto increment field. This contains 90 records but over time the records have...
13
by: BobLewiston | last post by:
Using Visual C# 2008 Express and SQL Server 2008 Express, I would like to insert new records into database "AdventureWorks", table "Person.Contact". To my surprise, this table's int-value identity...
1
by: ghjk | last post by:
I'm a new to postgres. i have a table which is having a colomn with auto increment value. When I insert value to that table, it gives an error. First column is the auto increment one....
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.