Solved

Copying a record

Posted on 2007-11-28
14
175 Views
Last Modified: 2013-12-24
I am running the following query to make a copy of the record. All seems to work and the record is copied in the database. I get the following error when I open the copy of the record,
'Cannot insert explicit value for identity column in table 'Products' when IDENTITY_INSERT is set to OFF. "

If I set it to ON it states the values must be explicit.

---------Query------------------------------------------------------------------
<cfquery dataSource="#request.dataSource#" userName="#request.userName#" password="#request.password#">
      INSERT INTO Prods(stock,reorder,SKU,ENum,CatID,ProductName,Description,Weight,Quantity,PurchasePrice,RetailPrice,WarehouseID,Active,Taxable,Shippable,StoreID,Book,Candy,Food,Media)
      VALUES ('','','#SKU#','00000','#CatID#','Copy of #ProductName#','#Description#','#Weight#','#Quantity#',#PurchasePrice#,#RetailPrice#,'#WarehouseID#','0','0','#Shippable#','#StoreID#','0','0','0','0')
</cfquery>
--------------------------------------------------------------------------------------------------------------
0
Comment
Question by:usky1
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 20368806
What do you mean "open" the copy of the record?
0
 

Author Comment

by:usky1
ID: 20368921
I have a form where I list all the records by row. If I select the record that was just created to view it I get the error.
0
 
LVL 19

Expert Comment

by:erikTsomik
ID: 20368932
Make sure you have identity column enabled and increment +1
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 52

Expert Comment

by:_agx_
ID: 20368935
The error 'Cannot insert explicit value for identity column in table 'Products' ' suggests the code is doing more than a SELECT.  

Can you post that code?
0
 

Author Comment

by:usky1
ID: 20369033
The identity column is one of the things that is confusing me. The values look fine in the database and the identity column did increment by +1.
The code on the form page is 1100 line long please it makes calls to several other pages. Is there something specific I can pull from it to help?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 20369085
Maybe the complete CF error message you're talking about. Possibly the query that causes it assuming its not the one you posted originally.

0
 

Author Comment

by:usky1
ID: 20369125
I have something I want to try and I'll let you know if it works.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 20369134
Okay
0
 
LVL 9

Expert Comment

by:digicidal
ID: 20369846
agx is correct... if you are getting that error you are most definitely NOT simply running a SELECT query.  Is it possible that you do both the INSERT and the SELECT in the same CFQUERY?  Or are you using a StoredProc to do the SELECT?  If so then check that because it's definitely trying to do an insert into the table or the error would not occur.  Also make sure in your INSERT statement that you have not inadvertently set more than one field as the Identity.  I see several relational ID fields that I assume point to records in other tables... check to make sure that A) you have not accidentally set one of them to an Identity as well as the PKID for the record, and B) that you have not defined any cascading rules in the relationships assigned to the table.  It's possible if you have that it is requiring that the relationship is also updated with the inserted information because of a stated dependency.

This seems like it is more likely a SQL configuration issue rather than a CFML issue unless you are doing the INSERT and SELECT in the same thread on the CF server.
0
 
LVL 9

Assisted Solution

by:digicidal
digicidal earned 100 total points
ID: 20369869
OOPS... I just reread my post... this:

Also make sure in your INSERT statement that you have not inadvertently set more than one field as the Identity.

SHOULD READ:
Also make sure in your TABLE PROPERITES page that you have not inadvertently set more than one field as the Identity.

Sorry for the confusion.
0
 

Author Comment

by:usky1
ID: 20373847
This is the error when when IDENTITY_INSERT is set to ON,
"Explicit value must be specified for identity column in table 'Prods' when IDENTITY_INSERT is set to ON."
The ProdID is the key field. If I look in the database the entry is created and properly incremented. This is going way above my knowledge as I am still a newbie with ColdFusion and SQL. Please let me know of any other suggestions.
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 400 total points
ID: 20377772
Did you try digicidal's suggestions above?  Also

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

One thing I noticed is the error message refers to a table named "Products" but the query you posted inserts into a table named "Prods".  Is it possible you posted the wrong query?

Its difficult to diagnose further without more information.  Could you post the full error message (line numbers and all) and the full cfquery or stored procedure referenced in the error message?

0
 

Author Comment

by:usky1
ID: 20382417
I apologize for some of my confusion. I am doing updates to a large site that has no documentation. I found a subquery that was linked back and needed to have the new prodid also inserted. As soon as I took care of that all worked. I appreciate your time in helping me.
0
 

Author Closing Comment

by:usky1
ID: 31411526
agx and digicidal helped me troubleshoot the problem and point me in the right direction to find a solution.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Website Test Question 6 128
.htacess file 301 redirects that are strange 4 64
AWS New EC2 Instance and EBS Storage 2 85
WebSite Direction 1 54
This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

820 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