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

Posted on 2011-05-05
Last Modified: 2012-05-11
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

Question by:jslaught
    LVL 19

    Accepted Solution

    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
    LVL 51

    Expert Comment

    (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.

    Author Closing Comment

    worked like a charm. Thanks for the assistance.
    LVL 19

    Expert Comment

    thank you

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
    Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now