asked on

Existing Record Verification

Hi,
I have a situation here in my coldfusion application, where i need to verify that the user trying to save the data, is not already existed in the table. I am trying to use cfajaxproxy to solve this issue. I've 4 values to pass and match'em in my query. if record count is equal or more than 1, then it'll show the error to user without submitting the form. the sample code of .cfm and .cfc are as under:

CFM FILE CODE:

<cfajaxproxy cfc="sample" jsclassname="chkExistingClass" />

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Archive Form</title>
</head>
<script type="text/javascript" language="javascript">
function chkExistingRecord(){
	
	var pRefForm = document.getElementById('PatientsRef').value;
	var vDateForm = document.getElementById('VisitDate').value;
	var aTimeForm = document.getElementById('ArrivalTime').value;
	var dTimeForm = document.getElementById('DepartureTime').value;
	
	var checkRecord = new chkExistingClass();
	var resultRef =  checkRecord.findRecord(pRefForm);	
	var resultDate =  checkRecord.findRecord(vDateForm);
	var resultArrival =  checkRecord.findRecord(aTimeForm);
	var resultDeparture =  checkRecord.findRecord(dTimeForm);

	if(recordcount>=1){
	alert('the information you are entering is already existed in sytem.');	return false;	}
	else
	{return true;}

}
</script>
<body>
<cfform onsubmit="return chkExistingRecord(_CF_this)">
PATIENTS REF:<cfinput type="text" name="PatientsRef" id="PatientsRef" value="65"><br />
VISIT DATE:<cfinput type="text" name="VisitDate" id="VisitDate" value="05/05/2010"><br />
PATIENTS NAME:<cfinput type="text" name="patientName" id="patientName" value="Helloworld"><br />
ARRIVAL TIME:<cfinput type="text" name="ArrivalTime" id="ArrivalTime" value="02:00AM"><br />
DEPARTURE TIME:<cfinput type="text" name="DepartureTime" id="DepartureTime" value="02:04AM"><br />
<cfinput type="submit" name="submit" value="SUBMIT FORM" id="submit">
</cfform>
</body>
</html>

Open in new window

CFC CODE:

<cfcomponent>
<cffunction name="findRecord" access="remote" returnType="string">
    <cfargument name="pRefForm" type="numeric" required="yes">  
    <cfargument name="vDateForm" type="date" required="yes">
    <cfargument name="aTimeForm" type="string" required="yes">
    <cfargument name="dTimeForm" type="string" required="yes"> 
    
		<cfset var getRecord ="">
            <cfquery name="getRecord" datasource="connTynet">
                SELECT * FROM Table
                WHERE PatientsRef=#ARGUMENTS.pRefForm#
                AND VisitDate='#ARGUMENTS.vDateForm#'
                AND ArrivalTime='#ARGUMENTS.aTimeForm#'
                AND DepartureTime='#ARGUMENTS.dTimeForm#'        
            </cfquery>
            
    <cfreturn getRecord.Recordcount>
</cffunction>
    
</cfcomponent>

Open in new window

Any comments, feedback and reply would be deeply appreciated. Thanks in advance.

ASKER CERTIFIED SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

_agx_

Correction, this line in the CFC

WHERE PatientsRef = <cfqueryparam value="#ARGUMENTS.pRefForm#" cfsqltype="integer" />

Should be:

WHERE PatientsRef = <cfqueryparam value="#ARGUMENTS.pRefForm#" cfsqltype="cf_sql_integer" />

_agx_

Also, I forgot to mention you should use cfqueryparam on all of the arguments to guard against sql injection. I didn't add it because I didn't know the data types of your 4 columns or your db type.

I have to hit the sack soon .. but will check back later.

s_hausen

ASKER

perfect solution...