?
Solved

IDENTITY_INSERT is set to OFF.

Posted on 2007-12-01
14
Medium Priority
?
4,561 Views
Last Modified: 2013-12-16
Hallo experts.
I want your help to fix my problem.
When i try to insert in a db table (t_artikel) i have the following error:
 Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Cannot insert explicit value for identity column in table 't_artikel' when IDENTITY_INSERT is set to OFF.
I use cf8 and ms sql server.
please help.
0
Comment
Question by:Panos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
  • +1
14 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 20387630
That means you're trying to insert a value into an identity column.  Normally you don't do this.   Identity columns generate the numbers automatically.  ie so you don't have to insert a value.  
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 20387637
> When i try to insert in a db table (t_artikel)

Check your insert statement.  If you've accidentally included the identity column in the insert list like this

<cfquery ...>
INSERT INTO YourTable (TheNameOfYourIdentityColumn, Name, OtherColumns...)
VALUES (1, 'Some Name', 'Other Values....', ...)
</cfquery>

Remove it from the insert and values list

<cfquery ...>
INSERT INTO YourTable (Name, OtherColumns...)
VALUES ( 'Some Name', 'Other Values....', ...)
</cfquery ...>
0
 
LVL 25

Assisted Solution

by:imitchie
imitchie earned 500 total points
ID: 20387639
the short answer is that instead of
INSERT INTO t_artikel (ID, a,b,c,d...) values (1,2,3,4,5...)
just drop the ID column, so it becomes
INSERT INTO t_artikel (a,b,c,d...) values (2,3,4,5...)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 52

Expert Comment

by:_agx_
ID: 20387642
Was that not just what I said? :)
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20387674
I find examples a lot clearer, same for SQL, C#, C++, PHP or just anything really.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 20387675
Umm.. yes, that's why I included one ;-)
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20387680
ahh.. sorry. i saw your original one, then i saw the essay from the other comment. i hadn't seen the other one before i piped in.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 20387683
> then i saw the essay from the other comment
(rofl)

No problem :)
0
 
LVL 15

Expert Comment

by:spprivate
ID: 20387717
The idea behind giving extra information is that you understand the concept in full rather than solving the problem one time.So lets concentrate on giving solutions rather than commenting other's answers.
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20387720
the corollary to that argument is that if everyone wanted information rather than a solution, we don't need to be here! BOL+Google covered that ground already
0
 
LVL 52

Expert Comment

by:_agx_
ID: 20387722
spprivate,

Posting a few sample lines is okay.  But if its a long section in the documentation, better to post a link or point the asker to the pertinent section in the Books On Line.  Too much information can be overwhelming and is often completely ignored.  
0
 
LVL 15

Expert Comment

by:spprivate
ID: 20388183
This shows that you people are not readin the post properly,
The first few lines in my comment was the solution.The rest was the reference from SQL Server books online.
Pity that Gurus and Wizards not able to understand this.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20388697
Hi again,
>The rest was the reference from SQL Server books online.
yes, but plain copy/paste without the reference, so that is 2 problems:
* no reference: although it is obvious that someone else had written that text, and even obvious who, you should have AT LEAST given credits and/or a link.
* copy/pasting entire articles is prohibited by the authors, or do you have the written authorization to copy their articles? if you had, you should have indicated that you have...

please, understand that we do not try to lower you knowledge, but MS (among others) are threathening EE (and others) to control what is being copied from their site/documents to the rest of the internet. they "allow" that extracts can be copied, but not entire pages, and as that was what you did, you violated several rules.

the first lines you posted where fine, and I invite you do continue posting along those lines in the other questions you can answer. but when it comes to copy a full document from an external source, just explain and post the reference, that will be better for all parties.

thanks for your understanding

angelIII, zone advisor
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

801 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