In .NET I am using the OleDB driver to access a Visual FoxPro database. I have everything working fine, except when it comes to marked records.

Let's assume we have a foxpro table that has a code field (Attendance Code) that is unique and is the primary key for the table. In an operation I can issue an SQL delete command, which marks the record correctly. I then try to issue an SQL insert to add a new record with the same key value as the deleted one and a Trigger Error is generated, which only makes sense.

I would like to be able to:
1) Do a search of marked records before insertion to avoid the trigger error
2) Recall a marked record and issue an update rather than an insert

I do realize that if I used good database practices, such as having a primary key that is auto generated and not meaningful, I could avoid this scenario. However, I am trying to develop a web interface for an existing VFP system that would require an extensive rewrite to correct the problem with primary keys.

My best guess at a solution so far is to use a stored procedure in the VFP DBC to handle checking for and recalling the record, but I'm looking for something easier.

Thanks for any help that can be provided.