This topic is locked

Insert Problem (IDENTITY_INSERT is set to OFF)

Posted 03 Jul 2002 17:51:41
has voted
03 Jul 2002 17:51:41 Saeed Abdi posted:
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
03 Jul 2002 21:17:21 Brent Colflesh replied:
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
04 Jul 2002 11:55:54 Saeed Abdi replied:
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
05 Jul 2002 03:21:17 b w replied:
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
05 Jul 2002 09:23:36 Saeed Abdi replied:
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
06 Jul 2002 03:37:34 b w replied:
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
08 Jul 2002 11:23:53 Saeed Abdi replied:
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
04 Mar 2006 17:59:19 Nathon Jones replied:
<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
25 Jan 2010 12:24:34 Miroslav Zografski replied:
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
25 Jan 2010 12:40:12 Nathon Jones replied:
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