Check if record already exists while submitting a form

kv_ravi
kv_ravi used Ask the Experts™
on
hi,
i am trying to check for repeated data while adding a new record to a table using the form in ColdFusion. if the record exists, i would like to display a message saying that the record already exist and still let the user create the repeated record if needed. thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Hi,

if i understand u correctly ... what u can do is :

1. have a page that displays the form for entering the details of a record.

2. when the user submits this page [check for uniqueness of the record (this cls be the username / userid)].

3. hit a query in the table & chk if it exists ... if the record exists ... then dont insert the record.

4. display a msg saying record already exists ... do u wish to continue ...

5. if user clicks on yes button ... then submit the page & insert the record in the table - else return to previous page & display teh form again.

the above is step by step logic for u to code.

if u want me to write the code - do let me know ur table details as i can then decide on the code accordingly !

K'Rgds
Anand

Author

Commented:
hi,
Thanks for looking into my question. The tables are "Shift" containing the fields ShiftId (autonumber), Date (text), Start (text), Finish(text) and EmployeeId(number), where EmployeeId is from table "Employee" containing the fields EmployeeId (autonumber), EmployeeName (text), Address (text), DoB (text). I would mainly like to verify the repeatition for table "Shift".
Thx,
Ravi.

Commented:
how you want the record should not duplicate. as ShiftId is autogenerated. are u need there should not be any duplicate in in whole combination except ShiftId.

<cfquery name="test" datasource="dsn">
   select count(*) reccount from shift
   where date = '#form.date#'
   and Start = '#form.sdate#'
   and finish = '#form.fdate#'
   and EmployeeId = '#form.empid#'
   
   
</cfquery>

so check all combination of user input with database. if record exist show message to user.

<cfset count = 0>
<cfloop query="test">
   <cfset count = #reccount#
</cfloop>

<cfif count GT 0>
   redirect to one page showing details already exist.
<cfelse>
  do the needful save operation.
</cfif>
 
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

There is a simpler way to do that.

<cfquery>
whatever you query code is
with where clause to see if record exists
</cfquery>

<cfif query.recordcount gt 0>
This record already exists!
<cfelse>
Form code to create new record
</cfif>
Commented:
You can also do a combination of Indexes in the Access database, to ensure no data is inserted (or updated) that is repeted, and then use CFTRY and CFCATCH to control the errors that the database throws when encounters repeted values, yes is more complicated and stuff, but what the hell, it's more one way to do the job ;)

Commented:
No comment has been added lately, so it's time to clean up this question.
I will leave the following recommendation in the Cleanup topic area:

Split anandkp & fmedia

Please leave any comments here within the next four days.

mrichmon
EE Cleanup Volunteer

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial