.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Identity column cannot be updated - Please help

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :

Hi everyone!

I started a project where the database was installed/copied to the pc's on which the program(.net program) is installed. But soon afterwards, the database was moved to host on the internet (web africa). So now I'm working from that database. There was only 3 tables when the database was uploaded, but after the database was uploaded I created a new table.

The problem that I'm having is that when I try to update the databse it gives me the error:

"cannot update identity column"   -- which is the primary key of that table and which shouldn't be changed at all... This error only started with the new table that I created and only since it has been hosted....

This is what I've done:

if ((dataChange["TaskDateCompleted"] == System.DBNull.Value))

                this.Cursor = System.Windows.Input.Cursors.Arrow;
                (tblChatDataGrid.SelectedItem as DataRowView).Row["TaskDone"] = 1;
                (tblChatDataGrid.SelectedItem as DataRowView).Row[

View Complete Post

More Related Resource Links

Fetch Identity column just after inserting a row in table

Hi, Please help me with this question. I have a table and I insert a row into it. How can i select the latest inserted row? Can the 'inserted table' keyword be used outside trigger? ( I mean can we use it in above scenario?? If yes how)   Thanks in advance Tiya

SSIS Ado >NET Destination Identity column inserts

Hi I am trying to build an SSIS package that imports the identity values from one SQL table to another.  I have an ADO .NET destination connection as the import is for SQL Azure although Im currently testing it in SQL 2008.  The connection has RetainSameConnection set to True.  Before the import I have a SQL task that runs SET IDENTITY_INSERT  activityDisplayGroup ON.  This executes but then I get an error message at the data flow task.  [Destination - activityDisplayGroup 1 [1]] Error: An exception has occurred during data insertion, the message returned from the provider is: Explicit value must be specified for identity column in table 'activityDisplayGroup' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column. In the profiler I see insert bulk "dbo"."activityDisplayGroup" ([name] VarChar(200) COLLATE SQL_Latin1_General_CP1_CI_AS) Is it at all possible to insert identies using the ADO .NET, if so what have I missed?   Thanks Dan      

Data Base Properties column Identity value will not accept value change


In the tutorial I am doing I have been requested to change a field in the colums properties of a table.  The ID column is currentl displaying NO and NO again for "is aIdentity.  I can Highlight the values but cannot change to yes.  either in the properties talbe or in the properties window on the right hand side of IDE interface.  How do I change that value?


transactional replication with identity column


Dear friends,

I have a transactional replication between A and B where A replicates its data into B . the schema of A and B are EXACTLY same. I have a table "t" in both databases which has an IDENTITY column with name "c" and it is set to be NOT FOR REPLICATION in both databases. Indeed I have "A.t.c " and "B.t.c " columns. Also I should mentioned that I never do any changes in B.t table. It should just get the last data from A.t (but because of some reasons I need to have a same schema).

I do the following steps :

1- Add a new row to A.t . (and see the added row in B.t after a few seconds either )

2- update this added row's c2 column (not the c which is  the identity and PK column)

3- an exception I receive in Replication Monitor which says I cannot update the B.t.c column because it is an Identity column!!!... (I checked the related MS's related update stored procedure)...

the question is why it wants to update the Identity column which is not needed indeed!?

how to insert value into a Identity column in temporary table using dataflow task



I have requirement.

I have a temporary table with and identity column,table will get populated with data on the execution of a dataflow task except the identity Column.now I need to insert value into identity column.

How can I acheive this.


Thanks, A2H

Problem with Identity Column


Hi.. my table contains an identity column - QID - (1,1)

the row count starts with 1 and increments by 1.. if i delete any row and then add a new row to the table the QID field is not incrementing properly..

eg:: say i have 50 rows in a table.. QID field (Identity Column) starts from 1 and goes till 50...

now if i delete the 25th row... and then add a new row to the table, the QID field value should be 50 for the new row.. but in my case it is 51...

i doubt that auto commit option must have enabled somewhere.. so it happens like this.. ny wayz i am new to SQL.. pls help me to fix this issue...

Transactional Replication Identity column difference in SQL 2008


Hello All,

I have a Pull Transactional Replication in SQL 2000.

To enable the copying of Identity columns, my subscriber Insert stored procs are modified to mark


In the end, I set it to Off.

The environment is stable for 2 years. Now we are upgrading to SQL 2008.

Do I still need to do the above step?

I have noticed that SQL 20088 is even updating Subscriber table for schema changes also.

I am not sure if MS took care of that issue in SQL 2008.

Please let me know your inputs.



Trigger for which column Updated


Hi all

I have Trigger  which can capture  date  If ID cloumn Updated

DECLARE @oldval varchar(100)  
DECLARE @Newval varchar(100)  
DECLARE @email_id varchar(100)  
SELECT @oldval=id FROM deleted  
SELECT @Newval=id FROM inserted  
SELECT @email_id=Email from inserted   
INSERT INTO updatehistory3  
SELECT USER_NAME(),APP_NAME(),HOST_NAME(),@oldval,@Newval,@email_id,getdate()  

 After couple of reaserch I found that  I want find out whcih column is updated.

DECLARE @oldval varchar(100)  
DECLARE @Newval varchar(100)  
DECLARE @email_id varchar(100)
DECLARE @updated_col varchar(100)
SET  @updated_col = ?
SELECT @oldval=id FROM deleted  
SELECT @Newval=id FROM inserted  
SELECT @email_id=Email from inserted   
INSERT INTO updatehistory3  
SELECT USER_NAME(),APP_NAME(),HOST_NAME(),@oldval,@Newval,@email_id,getdate(),@updated_col  

is it possible ,If it is please give idea how to impliment this ...


Table with Identity column issue


Hi all

I am using SQL CE 3.5, and I created a table with Identity column as PK (say I have columns ID, Name), but for somehow the table has very strange behavior. When I do insert statement

insert into table_a (Name) values ('Test Name')

I got message "ErrorMessage: The column cannot contain null values. [ Column name = ID,Table name = table_a]".

Then I ran

set identity_insert table_a off

And run the same insert statement above, I got "ErrorMessage: A duplicate value cannot be inserted into a unique index. [ Table name = table_a,Constraint name = PK_table_a ]"

Any idea what did I mess up with the table here? I checked carefully, the column Identitiy attribute is "True", Identity seed is 1, Identity increatment is 1.



Welcome to help me with my open source project at http://code.google.com/p/batch-image-watermark-processor/

Identity column in Management Studio


I am teaching a beginning class on SQL Server 2008 (not Express, and not R2), and we have begun using Management Studio.  One of the exercises included creating a small table with an identity column.  I then asked the students to insert some data just to see how the identity column works.

Strangely enough several of the students discovered that the identity column was increasing not by 1 but by some other integer.  I checked the Increment property and it was 1 in all cases.

What would cause this?  I have never see this before.

problem inserting using a stored procedure and identity column.


I recently moved a .net site from one machine to another, now for some reason one of the stored procedures is throwing an exception when attempting to insert! 

Exception Details: System.Data.SqlClient.SqlException: An explicit value for the identity column in table 'dbo.tbl_Events' can only be specified when a column list is used and IDENTITY_INSERT is ON

BTW, the column in question does have the identity set to Yes in management studio

I was using originally SQL 2005, now its on SQLexpress 2008

stored procedure:



PROCEDURE [dbo].[proc_EventsAddEdit]int,



How do I automate an UPDATED datetime column on insert/update

Automatically setting a CREATED column in a table is when a record is inserted is simple (define GETUPDATE() on it). Is there a simple way of doing this for an UPDATED datetime column whenever the record changes (insert/update)?

After Merge Replication Identity column was not generated Properly


HI All,


    I has been implemented Merge replication in sql server 2008, in that some tables Id locums are add 2000 and 20000 no add automatically incremented, why it was happens how to resolved this issues? if i want to get continues number what will it do, please help me in this ..... this is very urgent for me.




SQL Identity column / concurrent insert performance


How does sql handle concurrent insertion of rows into a table that has a integer identity column for a primary key?  Does it deadlock?  Does it just lock / block while inserts are performed one at a time?  My client app may be running twenty concurrent thread resulting in 20 concurrent inserts.  Is this a bad design from a performance perspective?  Is it better to use a different unique ID such as a GUID?




How to drop identity property of Column



Hi all,



Can any one help me or guide me how to drop identity property of a column with help of smo or TSQL ?



Any help is appreicated.

Tooltip for GridView Column Headers (ASP.Net)

I just wanted to add auto tooltips for my GridView headers. There are couple of ways but I don't want to use javascript and wanted to use simple 'title' attribute. It took couple of hours to get the working code supporting sortable columns. I thought it will be very useful for others. Here's the code and logic behind it.

Multiple Column Dropdownlist for the ASP.NET DataGrid

Based on my previous control "Multiple Column DropDownList for ASP.NET", I received many emails asking for the same control to be used in the DataGrid for web applications. Here we go.. This control can be used as the regular MS DropDownList in the DataGrid and also as a regular dropdownlist. It has all the properties, like DataTextField, DataValueField, DataSource, SelectedIndex etc. The download file contains the samples both in VB.NET and C#. In this sample, I have used the Northwind database of SQL Server.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend