1 Reply Latest reply on Mar 12, 2009 7:34 AM by Lithcause

    ajax help checking for duplicate records

    Lithcause
      i am trying to have a form in ajx that will also insert a new contact... i havit working with the insert part but i want to have it check for duplicate records before inserting the contact... here is the form ... and i also have a cfc for the functions

      <cfcomponent output="false">
      <cfset this.dsn="myserver">
      <!--- Populates the grower list Select --->
      <cffunction name="getCompany" access="remote" returntype="array">
      <cfset var rsData="">
      <cfset var myReturn=ArrayNew(2)>
      <cfset var i=0>
      <cfquery name="rsData" datasource="myserver">
      SELECT cid ,Company
      FROM Contacts
      order by Company asc
      </cfquery>
      <cfloop query="rsData">
      <cfset myReturn[rsData.currentrow] [1]=rsdata.cid>
      <cfset myReturn[rsData.currentrow] [2]=rsdata.Company>
      </cfloop>
      <cfreturn myReturn>
      </cffunction>

      <!--- Populates list related to grower --->
      <cffunction name="getcontacts" access="remote" returntype="array">
      <cfargument name="cid" type="string" required="no">
      <cfset var rsData="">
      <cfset var myReturn=Arraynew(2)>
      <cfset var i=0>
      <cftry>
      <cfquery name="rsdata" datasource="myserver">
      SELECT cid, Company, FullName, Lname, Fname, Address1, Address2, City, State, zip, country, Phone, ext, cell, Fax, tollfree, Web, Email, Title, ExecutiveTitle
      FROM Contacts
      WHERE Contacts.Company = '#arguments.cid#'
      </cfquery>
      <cfcatch type="any">
      <cfset returnStruct.success = false />
      <cfset returnStruct.message = cfcatch.message />
      </cfcatch>
      </cftry>
      <cfloop query="rsdata">
      <cfif rsdata.recordcount gt 0>
      <cfset myReturn[rsData.currentrow] [1]=rsdata.cid>
      <cfset myReturn[rsData.currentrow] [2]=rsdata.FullName>
      <!--- <cfelse>
      <cfset rsdata.cid = 999999>
      <cfset rsdata.FullName = 'none'>
      <cfset myReturn[rsData.currentrow] [1]=rsdata.cid>
      <cfset myReturn[rsData.currentrow] [2]=rsdata.FullName> --->
      </cfif>
      </cfloop>
      <cfreturn myReturn>
      </cffunction>

      <!--- Gets contact info for 2page contacts!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! --->
      <cffunction name="getcontactsinfo2" access="remote" returnType="struct">
      <cfargument name="growerName" type="string" required="no">
      <cfset var data="">
      <cfset var c = "">
      <cfset var s = structNew()>

      <cftry>
      <cfquery name="data" datasource="myserver">
      SELECT cid, Company, FullName, Lname, Fname, Address1, Address2, City, State, zip, country, Phone, ext, cell, Fax, tollfree, Web, Email, Title, ExecutiveTitle
      FROM Contacts
      WHERE Company= <cfqueryparam cfsqltype="cf_sql_varcar" value="#arguments.growerName#">
      </cfquery>

      <cfloop list="#data.columnlist#" index="c">
      <cfset s[c] = data[c][1]>
      </cfloop>
      <cfcatch type="any">
      <cfset returnStruct.success = false />
      <cfset returnStruct.message = cfcatch.message />
      </cfcatch>
      </cftry>
      <cfreturn s>
      </cffunction>

      <cffunction name="getcontactsinfo" access="remote" returnType="struct">
      <cfargument name="cid" type="numeric" required="true">
      <cfset var data="">
      <cfset var c = "">
      <cfset var s = structNew()>
      <cftry>
      <cfquery name="data" datasource="myserver">
      SELECT cid, Company, FullName, Lname, Fname, Address1, Address2, City, State, zip, country, Phone, ext, cell, Fax, tollfree, Web, Email, Title, ExecutiveTitle
      FROM Contacts
      WHERE contacts.cid= <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.cid#">
      </cfquery>
      <!--- --->
      <cfloop list="#data.columnlist#" index="c">
      <cfset s[c] = data[c][1]>
      </cfloop>
      <cfcatch type="any">
      <cfset returnStruct.success = false />
      <cfset returnStruct.message = cfcatch.message />
      </cfcatch>
      </cftry>
      <cfreturn s>
      </cffunction>

      <!--- Updates the database of grower contacts --->
      <cffunction name="markTaskComplete" output="false" returntype="struct" access="remote" hint="i mark a task complete">
      <cfargument name="cid2" type="numeric" required="true" />
      <cfargument name="company2" type="string" required="true" />
      <cfargument name="address2" type="string" required="true" />
      <cfargument name="city2" type="string" required="true" />
      <cfargument name="state2" type="string" required="true" />
      <cfargument name="zip2" type="string" required="true" />
      <cfset var qMarkTaskComplete = "" />
      <cfset var returnStruct = structNew() />
      <cfset returnStruct.success = true />
      <cfset returnStruct.taskID = arguments.cid2 />
      <cftry>
      <cfquery name="qMarkTaskComplete" datasource="myserver">
      UPDATE
      Contacts
      SET
      Company = <cfqueryparam value="#arguments.company2#" cfsqltype="cf_sq_varcar" /> ,
      Address1 = <cfqueryparam value="#arguments.address2#" cfsqltype="cf_sq_varcar" /> ,
      City = <cfqueryparam value="#arguments.city2#" cfsqltype="cf_sq_varcar" />,
      State = <cfqueryparam value="#arguments.state2#" cfsqltype="cf_sq_varcar" />,
      zip = <cfqueryparam value="#arguments.zip2#" cfsqltype="cf_sq_varcar" />
      WHERE
      cid = <cfqueryparam value="#arguments.cid2#" cfsqltype="cf_sq_int" />
      </cfquery>
      <cfcatch type="Database">
      <cfset returnStruct.success = false />
      <cfset returnStruct.message = cfcatch.message />
      </cfcatch>
      </cftry>
      <cfdump var="#returnStruct#"/>

      <cfreturn returnStruct />

      </cffunction>

      <cffunction name="lookupGrower" access="remote" returntype="array">
      <cfargument name="search" type="any" required="false" default="">

      <!--- Define variables --->
      <cfset var data="">
      <cfset var result=ArrayNew(1)>

      <!--- Do search --->
      <cfquery name="data" datasource="myserver">
      SELECT cid, Company, FullName, Lname, Fname, Address1, Address2, City, State, zip, country, Phone, ext, cell, Fax, tollfree, Web, Email, Title, ExecutiveTitle
      FROM Contacts
      WHERE (Company LIKE '#ARGUMENTS.search#%')

      </cfquery>

      <!--- Build result array --->
      <cfloop query="data">
      <cfset ArrayAppend(result, Company)>
      </cfloop>

      <!--- And return it --->
      <cfreturn result>
      </cffunction>


      <cffunction name="markTaskComplete2" output="false" returntype="struct" access="remote" hint="i mark a task complete">

      <cfargument name="company3" type="string" required="true" />
      <cfargument name="address3" type="string" required="true" />
      <cfargument name="city3" type="string" required="true" />
      <cfargument name="state3" type="string" required="true" />
      <cfargument name="zip3" type="string" required="true" />
      <cfset var qMarkTaskComplete = "" />
      <cfset var returnStruct = structNew() />
      <cfset returnStruct.success = true />
      <cfset returnStruct.taskID = arguments.cid />
      <cftry>
      <cfquery name="qMarkTaskComplete" datasource="myserver">
      INSERT INTO KYIntranet.dbo.Contacts
      (Company
      ,Address1
      ,City
      ,State
      ,zip)
      VALUES
      (<cfqueryparam value="#arguments.company3#" cfsqltype="cf_sq_varcar" /> , <cfqueryparam value="#arguments.address3#" cfsqltype="cf_sq_varcar" /> ,<cfqueryparam value="#arguments.city3#" cfsqltype="cf_sq_varcar" />,<cfqueryparam value="#arguments.state3#" cfsqltype="cf_sq_varcar" />,<cfqueryparam value="#arguments.zip3#" cfsqltype="cf_sq_varcar" />)
      </cfquery>
      <cfcatch type="any">
      <cfset returnStruct.success = false />
      <cfset returnStruct.message = cfcatch.message />
      </cfcatch>
      </cftry>
      <cfreturn returnStruct />
      </cffunction>

      </cfcomponent>

      END CFC!!!!!!!!