Cannot update identity column

Question

I want to edit data in database but if I tried to edit any value in a row, an error message

Cannot update identity column 'ProductID'

appeared after trying to update the data. I'm just a beginner, so I have no idea where the bug is.

<asp:SqlDataSource ID="Adventure" runat="server" 
     ConnectionString="<%$ ConnectionStrings:ConStrAdventure %>" 
     DeleteCommand="DELETE FROM [Production].[Product] WHERE [ProductID] = @ProductID" 
     InsertCommand="INSERT INTO [Production].[Product]  ([ProductID], [Name], [ProductNumber], [MakeFlag], [FinishedGoodFlag], [Color], [SafetyStockLevel], [ReorderPoint], [StandardCost], [ListPrice], [Size], [SizeUnitMeasureCode], [WeightUnitMeasureCode], [Weight], [DaysToManufacture], [ProductLine], [Class], [Style], [ProductSubcategoryID], [ProductModelID], [SellStartDate], [SellEndDate], [DiscontinuedDate], [rowguid], [ModifiedDate]) VALUES (@ProductID, @Name, @ProductNumber, @MakeFlag, @FinishedGoodFlag, @Color, @SafetyStockLevel, @ReorderPoint, @StandardCost, @ListPrice, @Size, @SizeUnitMeasureCode, @WeightUnitMeasureCode, @Weight, @DaysToManufacture, @ProductLine, @Class, @Style, @ProductSubcategoryID, @ProductModelID, @SellStartDate, @SellEndDate, @DiscontinuedDate, @rowguid, @ModifiedDate) " SelectCommand="SELECT * FROM [Production].[Product]" 
     UpdateCommand="UPDATE [Production].[Product] SET [ProductID] = @ProductID,[Name] = @Name, [ProductNumber] = @ProductNumber, [MakeFlag] = @MakeFlag, [FinishedGoodsFlag] = @FinishedGoodsFlag, [Color] = @Color, [SafetyStockLevel] = @SafetyStockLevel, [ReorderPoint] = @ReorderPoint, [StandardCost] = @StandardCost, [ListPrice] = @ListPrice, [Size] = @Size, [SizeUnitMeasureCode] = @SizeUnitMeasureCode, [WeightUnitMeasureCode] = @WeightUnitMeasureCode, [Weight] = @Weight, [DaysToManufacture] =  @DaysToManufacture, [ProductLine] = @ProductLine, [Class] = @Class, [Style] = @Style, [ProductSubcategoryID] = @ProductSubcategoryID, [ProductModelID] = @ProductModelID, [SellStartDate] = @SellStartDate, [SellEndDate] = @SellEndDate, [DiscontinuedDate] = @DiscontinuedDate, [rowguid] = @rowguid, [ModifiedDate] = @ModifiedDate WHERE [ProductID] = @ProductID">

The second part of the code here as image.

Thank you in advance for any ideas.

I can make no sense of this wall of code. Please format your code so it's at least readable. Judging from the error message, you appear to be updating an identity column; you shouldn't want to do that. — HoneyBadger, Nov 14 '17 at 14:47
Look at this : https://stackoverflow.com/questions/3947453/update-values-in-identity-column — Sankar, Nov 14 '17 at 14:48
Possible duplicate of [Update values in identity column](https://stackoverflow.com/questions/3947453/update-values-in-identity-column) — Sankar, Nov 14 '17 at 14:50
You just can't update the identity column. That is all point of using that data type. — cap7, Nov 14 '17 at 15:15
An `IDENTITY` column just plain and simple **cannot** be updated - there's no hack, workaround, trick, method or anything to achieve this. You can *insert* specific values into an `IDENTITY` column, if you really must - but once a value is set, it's done - no chance to update and change it. But really: you should ***never*** be changing / updating your primary key in a table anyway ..... — marc_s, Nov 14 '17 at 16:26

score 2 · Answer 1 · edited Nov 14 '17 at 16:04

2

Your update command begins,

UpdateCommand="UPDATE [Production].[Product] SET [ProductID] = @ProductID,[Name],.....

The error message is telling you you can't update [ProductID] as it's an 'identity column' , that is your primary key. You can't update your primary key, that's what identifies the record. Just drop [ProductID] = @ProductID from the list of column/data pairs in your UpdateCommand.

edited Nov 14 '17 at 16:04

pix

1,264
19
32

answered Nov 14 '17 at 15:17

OTTA

1,071
7
8

It doesnt work. Error message just changed to "The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated." – Rob Nov 14 '17 at 19:26
@Rob "It doesn't work": no, you just got a new error message. You should solve that error and try again. There may be many errors to solve. Also, ask yourself *why* you are trying to update your primary key at all. What are you trying to replace the existing value with, and to what end? – HoneyBadger Nov 14 '17 at 21:27
@Rob, you need to understand that a different error message is generally progress, you've resolved one problem but by doing so have uncovered another one. Your new error message is saying that the value you're supplying for one of your date columns isn't a valid date. – OTTA Nov 15 '17 at 13:28

Cannot update identity column

1 Answers1