|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Laplink Gold 2008 - $15 Off with Coupon Code CJM9NE
|
|
#1
April 16th, 2004, 11:35 AM
|
||||
|
||||
|
CFOUTPUT based upon SQL IF statement?
I have a query that checks to see if a primary key exists in the table before inserting a row entered by a user. The statement reads:
Code:
IF #form.itemid# NOT IN(SELECT ItemID FROM Merchandise) INSERT INTO Merchandise (...) VALUES (...) So what I want to do is print confirmation. If the record was inserted, print something, and if the primary key already existed in the database, print something like "could not insert, pk already exists..." I assume this will require a getRowCount(), but what do I count? myQuery.getRowCount() always returns 0 regardless of whether it was inserted or not. I know I'll need to use CFINSERT too, but how can I check to make sure the primary key being inserted doesn't already exist in the table?
__________________
Reinventing the wheel again Last edited by mateoc15 : April 16th, 2004 at 12:28 PM.
|
|
#2
April 16th, 2004, 01:08 PM
|
||||
|
||||
|
OK, I've narrowed the question.
Code:
<CFQUERY datasource="#DataSource#" name="merchandise"> SELECT DISTINCT ItemID FROM Merchandise </CFQUERY> <CFIF (CHECK form.itemid against all mechandise ItemID to make sure it's not already in there)> <CFINSERT ...> <CFOUTPUT>Confirmation Message</CFOUTPUT> </CFIF> So how do I code the bolded, underlined part? Can something like ListQualify be used to get every value from a column in a select statement? Last edited by mateoc15 : April 16th, 2004 at 01:12 PM.
|
|
#3
April 16th, 2004, 02:18 PM
|
|||
|
|||
|
Woah, why are you running a query and pulling out a list of all the id's, and then checking the form value against that list? That is total overkill and a very bad idea, and this solution will blow up in your face if the database has ten million records in it. Why not just do:
Code:
<CFQUERY name="checkForNewRecord" datasource="#DataSource#" name="merchandise"> SELECT ItemID FROM Merchandise WHERE ItemID = #form.itemid# </CFQUERY> <CFIF not checkForNewRecord.recordCount> <CFINSERT ...> <CFOUTPUT>Confirmation Message</CFOUTPUT> </CFIF> As an aside, using <cfinsert> is also a bad, bad idea, that tag sucks. Just write your own SQL insert statement using <cfquery>. Last edited by kiteless : April 16th, 2004 at 02:20 PM.
|
|
| Viewing: Dev Shed Forums > Programming Languages - More > ColdFusion Development > CFOUTPUT based upon SQL IF statement? |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
