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

comparing search input values with <cfquery> values before <cfquery> insert

Hello again.

1. I have a "input new customer" coldfusion page that allows adding of new customers into an access database.
2. The values that can be inputted on the page is Customer First name, last name, phone # and office #.
3. When the submit button is clicked I would like a check of the page values against the customer table in the database to make sure there isn't a record already entered.

Thanks in advance,

Below is the code:

<!---add new customer page--->

<form action="inc.cfm?user_name=#url.user_name#" method="post">

<td><b>Customer Last Name:</b></td>
<td><input type="text" name="ln" size="25" maxlength="25"></td>

<td><b>Customer First Name:</b></td>
<td><input type="text" name="fn" size="25" maxlength="25"></td>

<td><b>Customer Office Phone #:</b></td>
<td><input type="text" name="copn" size="15" maxlength="15"></td>

<td><b>Cubicle #:</b></td>
<td><input type="text" name="cubn" size="15" maxlength="15"></td>

<td><input type="Submit" value="Submit">&nbsp;<input type="Reset" value="Clear Form"></td>

<!--- <cfquery> insert page --->

INSERT INTO Customers (Last_Name, First_Name, Business_Phone, Cubicle_num)
  VALUES('#Form.ln#', '#Form.fn#', '#Form.copn#', '#Form.cubn#')

       alert("Customer <cfoutput>#form.fn# #form.ln#</cfoutput> has been successfully added");

Open in new window

  • 2
1 Solution
erikTsomikSystem Architect, CF programmer Commented:
this is really simple:

Example 1:

once you click save do this

<cfquery name="checkCustomer" datasource="xxx">

select * from Customers
where lastname = '#form.ln#' or first_name = '#form.fn#'

continue set where clause as needed

<cfif not checkCustomer.recordcount>
   do the insert here

Simply put the message that this custome already exists

Example 2: just use jquery
where you will use.ajax and upon typing you will check if the first (or other fields are ) name exists in  the  Database
(no points ...)

that approach'll work with small apps w/few concurrent users.  but it's not thread safe. if preventing duplicate records is critical, I'd create a unique index on the table as well.
jslaughtAuthor Commented:
worked like a charm. Thanks for the assistance.
erikTsomikSystem Architect, CF programmer Commented:
thank you

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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