Hi all,

I have a good one here and i am hoping there is an answer for this.

I have a form which displays a profile. Each profile has multiple rows of data, each row consists of a sort order, an operation name and a delete flag, which when saved runs through a loop in a cfc and inserts into the db, no problem there.

There is a business rule that the Operation name field must not be duplicated within each profile.

Now i can write code to check for a duplication of an operation when a new operation name is created, no problem.

What i cant seem to get my head around is to check to make sure that there are no duplicates in any of the rows.

What i first did was create a select statement to check for duplicates and then if the select statement returned a recordcount greater than 0 it would return back an error.

Of course this doesnt work because each form value on the screen already exists in the db so every value was handled as a duplicate.

The only way i can think of doing it is to write a cfc that deletes the existing values from the table and creates a new values and at the point of creation checks for duplicate values, if there are dups, then do a transaction roll back and inform the user, i know its a bit wild but looking for any other ideas on how to handle this.

JM

Managed to figure out how to do this.

If anyones interested then let me know.

Yep, no problem:

I created the usual insert sql statement to insert all of the updated rows back into the DB.

Next i created a dupcheck select statement which basically is a select statement which checks for dups onitself:

<cfquery name="qryCheckOperationNameDups" datasource="UKCHAPP145">
	SELECT DISTINCT
		a.OPERATION_UUID
    FROM
		UKCHAPP145.TBL_OPERATION a,
        UKCHAPP145.TBL_OPERATION b
    WHERE 
		a.HEADER_UUID = <cfqueryparam value="#arguments.HEADER_UUID#" cfsqltype="cf_sql_integer">
    AND 
		b.HEADER_UUID = <cfqueryparam value="#arguments.HEADER_UUID#" cfsqltype="cf_sql_integer">
	AND
    	a.OPERATION_UUID > b.OPERATION_UUID
    AND
    	a.OPERATION_NAME = b.OPERATION_NAME
    AND
    	a.DELETE_FLAG = <cfqueryparam value="N" cfsqltype="cf_sql_varchar">
    AND
    	b.DELETE_FLAG = <cfqueryparam value="N" cfsqltype="cf_sql_varchar">
    ORDER BY
    	a.OPERATION_UUID
</cfquery>

Then used an if statement to run a condition to see if any rows were returned, if they were then return back to the user an error plus do a transaction rollback.

It works very well.
I found the dup select statement idea on a blog and modified the code to suit my application.

cheers

JM

This question has already been answered. Start a new discussion instead.