Avoiding duplicate records

[mtgriffiths86]

Hi All,

What i am trying to do is avoid inserting duplicate records into a database. I am inserting the records using a webpage but when i enter a flight number(primary key) that already exists i get an error and system crashes.

What i want to do is if a duplicate record is about to be entered then redirect the user to another page. Is this possible

Thanks in advance

Matthew

[DrBunchman]

Hi Matthew,

You can check whether a primary exists already easily enough by running a query against the table before insertion. Something like:

 Dim strSql 
Dim oConn
Dim oRS
 
strSQL = "SELECT COUNT(*) AS RecordCount FROM Table1 WHERE FlightNumber = '" & strFlightNumber & "' "
 
oConn.Open oRS, strSql
 

If the value of oRS("RecordCount") is 0 then no record exists and you can continue with the insert. If it does then you can response.redirect the user to another page as required.

I presume you are not using an automatically generated primary key (a GUID or IDENTITY field) as if you were there would be no chance of a duplicate primary key occurring

Hope this helps,

Dr B

[jhardman]

I use a similar approach to Dr B. I Open a recordset with the exact criteria I am looking for - If the recordset is empty, I add a new record with that info, otherwise I don't. The code looks something like this:

query = "SELECT * FROM myTable WHERE firstName = '"
query = query & fname & "' AND lastName = '"
query = query & lname & "' AND phoneNumber = "
query = query & phNumber
 
objRS.Open query, objConn, AdOpenDynamic, adLockOptimistic
 
if objRS.eof then
   objRS.addNew
   objRS("firstName") = fname
   objRS("lastName") = lname
   objRS("phoneNumber") = phNumber
   objRS.update
else
   response.write "Hey, Idiot!  This record is already in the db."
end if