#1
  1. Business Analyst
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2004
    Location
    The 'Ville
    Posts
    1,087
    Rep Power
    22

    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?
    Last edited by mateoc15; April 16th, 2004 at 12:28 PM.
    Discontent is the first necessity of progress. - Edison
  2. #2
  3. Business Analyst
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2004
    Location
    The 'Ville
    Posts
    1,087
    Rep Power
    22
    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.
    Discontent is the first necessity of progress. - Edison
  4. #3
  5. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,243
    Rep Power
    967
    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.

IMN logo majestic logo threadwatch logo seochat tools logo