kv_ravi
asked on
Check if record already exists while submitting a form
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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>
<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>
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>
<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>
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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.