Error handling: INSERT INTO with UNIQUE Index

[RottPaws]

Short version:
How do I handle an error that occurs on my asp page when an attempt is made to add a duplicate record to a SQL7 table with a unique index through an ADO using vbscript?

Additional information:
I've got an ASP page that pulls data from two different databases and puts it all together for the user. Occasionally, it encounters a case where a reference for one system cannot be found in the other.

Relying on the user to report such instances so they can be fixed is not realistic, so I've created a table in a SQL7 db. Whenever the page runs into this situation it runs an append query to add a record into the table so someone can be informed about and fix the problem.

The problem is, I don't need to have a single problem reported 50 times in the table, so I tried adding a UNIQUE index in the SQL7 table. My thinking was that if it tried to add record that was a duplicate in the key field, it would just ignore it and move on. Unfortunately, I have found this is not the case. Instead it fails, giving me Violation of UNIQUE KEY constraint error.
_________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[Tarwn]

Well, there are several ways to handle this, one would consist of doing a read ahead, ie selecting from that table for the id before adding it and seeing if you get back a result. This could be handy in that if you do get a result than instead od inserting (which we kjnow will cause an error) you could update the record. If you add an error_count field to the table with an initial value of 1 than you could use your update to iuncrement that field, thus keeping count of how many times this error occurred, which will give whoever has to make the changes a good idea of which records are the most important to start with.
While this will cause more overhead because you will need to execute a query and then either an insert or update, I think it will not be noticeable and will add functionality to the site.
-Tarwn The three most dangerous things in the world are a programmer with a soldering iron, a hardware type with a program patch, and a user with an idea
-computer saying (Wiz Biz - Rick Cook)

 

[RottPaws]

Something like this???

Create a cursor that does a SELECT query on my BAD_REF table where the circuit_ID = the failed circuit_ID and if that cursor is empty, then run the append query.

I think I can manage that.

Thanks, _________
Rott Paws

...It's not a bug. It's an undocumented feature!!!