This topic is locked
Insert Problem (IDENTITY_INSERT is set to OFF)
Posted 03 Jul 2002 17:51:41
I created a Simple insert form with one field but whenever I try to submit something to the db it gives me this error msg

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert explicit value for identity column in table 'tblcustdetails' when IDENTITY_INSERT is set to OFF.

Your help is greatly appreciated

IIS 5 with SQL 2000
Replied 03 Jul 2002 21:17:21
Dear Xlarge,
Does your table have an auto-incrementing or otherwise server-driven ID field? If so, let the server do the work & don't try to insert your own ID - you'll get the error in your message.

If you want a table with an INSERT updateable ID field (bad idea for most purposes), then use SQL Ent. Manager to change the Enable Identity Insert property in the Column Mappings and Data Transformations window...


Replied 04 Jul 2002 11:55:54
My table is a test table so it only has two columns, ID And Name, and the ID is server-driven ID (auto-increment by 1), my insert form only has one text field for the Name field.

Thanks for replying<img src=../images/dmxzone/forum/icon_smile_big.gif border=0 align=middle>
Replied 05 Jul 2002 03:21:17
In SQL Enterprise Manager
For your table
For the Identity column(id)
Make sure the Identity is set to "YES"
Identity seed "1"

I assume it is set to "NO"

Replied 05 Jul 2002 09:23:36
Identity is set to yes
Identity Seed set to 1
Identity increment set to 1
Data type is int
length 4, and not NULL

Thanks for your Reply <img src=../images/dmxzone/forum/icon_smile_big.gif border=0 align=middle>
Replied 06 Jul 2002 03:37:34
when you insert do you just insert for the name column? The ID column is inserted automatically. I assume from the error messages that it is trying to insert to the ID column from your webpage.

also make sure the permissions of the table is set to select,update,or insert

Edited by - biffysix on 06 Jul 2002 03:43:30
Replied 08 Jul 2002 11:23:53
Thanks guys, Finally got there (with your help)

Edited by - xlarge on 08 Jul 2002 11:24:33
Replied 04 Mar 2006 17:59:19
<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Thanks guys, Finally got there (with your help)

Edited by - xlarge on 08 Jul 2002 11:24:33
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Can I ask how? I've followed the above advice, but I'm still having problems.

Replied 25 Jan 2010 12:24:34
Hello Nathon Jones,

The idea is that once have the ID key set the way it is described here you don't need to bother to enter manually a value for that field.
If you enter data in your database through SQl Server Management you will get that error again and again.

Replied 25 Jan 2010 12:40:12
3 years, 10 months and 21 days for a reply...that's got to be a record! []

Hard to not sound sarcastic but, sincerely, thanks for sticking with it! I had worked it out, but it's the thought that counts.

Reply to this topic