Solved

How to solve the "Cannot Update Identity Column" error

Posted on 2009-03-29
13
365 Views
Last Modified: 2012-05-06
I am trying to find a solution to that byt nothing ....
I change a field in my Personnel table To Identity Specification ..
While I am trying to update my table adapter i got this error as an exception in my try Catch ...
Plz someone guide me ...
this seems to me that the sql server cannot generate the next [A/A] (A/A is the field i changed)
0
Comment
  • 7
  • 6
13 Comments
 
LVL 18

Expert Comment

by:carlnorrbom
ID: 24016805
Hi,

If you have the column set to identity specification you should also set AutoIncrement to true (if you don't have any perticular logic to handle the automatic incrementation already) and seed to whatever suits your application (usually 1). Also, if you in existing logic have insert / update queries putting / updating values of that particular column you need to rewrite them to exclude the identity specification column.

/Carl.
0
 
LVL 18

Author Comment

by:John (Yiannis) Toutountzoglou
ID: 24016828
yes i did that in sql server
I modify the table and i did set seed and increament to 1
0
 
LVL 18

Expert Comment

by:carlnorrbom
ID: 24016843
Hi,

Yes, but have you updated the sql queries for update / insert to reflect this change?

/Carl.
0
 
LVL 18

Author Comment

by:John (Yiannis) Toutountzoglou
ID: 24016882
you mean to configure the dataset again?
0
 
LVL 18

Expert Comment

by:carlnorrbom
ID: 24016893
Hi,

Otherwise, try using SET IDENTITY_INSERT <tablename> ON , i.e. (SQL Code example for copying between two tables):

ALTER TABLE <tablename> DISABLE TRIGGER MyTrigger

SET IDENTITY_INSERT <tablename> ON

INSERT INTO <tablename> (IDCol, Col1, Col2, Col3, Col4)
  SELECT IDCol, Col1, Col2, Col3, Col4 FROM <tablename2>
  WHERE IDCol=<value>

SET IDENTITY_INSERT <tablename> OFF

ALTER TABLE <tablename> ENABLE TRIGGER MyTrigger

/Carl.
0
 
LVL 18

Author Comment

by:John (Yiannis) Toutountzoglou
ID: 24016959
before that ....let me ask you something
my [A/A] filed is not my primary KEy
primary key is Key_Personnel which is identity specificationi
i saw that i am not allowed to set 2 fileds with identity.

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 18

Expert Comment

by:carlnorrbom
ID: 24017001
Hi,

Ok, there is really no point in having two fields as identity. The reason for an identity field is to ensure unique identification of each commited row. My recommendation would be to have one field acting as both primary key / identity specification with autoincrement.

/Carl.
0
 
LVL 18

Author Comment

by:John (Yiannis) Toutountzoglou
ID: 24017298
so can i use for the [A/A] field this?
Dim lastrow As Int32
       lastrow = PersonnelDataSet.Tables(0).Rows.Count - 1
       A_ATextBox.Text = CStr(CDbl(CDbl(PersonnelDataSet.Tables(0).Rows(lastrow).Item(1).ToString) + 1))
in my form load event? this one just add +1 increament in the last A/A
is this logical? would this work?

0
 
LVL 18

Author Comment

by:John (Yiannis) Toutountzoglou
ID: 24017301
my dought is about duplicates values
0
 
LVL 18

Accepted Solution

by:
carlnorrbom earned 500 total points
ID: 24021126
Hi,

You can do a select which will get the highest value from the colum in question before you perform an insert in order to make sure you avoid duplicates, i.e.

strMax = "SELECT MAX(A/A) AS MaxCount FROM YourTableName"
Dim maxCount As New SqlCommand(strMax, YourSqlConnection)
Dim maxAA As Integer = maxCount.ExecuteScalar()
Dim newAA As Integer = maxAA + 1

then in Your insert query -> insert the value for newAA..

/Carl.
0
 
LVL 18

Author Comment

by:John (Yiannis) Toutountzoglou
ID: 24022731
Sorry for my delay.....i was Flying To brussels and back to greece.
It works ....
here is what i've done according to your suggestion...
i increase the point value...but check my code for other suggestion..thank you very much....


 Me.PersonnelBindingSource.AddNew()

       

        Dim conn As New SqlConnection(My.Settings.MyconnectionString)

        If conn.State = ConnectionState.Open Then conn.Close()

        conn.Open()
 

        Dim strMax As String = "SELECT MAX([A/A]) AS MaxCount FROM Personnel"

        Dim maxCount As New SqlCommand(strMax, conn)

        Dim maxAA As Integer = CInt(maxCount.ExecuteScalar())

        Dim newAA As Integer = maxAA + 1

        

A_ATextBox.Text = CStr(newAA)

        ..

..

..
 

        conn.close()

Open in new window

0
 
LVL 18

Author Closing Comment

by:John (Yiannis) Toutountzoglou
ID: 31564202
Plz check my last comment and correct me if for any mistakes ...
note that ...all my code is in my form load event...
0
 
LVL 18

Expert Comment

by:carlnorrbom
ID: 24031881
Hi,

It seems to check out.

/Carl.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now