Cannot update identity column 'LocationID'

Question

I am trying to update an ID value. So if the ID is 1 the new value is 2 it should replace the old ID with the new.

Back end:

     public static void UpdateLocation( int locationID, SqlConnection connection,                SqlTransaction transaction )
    {
        StringBuilder sqlString = new StringBuilder();
        SqlCommand command;

        sqlString.Append( "UPDATE [Location] SET " );
        sqlString.Append( "description = @description " );
        sqlString.Append( "WHERE locationID = @locationID " );
        command = new SqlCommand( sqlString.ToString(), connection );
        if( ( transaction != null ) ) command.Transaction = transaction;

        command.Parameters.Add( "@locationID", SqlDbType.Int ).Value = locationID;
       command.Parameters.Add( "@description", SqlDbType.VarChar ).Value = description;
        int rowsAffected = command.ExecuteNonQuery();

        if( !( rowsAffected == 1 ) )
        {
            throw new Exception( "An error has occurred while updating UpdateMedicationDispenseStatus." );
        }
    }

Front End:

      private void btnFromLocation_Click( object sender, System.Windows.RoutedEventArgs e )
     {
         if( !( ( Locations )cmbLocationDescriptionList.SelectedItem == ( Locations )cmbDescriptionListTo.SelectedItem ) )
         {


             for( int i = 0; i < lstMedicationForCurrentLocation.SelectedItems.Count; i++ )
    {
                 location = (Locations)cmbDescriptionListTo.SelectedItem;
        LocationData.UpdateLocation( location.LocationID );

EDIT I have added a parameter to the SQL Query but still doesnt Update

You can't (well, shouldn't be able to) change the value of a primary key. If you need a changeable identifier, you should add a new one to the table. May I ask why you're trying to change it in the first place? — Joachim Isaksson, Jul 29 '13 at 11:32
http://stackoverflow.com/questions/751522/how-to-change-identity-column-values-programmatically — PGallagher, Jul 29 '13 at 11:34
If it's marked as an identity column in the database, why would you be updating it at all? If this column has some other use than a unique identifier as sorts, remove the identity spec. If you just wanted to create new rows with a specific ID, then you can turn `IDENTITY_INSERT` for this table on and off around your query. — Bridge, Jul 29 '13 at 11:34
Are you sure `rowsAffected == 1` after the Update? I mean your `WHERE` clause may match more than 1 because of your fault in data table design. — King King, Jul 29 '13 at 11:35
Your query sets LocationID to the same value it already has, so why not not just set `Description` and leave LocationID unchanged? — Joachim Isaksson, Jul 29 '13 at 11:43
@JoachimIsaksson Appologies forgot the delete the LocationID. I have now removed it. Stil not updating the database — Mark, Jul 29 '13 at 11:46
@Mark Hm, but you should not get the same error at least...? — Joachim Isaksson, Jul 29 '13 at 11:46
In general relational theory (if I can be forgiven for ragging on MS for a moment) primary keys aren't read-only, they're just unique and non-nullable. You certainly should be able to change them. Referencing foreign keys should have an "on update cascade" option. Auto-incrementing should be done by setting the default to the result of an expression that may involve a function call, .eg the next value of a sequence. SQL Server takes it's que from MS Access where these "identity" fields are concerned. It is neither standard nor robust, and it's the cause of a whole lot of pain and suffering. — Shavais, Jan 14 '14 at 19:50

score 6 · Answer 1 · answered Jul 29 '13 at 12:32

The below workaround may not be preferable but At least from knowledge perspective this may be helpful to you.

Some workaround are there if you want to set Identity column (LocationID) value yourself rather then let do the Sql server.

set identity_insert Table_Name ON

Then delete your row and reinsert it with different identity. Please note that you still cannot update the same row to a new Identity. This will allow insert statements to specify Identity column values.

Once you have done the insert with new identity value , turn identity_insert off

set identity_insert Table_Name OFF

Since you are doing updates programmatically, you can have the Values of all Columns, and probably insert a new row with same values for all columns except the Identity Value which will be of your choice.

score 0 · Answer 2 · answered Jul 29 '13 at 11:31

0

LocationID is an automatically generated id then. You should change in database. You should set identity to no.

answered Jul 29 '13 at 11:31

Ron Deijkers

2,791
2
22
28

Look at the EDIT. Thanks – Mark Jul 29 '13 at 11:39

score 0 · Answer 3 · answered Jul 29 '13 at 12:33

You have to check if Identity_insert is enabled in your SQL db , else do it as below before your update statement.

set identity_insert YourTable ON . Then delete your row and reinsert it with different identity.

Once you have done the insert don't forget to turn identity_insert off

set identity_insert YourTable OFF.

Cannot update identity column 'LocationID'

3 Answers3