Wrox Programmer Forums
ASP Pro Code Clinic As of Oct 5, 2005, this forum is now locked. No posts have been deleted. Please use "Classic ASP Professional" at: http://p2p.wrox.com/forum.asp?FORUM_ID=56 for discussions similar to the old ASP Pro Code Clinic or one of the other many remaining ASP and ASP.NET forums here.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP Pro Code Clinic section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old February 25th, 2005, 11:22 AM
Authorized User
Join Date: Jan 2005
Posts: 42
Thanks: 0
Thanked 0 Times in 0 Posts
Default IDENTITY_INSERT is set to OFF ??!!??!!

 I am trying to copy data from one table to another and I get this error...I have a field named [deaID] which is in both tables. The table I am inserting to has this as an Identity field. But I don't see a way to change the IDENTITY_INSERT setting.

Cannot insert explicit value for identity column in table 'dealerships' when IDENTITY_INSERT is set to OFF.

Also will this effect how my identity field is used...meaning will my identity field start to reuse numbers or will it keep the numbers going in order? I want it to keep the [deaID] of the data coming in the same as the table the data is coming from and I know the numbers won't conflict...ie trying to insert a duplicate [deaID]

I just don't want it to effect any new data inserted into this table from another asp page.

Old February 25th, 2005, 11:37 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post

Is this SQL Server? If so you just need to incorporate this statement in your stored procedure, or SQL, before you do the copy.
SET IDENTITY_INSERT [ database. [ owner. ] ] { table } ON

Don't forget to turn it back off again after!

SQL Server will automatically update its generated IDs to ensure you don't get duplicates after the insert.

Old February 25th, 2005, 12:03 PM
Authorized User
Join Date: Jan 2005
Posts: 42
Thanks: 0
Thanked 0 Times in 0 Posts

Here is my SQL Statement on my asp page can you help me set it up correctly...Yes it is SQL Server by the way.

sql3 = "INSERT INTO dealerships
(deaID,latitude,longitude,mediaURL,ccemail,newused ,dealername,dealphone,dealaddress,dealcity,dealst, dealzipcode,dealfax,dealwebsite,dateentered,dateac tive,dactive,makerID,GraphicsLogo,GraphicsBanner,d ivisionID)
SELECT deaID,latitude,longitude,mediaURL,ccemail,newused, dealername,dealphone,dealaddress,dealcity,dealst,d ealzipcode,dealfax,dealwebsite,dateentered,dateact ive,dactive,makerID,GraphicsLogo,GraphicsBanner,di visionID

FROM inactivedealerships WHERE dactive=1"

Similar Threads
Thread Thread Starter Forum Replies Last Post
XPath: set operation with a disjoint node set rich_unger XSLT 7 May 6th, 2008 09:24 AM
Identity_insert problem Shuchik Classic ASP Basics 1 August 30th, 2007 05:51 AM
IDENTITY_INSERT happygv SQL Server 2000 4 November 27th, 2006 02:10 PM
Linked server with Identity_insert on b_amit76 SQL Server 2000 2 February 22nd, 2005 12:35 PM
How to set DOM character set sonicDace XML 0 May 27th, 2004 08:52 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.