• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 207
  • Last Modified:

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.
0
s_hausen
Asked:
s_hausen
  • 3
1 Solution
 
_agx_Commented:
     > var resultRef =  checkRecord.findRecord(pRefForm);      

You only need to call the function once. But you need to pass in all 4 arguments. See example below.

     >    <cfargument name="pRefForm" type="numeric" required="yes">  
     >    <cfargument name="vDateForm" type="date" required="yes">

If the values aren't strings, then you should definitely add cfform validation for those fields. Otherwise you'll run into errors if a user enters a bad date or reference number.  In the example I added basic validation for pRefForm (integer) and vDateForm (date) and all 4 fields are "required"


<cfajaxproxy cfc="Artists" 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 handleError(code, message) {
        // add some error handling here ...
        alert('Something went wrong: '+ code +'\nMessage: '+ message);
    }
    
    function chkExistingRecord() {
        var refNum    = document.getElementById('PatientsRef').value;
        var visit     = document.getElementById('VisitDate').value;
        var arrival   = document.getElementById('ArrivalTime').value;
        var departure = document.getElementById('DepartureTime').value;
    
        var checkRecord = new chkExistingClass();
        var recordsFound =  checkRecord.findRecord(refNum, visit, arrival, departure);
        if (recordsFound > 0){
            alert('the information you are entering is already existed in sytem.');    
            return false;    
        }
        return true;
    }
</script>
<body>
<cfform onsubmit="return chkExistingRecord()">
PATIENTS REF:<cfinput type="text" name="PatientsRef" id="PatientsRef" value="65" required="true" validate="integer"><br />
VISIT DATE:<cfinput type="text" name="VisitDate" id="VisitDate" value="05/05/2010" required="true" validate="date"><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" required="true"><br />
DEPARTURE TIME:<cfinput type="text" name="DepartureTime" id="DepartureTime" value="02:04AM" required="true"><br />
<cfinput type="submit" name="submit" value="SUBMIT FORM" id="submit">
</cfform>
</body>
</html>

Open in new window


<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 COUNT(*) AS MatchCount
           FROM   TableName
           WHERE  PatientsRef = <cfqueryparam value="#ARGUMENTS.pRefForm#" cfsqltype="integer" />
           AND    VisitDate='#ARGUMENTS.vDateForm#'
           AND       ArrivalTime = '#ARGUMENTS.aTimeForm#'
           AND    DepartureTime = '#ARGUMENTS.dTimeForm#'
        </cfquery>
            
        <cfreturn getRecord.MatchCount>
    </cffunction>
</cfcomponent>

Open in new window

0
 
_agx_Commented:
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" />
0
 
_agx_Commented:
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.



0
 
s_hausenAuthor Commented:
perfect solution...
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now