how can I check if a record already in database before submitting form in ColdFusion applications

Question

<cfquery datasource = "myDb" name = "compare">
    select *
    from users
    where cnic = #form.cnic#

</cfquery>
<cfif compare.cnic eq form.cnic>
        <p> *CNIC already Exists </p>
</cfif>

Answer 1

Depending on your database, too, there are other options. MySQL has some features for INSERT ON DUPLICATE KEY UPDATE (https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html)

In MSSQL you can do: IF NOT EXISTS (SELECT 1 FROM [users] u WITH (NOLOCK) WHERE cnic = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.cnic#") INSERT INTO [users].....

(My cfsqltype syntax might not be 100% correct, but always use . Always.

Those might give you some other ways to handle your scenario.

Answer 2

I think you're misstating the problem. It should be more like,

"How can I show a form to add a record I know is not in the database?"

Please clarify if that is not the case.

Based on your code, I assume there's been a form submission from another page already. You're running a query to see if there is a record in the users table where cnic = #form.cnic#. If there was no previous form submission, then form.cnic wouldn't exist.

<cfquery datasource="myDb" name="compare">
    select *
    from users
    where 
        cnic = #form.cnic#
</cfquery>

So when this page loads, you've done your "check if a record is already in the database" with the query named compare. Now all you need to do is check if there are 0 records in the query.

<cfif compare.recordcount EQ 0>
    <!--- Display form here. ---->
</cfif>

If the query returns any records, then there is at least one record in the database, so no need to show the form or allow it to be submitted.

Answer 3

You can use bellow code in Jquery ajax calling

<cfquery datasource = "myDb" name = "compare">
    select *
    from users
    where cnic = #form.cnic#

</cfquery>

<cfif compare.recordcount GT 0>
        <p> *CNIC already Exists </p>
</cfif>