error inserting

Heaven Valenzona

my primary key service_code is a autonumber..

when I insert data in my page I get this error:

Error inserting

LabelCannot insert explicit value for identity column in table 'SERVICES' when IDENTITY_INSERT is set to OFF.

Why?

--MS sql

--c#

--asp.net

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

PJ Martins16y ago
Accepted answer
Again you have two options, if you want an autonumber identity, set the primary key back to autonumber and make sure that when you insert your records, you DO NOT set values for service_code so that table may handle it automatically. Second option, you keep it as you have it now, retrieve the maximum identity from your table (select max(service_code) from table), increment it by one, and set it as your new record's identity. I stronly suggest you follow the first option as the second options requires a lot of extra work, especially when inserting multiple records, and you run into the risk of two inserts with the same identity happening at the same time. Using the first option will avoid all this.
Heaven Valenzona16y ago
Ah, ok.. I have already solve my problem :) thanks anyway sir..
Heaven Valenzona16y ago
Sir, I changed the setting of my primary key. my primary key is now not a autonumber.. and yes it inserts record now. But I need my primary key to be auto number.. how can I insert data with a autonumber identity? :( please help. thank you
PJ Martins16y ago
Are you setting the service_code value before your insert? If you are, you will need to update the table as to where you can insert identities, however this could be dangerous when using integers. Setting the service_code to null will allow the table to assign an identity to it.