John (Yiannis) Toutountzoglou
asked on
How to solve the "Cannot Update Identity Column" error
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)
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)
ASKER
yes i did that in sql server
I modify the table and i did set seed and increament to 1
I modify the table and i did set seed and increament to 1
Hi,
Yes, but have you updated the sql queries for update / insert to reflect this change?
/Carl.
Yes, but have you updated the sql queries for update / insert to reflect this change?
/Carl.
ASKER
you mean to configure the dataset again?
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.
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.
ASKER
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.
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.
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.
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.
ASKER
so can i use for the [A/A] field this?
Dim lastrow As Int32
lastrow = PersonnelDataSet.Tables(0) .Rows.Coun t - 1
A_ATextBox.Text = CStr(CDbl(CDbl(PersonnelDa taSet.Tabl es(0).Rows (lastrow). Item(1).To String) + 1))
in my form load event? this one just add +1 increament in the last A/A
is this logical? would this work?
Dim lastrow As Int32
lastrow = PersonnelDataSet.Tables(0)
A_ATextBox.Text = CStr(CDbl(CDbl(PersonnelDa
in my form load event? this one just add +1 increament in the last A/A
is this logical? would this work?
ASKER
my dought is about duplicates values
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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....
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()
ASKER
Plz check my last comment and correct me if for any mistakes ...
note that ...all my code is in my form load event...
note that ...all my code is in my form load event...
Hi,
It seems to check out.
/Carl.
It seems to check out.
/Carl.
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.