This is a forum where members new and old can come to ask questions and get info and opinions. It is not a place to advertise your business or have other forms of advertising, whether it be in your posts or signature.

All links in the forum will not be indexed by Search Engines and any unapproved forms of advertising or spam will be dealt with accordingly, deleted, and that member's account banned.

Forums

Overview » Adobe Creative Suite » Insert Problem (IDENTITY_INSERT is set to OFF)

Insert Problem (IDENTITY_INSERT is set to OFF)
Saeed Abdi Member Since: 28 Apr 2002 Posts: 36	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 Info ~~~~~~~~~~~~~~~~~~~~~~~~~~~ IIS 5 with SQL 2000 ASP & VBS 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 Info ~~~~~~~~~~~~~~~~~~~~~~~~~~~ IIS 5 with SQL 2000 ASP & VBS

Insert Problem (IDENTITY_INSERT is set to OFF)

Saeed Abdi
Member

Since: 28 Apr 2002
Posts: 36

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

Info
~~~~~~~~~~~~~~~~~~~~~~~~~~~
IIS 5 with SQL 2000
ASP & VBS

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

Info
~~~~~~~~~~~~~~~~~~~~~~~~~~~
IIS 5 with SQL 2000
ASP & VBS

Brent Colflesh Locked Total freaking Member Since: 18 May 2001 Posts: 280	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... Regards, Brent

Saeed Abdi Member Since: 28 Apr 2002 Posts: 36	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>

b w Freaking Member Since: 15 Jan 2002 Posts: 69	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"

Saeed Abdi Member Since: 28 Apr 2002 Posts: 36	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>

b w Freaking Member Since: 15 Jan 2002 Posts: 69	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

Saeed Abdi Member Since: 28 Apr 2002 Posts: 36	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

Nathon Jones Member Since: 04 Mar 2006 Posts: 8	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. Thanks. Nath.

Miroslav Zografski Official Representative Since: 04 Apr 2008 Posts: 3,044	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. Regards,

Nathon Jones Member Since: 04 Mar 2006 Posts: 8	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. Regards Nathon.