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("RecordCoun t") is 0 then no record exists and you can continue with the insert. If it does then you can response.redire ct 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:
[code=asp]query = "SELECT * FROM myTable WHERE firstName = '"
query = query & fname & "' AND lastName = '"
query = query & lname & "' AND phoneNumber = "
query = query & phNumber

objRS.Open query, objConn, AdOpenDynamic, adLockOptimisti c

if objRS.eof then
objRS.addNew
objRS("firstNam e") = fname
objRS("lastName ") = lname
objRS("phoneNum ber") = phNumber
objRS.update
else
response.write "Hey, Idiot! This record is already in the db."
end if[/code]