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.CreateCommand();

comInsert.CommandType = CommandType.Text;

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

comInsert.ExecuteNonQuery();

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.CreateCommand();
DR>
DR> comInsert.CommandType = CommandType.Text;
DR>
DR> comInsert.CommandText = "Insert Into MyTable ( Col1, Col2)
DR> Values(1,2)";
DR>
DR> comInsert.ExecuteNonQuery();
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.com> wrote in message
news:9c**************************@msnews.microsoft .com...

Hello Doru,

Call SELECT @@idetity

DR> I insert a new record with a command:
DR> DR> private SqlCommand comInsert;
DR> DR> comInsert = conDataBase.CreateCommand();
DR> DR> comInsert.CommandType = CommandType.Text;
DR> DR> comInsert.CommandText = "Insert Into MyTable ( Col1, Col2)
DR> Values(1,2)";
DR> DR> comInsert.ExecuteNonQuery();
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.com> wrote in message
news:9c**************************@msnews.microsoft .com...

Hello Doru,

Call SELECT @@idetity

DR> I insert a new record with a command:
DR> DR> private SqlCommand comInsert;
DR> DR> comInsert = conDataBase.CreateCommand();
DR> DR> comInsert.CommandType = CommandType.Text;
DR> DR> comInsert.CommandText = "Insert Into MyTable ( Col1, Col2)
DR> Values(1,2)";
DR> DR> comInsert.ExecuteNonQuery();
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.com> wrote in message
news:9c**************************@msnews.microsoft .com...

Hello Doru,

Call SELECT @@idetity

DR> I insert a new record with a command:
DR> DR> private SqlCommand comInsert;
DR> DR> comInsert = conDataBase.CreateCommand();
DR> DR> comInsert.CommandType = CommandType.Text;
DR> DR> comInsert.CommandText = "Insert Into MyTable ( Col1, Col2)
DR> Values(1,2)";
DR> DR> comInsert.ExecuteNonQuery();
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.machin AT dot.state.fl.us> wrote
in message news:e6**************@TK2MSFTNGP11.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.com> wrote in message
news:9c**************************@msnews.microsoft .com...

Hello Doru,

Call SELECT @@idetity

DR> I insert a new record with a command:
DR> DR> private SqlCommand comInsert;
DR> DR> comInsert = conDataBase.CreateCommand();
DR> DR> comInsert.CommandType = CommandType.Text;
DR> DR> comInsert.CommandText = "Insert Into MyTable ( Col1, Col2)
DR> Values(1,2)";
DR> DR> comInsert.ExecuteNonQuery();
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(string logName, string msgID, string refID,
DateTime date, string from, string subject, string path, DateTime expires,
string body)

{

SqlCommand cmd;

using (SqlConnection conn = new SqlConnection(ConnectionString))

{

cmd = new SqlCommand("InsertMessage", conn);

cmd.CommandType = CommandType.StoredProcedure;

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

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

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

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

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

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

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

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

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

conn.Open();

int seq = (int)cmd.ExecuteScalar();

return seq;

}

}
--
William Stacey [MVP]

[Otis Mukinfus]

On Mon, 27 Mar 2006 13:45:14 -0500, "Doru Roman" <do*******@rogers.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.com> wrote in message
news:9c**************************@msnews.microsof t.com...

Hello Doru,

Call SELECT @@idetity

DR> I insert a new record with a command:
DR> DR> private SqlCommand comInsert;
DR> DR> comInsert = conDataBase.CreateCommand();
DR> DR> comInsert.CommandType = CommandType.Text;
DR> DR> comInsert.CommandText = "Insert Into MyTable ( Col1, Col2)
DR> Values(1,2)";
DR> DR> comInsert.ExecuteNonQuery();
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