|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Changing an ordinary column to an identiry columnThe primary key of a table was an ordinary int column, but I wanted to change
it to an identity column (which would automatically generate its values). I deleted the old column and created new one with same name, this time setting Identity to Yes. I had to change the definition of this column in the dataset too. Now I don't need a control on the form for this column. But when trying to do an Update (adding a new record) I get the exception: "Cannot insert explicit value for identity column in table <...> when IDENTITY_INSERT is set to OFF" What should I do in order to automatically set IDENTITY_INSERT to ON before INSERT? Or I did something wrong? Thanks in advance. Hi,
You should check your INSERT sql statement . what is the sql statement that you use to insert your new row ? You get this kind of error if you try to explicitly set the value of your identity column in the INSERT. Show quote > The primary key of a table was an ordinary int column, but I wanted to change > it to an identity column (which would automatically generate its values). I > deleted the old column and created new one with same name, this time setting > Identity to Yes. I had to change the definition of this column in the dataset > too. > > Now I don't need a control on the form for this column. > But when trying to do an Update (adding a new record) I get the exception: > "Cannot insert explicit value for identity column in table <...> when > IDENTITY_INSERT is set to OFF" > > What should I do in order to automatically set IDENTITY_INSERT to ON before > INSERT? Or I did something wrong? > > Thanks in advance. > On Thu, 7 Dec 2006 08:27:00 -0800, John Stivenson wrote:
Show quote > The primary key of a table was an ordinary int column, but I wanted to change If the column is an identity column, you shouldn't specify it in the insert> it to an identity column (which would automatically generate its values). I > deleted the old column and created new one with same name, this time setting > Identity to Yes. I had to change the definition of this column in the dataset > too. > > Now I don't need a control on the form for this column. > But when trying to do an Update (adding a new record) I get the exception: > "Cannot insert explicit value for identity column in table <...> when > IDENTITY_INSERT is set to OFF" > > What should I do in order to automatically set IDENTITY_INSERT to ON before > INSERT? Or I did something wrong? > > Thanks in advance. statement Go over to http://www.betav.com - and look for the Managing an @@IDentity
crisis. The easiest thing to do is to just set the values in the datacolumn to autoincrement and use -1 as the seed. This will allow you to pass in values and Sql Server will assign values for you. Show quote "John Stivenson" <JohnStiven***@discussions.microsoft.com> wrote in message news:41304367-5E58-4B32-8590-6295D5D70FE4@microsoft.com... > The primary key of a table was an ordinary int column, but I wanted to > change > it to an identity column (which would automatically generate its values). > I > deleted the old column and created new one with same name, this time > setting > Identity to Yes. I had to change the definition of this column in the > dataset > too. > > Now I don't need a control on the form for this column. > But when trying to do an Update (adding a new record) I get the exception: > "Cannot insert explicit value for identity column in table <...> when > IDENTITY_INSERT is set to OFF" > > What should I do in order to automatically set IDENTITY_INSERT to ON > before > INSERT? Or I did something wrong? > > Thanks in advance. > You do not say if you are speaking of a table in the database or speaking of
a datatable, so I will assume you mean the actual table in the database. The simple answer is to not try to Update (or Insert to) your Identity column. Remove those column references from your command objects and stored procedures and you should be fine. Show quote "John Stivenson" <JohnStiven***@discussions.microsoft.com> wrote in message news:41304367-5E58-4B32-8590-6295D5D70FE4@microsoft.com... > The primary key of a table was an ordinary int column, but I wanted to > change > it to an identity column (which would automatically generate its values). > I > deleted the old column and created new one with same name, this time > setting > Identity to Yes. I had to change the definition of this column in the > dataset > too. > > Now I don't need a control on the form for this column. > But when trying to do an Update (adding a new record) I get the exception: > "Cannot insert explicit value for identity column in table <...> when > IDENTITY_INSERT is set to OFF" > > What should I do in order to automatically set IDENTITY_INSERT to ON > before > INSERT? Or I did something wrong? > > Thanks in advance. >  
Other interesting topics
|
|||||||||||||||||||||||
