[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

whow to capture SQL errors in Coldfusion

Posted on 2014-11-06
2
Medium Priority
?
117 Views
Last Modified: 2014-11-29
I have a bunch of queries which insert data into my SQL server and sometimes I get a duplicate record which
throws an error like this

Error Executing Database Query.[Macromedia][SQLServer JDBC Driver][SQLServer]Violation of PRIMARY KEY constraint 'PK_tbl_CNR_D'. Cannot insert duplicate key in object 'dbo.tbl_CNR_D'. The duplicate key value is (C2491035056, 01, 1428310095, ). The specific sequence of files included or processed is: C:\inetpub\wwwroot\parse.cfm, line: 452

how can I capture this in my code and act accordingly ?

I want to avoid to have to fire of a query to see if recordcount GT 0 to avoid this
0
Comment
Question by:AlexPonnath
2 Comments
 
LVL 36

Accepted Solution

by:
SidFishes earned 1500 total points
ID: 40433265
Well you can parse the error in cfcatch.detail, evaluate if it's the duplicate error and then handle it however you want but that really is no "better" than handling it properly in the first place, by checking to see if the id already exists in a query.

There's going to be 2 hits to the db in either case

Call with error > call with corrected id
Call with id check > call to insert

To me it doesn't make sense (and it's a lot more complex) to try to handle the error than avoiding it in the first place.
0
 
LVL 29

Expert Comment

by:Pravin Asar
ID: 40434199
With insert/update query you can add result attribute to check number of rescords being updated.
To avoid duplicates, you may check the existence of record matching the pk or fk.

If record exists, may be you want to run update query.

for example

<cfquery datasource="#request.dsn#" name="saveChanges" result="updateResult">
UPDATE table_name
SET
column1 = '#value1#',
column2 = '#value2#'
WHERE column3 = 1
</cfquery>

<cfset recordsChanged = updateResult.recordCount>
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Question has a verified solution.

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

PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
There may be issues when you are trying to access Outlook or send & receive emails or due to Outlook crash which leads to corrupt or damaged PST file. To eliminate the corruption from your PST file, you need to repair the corrupt Outlook PST file. U…
Suggested Courses
Course of the Month9 days, 5 hours left to enroll

590 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