How check with CF whether data already exists in DB table

[tomtomtom]

Hi all

I'm building a little application in CF with a MS Access DB. I've got a table where I store persons, first name, last name, date of birth and some more. I've started saving data and now I get sometimes troubles because I cannot remember whether I already stored a certain person or not. Actually I wanted to build a little check in CF but I do not understand the basic of such a check. What I did so far: Before I save the data to the table I run a lttle query which checks for the first name and the last name (see below). If I get a result I through the request back telling that this person already exists. So far it's ok, but if I want to edit an existing data set I cannot save it to the database because it is alredy there. Is my problem understandable?

Here my code on how I would like to check whether the data already exists:

<!-- Here I access the database and send the three criteria to check for -->

<cfmodule template="../common/patient.cfm"

     action = "checkpatient"
     patient_name = "#patient_name#"
     patient_fname = "#patient_fname#"
     patient_dob = "#cf_patient_dob#"

     output = "get_CheckPatient">

<cfif get_CheckPatient.recordcount GT 0>
  <cfset error_message = "The data you try to save exist already. Would you like to save it anyway?">
  <cfinclude template="patient_edit.cfm">
  <cfexit>
</cfif>

As you can see I would like even to let the user choose whether he wants to save the data - it might be that there are more than one person having the same name and date of birth.

Thank you very much for your help!

[Dan_Bracuk]

Before worrying about how to code it, have you decided what you want to do about two people having the same names and birth dates?

[tomtomtom]

Hello Dan

Yes, they most probably do not live at the same address, so I want to save 'em both in the database...

[Adam Cameron.]

By the time you come to put the data in the DB, you shuld already know whether it's an update to an existing record or a new record.  If it's an update, then you must have already actively fetched that record from the DB, so when you come to want to write the data back, you just write it back to the same record.

However if it was not based on an existing record, then it's obviously an insert.

This question - ie: that you are asking it - suggests you need to get up to speed with how databases and SQL work.  Find some tutorials or buy a book or something.  Dan will suggest one in a moment... 😉

--

Adam

[Dan_Bracuk]

Since he's using Access, I suggest it's Help File.