canot update identity column

[future3839]

Hi, I am running VS2008 and use SQLExpress. I craete a SP in my database. then when I wanna save it I will get this error : Cannot update identity column 'Code' the above code is my SP

Create PROCEDURE dbo.SprocInsertUpdatePerson
(
@code int,
@id int,
@name nvarchar(50),
@passportNumber nvarchar(50),
@nationality nvarchar(50),
@created datetime,
@creator nvarchar(50)
)
AS

Declare @ReturnValue int
if (@Code IS NULL) --New Item
Begin
	insert into Person
	(
		Code,
		Id,
		Name,
		PassportNumber,
		Nationality,
		Created,
		Creator
	)	
	values
	(
		@code,
		@id,
		@name,
		@passportNumber,
		@nationality,
		@created,
		@creator
	)
	
	Select @ReturnValue = Scope\_Identity()
	
End


else

Begin --Update Item
	Update Person  		
	Set Code = @code,
	Id = @id,
	Name = @name,
	PassportNumber = @passportNumber,
	Nationality = @nationality,
	Created = @created,
	Creator = @creator				
	where Id = @id
	
	Select @ReturnValue = @Id
End


IF (@@ERROR != 0)
BEGIN
	RETURN -1
END
ELSE
BEGIN
	RETURN @ReturnValue
END

I really appreciate if someone help me. thx a lot

[Lost User]

You can remove it from the insert-statement; an IDENTITY[^] is automatically generated when a new record is inserted. Kinda like an autonumber-column in Microsoft Access. The Scope_Identity would then return that newly generated value.

I are Troll :suss:

[NeverHeardOfMe]

The error message has told you the problem: you cannot update teh identity column. Values for this column are automatically set by the database engine when you add a new record. They are read-only after that, as a unique identifier for that row. So you should not try to set the ID when updating records, just use it in the WHERE clause to specify which row to update.

[Bernhard Hiller]

Have you tried

SET IDENTITY_INSERT table ON

I use it when migrating data from an old database into an empty database. But normally, IDENTITY should be set by the database, not by your code.

[future3839]

Hi, thanks for your answer; I will get the solution in the last post.