0

I am working with Coldfusion. I am new to the language.

My requirement is to Insert data into database, only if a record not present. If it does exist in database, I want to throw error message.

Please help me how to do this .

Fergus
  • 2,821
  • 2
  • 27
  • 41
Soujanya
  • 277
  • 4
  • 17
  • 3
    Downvoting and voting to close as this question does not indicate any effort was made to solve the problem, and is very similar to [this closed question](http://stackoverflow.com/questions/35362652/how-to-check-entered-emaild-exist-or-not-in-database-coldfusion). – Leigh Feb 15 '16 at 18:24

1 Answers1

1
<cfquery name="qGetRecord" datasource="yourSorce"> 
    <!--- Your SELECT Query for finding the data--->
</cfquery> 

<cfif qGetRecord.recordCount EQ 0>
    <!--- Your INSERT Query --->
<cfelse>
    <!--- Show eroor message --->
</cfif>

The logic is that , first we will try to fetch the required data. Recordcount function is used to find if there is any matching records.
If matching record is not there then we can insert else show error message.

Updated answer with cftransaction as per the suggestion.

<cftransaction>
    <cftry>
        <cfquery name="qGetRecord" datasource="yourSorce"> 
            <!--- Your SELECT Query for finding the data--->
        </cfquery> 

        <cfif qGetRecord.recordCount EQ 0>
            <!--- Your INSERT Query --->
        <cfelse>
            <!--- Show eroor message --->
        </cfif>
        <cfcatch>
            <cftransaction action="rollback" />
        </cfcatch>
    </cftry>
</cftransaction>
Community
  • 1
  • 1
Rino Raj
  • 6,264
  • 2
  • 27
  • 42
  • Thanks for the reply – Soujanya Feb 15 '16 at 16:07
  • 1
    Better wrap this in a transaction – Tim Jasko Feb 15 '16 at 16:46
  • @TimJasko I have updated my answer as per your suggestion – Rino Raj Feb 15 '16 at 16:55
  • CF is multi-threaded, so this could still allow duplicates on a busy site. – Leigh Feb 15 '16 at 18:22
  • @Leigh is there any solution to avoid duplicate on a busy site? – Rino Raj Feb 16 '16 at 04:19
  • @Leigh can we use cflock to prevent that scenario? – Rino Raj Feb 16 '16 at 04:35
  • @RinoRaj - Personally, I have never been a fan of using cflock for controlling database objects. IMO that is a database's job. For general cases, see the details on atomic locking in this thread [ATOMIC UPSERT in SQL Server 2005](http://stackoverflow.com/questions/2522379/atomic-upsert-in-sql-server-2005). Ignore the UPDATE part, it is the INSERT that is relevant. For cases where a "duplicate" can be identified by a single unique value (or combination of values), I would instead apply a UNIQUE CONSTRAINT to the table and catch/handle constraint violations instead. – Leigh Feb 16 '16 at 05:33