"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