How can I get the Identity value of a newly inserted record?

[Doru Roman]

Hi,

I insert a new record with a command:

private SqlCommand comInsert;

comInsert = conDataBase.Cre ateCommand();

comInsert.Comma ndType = CommandType.Tex t;

comInsert.Comma ndText = "Insert Into MyTable ( Col1, Col2) Values(1,2)";

comInsert.Execu teNonQuery();

Col3 is an Identity column which autoincrements. How can I get the value of
the inserted record

immediately after, in an multi-user environment, on a MS SQL DB?

Thanks,

Doru

[Bruce Wood]

Check out the following article:

http://msdn.microsoft.com/library/de...anidcrisis.asp

[Michael Nemtsev]

Hello Doru,

Call SELECT @@idetity

DR> I insert a new record with a command:
DR>
DR> private SqlCommand comInsert;
DR>
DR> comInsert = conDataBase.Cre ateCommand();
DR>
DR> comInsert.Comma ndType = CommandType.Tex t;
DR>
DR> comInsert.Comma ndText = "Insert Into MyTable ( Col1, Col2)
DR> Values(1,2)";
DR>
DR> comInsert.Execu teNonQuery();
DR>
DR> Col3 is an Identity column which autoincrements. How can I get the
DR> value of the inserted record
DR>
DR> immediately after, in an multi-user environment, on a MS SQL DB?
DR>
DR> Thanks,
DR>
DR> Doru
DR>
---
WBR,
Michael Nemtsev :: blog: http://spaces.msn.com/laflour

"At times one remains faithful to a cause only because its opponents do not
cease to be insipid." (c) Friedrich Nietzsche

[Doru Roman]

Thanks Michael for the reply.
Would that return only my @@identity or it might return some other user's
value if that user got a new value inserted immediately after me?
"Michael Nemtsev" <ne*****@msn.co m> wrote in message
news:9c******** *************** ***@msnews.micr osoft.com...

Hello Doru,

Call SELECT @@idetity

DR> I insert a new record with a command:
DR> DR> private SqlCommand comInsert;
DR> DR> comInsert = conDataBase.Cre ateCommand();
DR> DR> comInsert.Comma ndType = CommandType.Tex t;
DR> DR> comInsert.Comma ndText = "Insert Into MyTable ( Col1, Col2)
DR> Values(1,2)";
DR> DR> comInsert.Execu teNonQuery();
DR> DR> Col3 is an Identity column which autoincrements. How can I get the
DR> value of the inserted record
DR> DR> immediately after, in an multi-user environment, on a MS SQL DB?
DR> DR> Thanks,
DR> DR> Doru
DR> ---
WBR,
Michael Nemtsev :: blog: http://spaces.msn.com/laflour

"At times one remains faithful to a cause only because its opponents do
not cease to be insipid." (c) Friedrich Nietzsche

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

Also note that you have to change from ExecuteNonQuery to ExecuteScalar

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"Michael Nemtsev" <ne*****@msn.co m> wrote in message
news:9c******** *************** ***@msnews.micr osoft.com...

Hello Doru,

Call SELECT @@idetity

DR> I insert a new record with a command:
DR> DR> private SqlCommand comInsert;
DR> DR> comInsert = conDataBase.Cre ateCommand();
DR> DR> comInsert.Comma ndType = CommandType.Tex t;
DR> DR> comInsert.Comma ndText = "Insert Into MyTable ( Col1, Col2)
DR> Values(1,2)";
DR> DR> comInsert.Execu teNonQuery();
DR> DR> Col3 is an Identity column which autoincrements. How can I get the
DR> value of the inserted record
DR> DR> immediately after, in an multi-user environment, on a MS SQL DB?
DR> DR> Thanks,
DR> DR> Doru
DR> ---
WBR,
Michael Nemtsev :: blog: http://spaces.msn.com/laflour

"At times one remains faithful to a cause only because its opponents do
not cease to be insipid." (c) Friedrich Nietzsche

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

It's better to use SCOPE_IDENTITY( )
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"Michael Nemtsev" <ne*****@msn.co m> wrote in message
news:9c******** *************** ***@msnews.micr osoft.com...

Hello Doru,

Call SELECT @@idetity

DR> I insert a new record with a command:
DR> DR> private SqlCommand comInsert;
DR> DR> comInsert = conDataBase.Cre ateCommand();
DR> DR> comInsert.Comma ndType = CommandType.Tex t;
DR> DR> comInsert.Comma ndText = "Insert Into MyTable ( Col1, Col2)
DR> Values(1,2)";
DR> DR> comInsert.Execu teNonQuery();
DR> DR> Col3 is an Identity column which autoincrements. How can I get the
DR> value of the inserted record
DR> DR> immediately after, in an multi-user environment, on a MS SQL DB?
DR> DR> Thanks,
DR> DR> Doru
DR> ---
WBR,
Michael Nemtsev :: blog: http://spaces.msn.com/laflour

"At times one remains faithful to a cause only because its opponents do
not cease to be insipid." (c) Friedrich Nietzsche

[Doru Roman]

Thanks Ignatio
"Ignacio Machin ( .NET/ C# MVP )" <ignacio.mach in AT dot.state.fl.us > wrote
in message news:e6******** ******@TK2MSFTN GP11.phx.gbl...

Hi,

It's better to use SCOPE_IDENTITY( )
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"Michael Nemtsev" <ne*****@msn.co m> wrote in message
news:9c******** *************** ***@msnews.micr osoft.com...

Hello Doru,

Call SELECT @@idetity

DR> I insert a new record with a command:
DR> DR> private SqlCommand comInsert;
DR> DR> comInsert = conDataBase.Cre ateCommand();
DR> DR> comInsert.Comma ndType = CommandType.Tex t;
DR> DR> comInsert.Comma ndText = "Insert Into MyTable ( Col1, Col2)
DR> Values(1,2)";
DR> DR> comInsert.Execu teNonQuery();
DR> DR> Col3 is an Identity column which autoincrements. How can I get
the
DR> value of the inserted record
DR> DR> immediately after, in an multi-user environment, on a MS SQL DB?
DR> DR> Thanks,
DR> DR> Doru
DR> ---
WBR,
Michael Nemtsev :: blog: http://spaces.msn.com/laflour

"At times one remains faithful to a cause only because its opponents do
not cease to be insipid." (c) Friedrich Nietzsche

[William Stacey [MVP]]

Here is example of a proc I just did. It uses real exceptions instead of
returning exit codes.

ALTER PROCEDURE [dbo].[InsertMessage]
@LogName nvarchar(50),
@MsgID nvarchar(50),
@RefID nvarchar(50),
@Date datetime,
@From nvarchar(50),
@Subject nvarchar(100),
@Path nvarchar(200),
@Expires datetime,
@Body nvarchar(max)
AS
begin
set nocount on
declare @logid int
set @logid = -1

begin try
-- Get LogID from LogName.
select @logid = LogID
from Logs
where LogName = @LogName
if (@logid = -1)
raiserror ('LogName does not exist.', 16, 1);

-- Insert record
insert into [Messages]
([LogID]
,[Date]
,[MsgID]
,[RefID]
,[From]
,[Subject]
,[Path]
,[Expires]
,[Body])
values
(@logid
,@Date
,@MsgID
,@RefID
,@From
,@Subject
,@Path
,@Expires
,@Body)
if (@@rowcount < 1)
raiserror ('Insert failed.', -- Msg. Error_Number will be 50000.
16, -- Severity
1); -- State
declare @seq int
set @seq = Scope_Identity( )
select @seq
end try
begin catch
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;

SELECT
@ErrorMessage = ERROR_MESSAGE() ,
@ErrorSeverity = ERROR_SEVERITY( ),
@ErrorState = ERROR_STATE();

-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
end catch
end

And call it from client:
public static int AddMessage(stri ng logName, string msgID, string refID,
DateTime date, string from, string subject, string path, DateTime expires,
string body)

{

SqlCommand cmd;

using (SqlConnection conn = new SqlConnection(C onnectionString ))

{

cmd = new SqlCommand("Ins ertMessage", conn);

cmd.CommandType = CommandType.Sto redProcedure;

cmd.Parameters. Add("@LogName", SqlDbType.NVarC har, 50).Value =
logName;

cmd.Parameters. Add("@MsgID", SqlDbType.NVarC har, 50).Value = msgID;

cmd.Parameters. Add("@RefID", SqlDbType.NVarC har, 50).Value = refID;

cmd.Parameters. Add("@Date", SqlDbType.DateT ime).Value = date;

cmd.Parameters. Add("@From", SqlDbType.NVarC har, 50).Value = from;

cmd.Parameters. Add("@Subject", SqlDbType.NVarC har, 100).Value =
subject;

cmd.Parameters. Add("@Path", SqlDbType.NVarC har, 200).Value = path;

cmd.Parameters. Add("@Expires", SqlDbType.DateT ime).Value = expires;

cmd.Parameters. Add("@Body", SqlDbType.NVarC har).Value = body;

conn.Open();

int seq = (int)cmd.Execut eScalar();

return seq;

}

}
--
William Stacey [MVP]

[Otis Mukinfus]

On Mon, 27 Mar 2006 13:45:14 -0500, "Doru Roman" <do*******@roge rs.com> wrote:

Thanks Michael for the reply.
Would that return only my @@identity or it might return some other user's
value if that user got a new value inserted immediately after me?
"Michael Nemtsev" <ne*****@msn.co m> wrote in message
news:9c******* *************** ****@msnews.mic rosoft.com...

Hello Doru,

Call SELECT @@idetity

DR> I insert a new record with a command:
DR> DR> private SqlCommand comInsert;
DR> DR> comInsert = conDataBase.Cre ateCommand();
DR> DR> comInsert.Comma ndType = CommandType.Tex t;
DR> DR> comInsert.Comma ndText = "Insert Into MyTable ( Col1, Col2)
DR> Values(1,2)";
DR> DR> comInsert.Execu teNonQuery();
DR> DR> Col3 is an Identity column which autoincrements. How can I get the
DR> value of the inserted record
DR> DR> immediately after, in an multi-user environment, on a MS SQL DB?
DR> DR> Thanks,
DR> DR> Doru
DR> ---
WBR,
Michael Nemtsev :: blog: http://spaces.msn.com/laflour

"At times one remains faithful to a cause only because its opponents do
not cease to be insipid." (c) Friedrich Nietzsche

It certainly could return another users identity value, even from another table.

Use return scope_identity( ) instead:

search for scope_identity( ) in T-SQL Help
Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com