Capturing a duplicate record message when inserting

[charlesb]

I have an Ultradev Insert page adding a record, the main primary key is generated. However, it is possible that the user could enter data such that another unique index causes a problem.

I either want to stop this insert if a duplicate is being entered.

Or issue a message telling the user that the details they are entering is a duplicate of another record.

I have tried putting <% On Error Resume Next %> at top of page and then after the execute of the insert command I placed.

<%If Err.Number = &quot;&quot; Then
Response.Write(&quot;Error #&quot; & Err.Number &&quot; <br>&quot
Response.Write(&quot;Error Source: &quot; & Err.Source & &quot;<br>&quot
Response.Write(&quot;Error Description: &quot; & Err.Description & &quot;<br>&quot
Response.end
End If%>

Nothing happens!, the insert is attempted nothing comes out and no record is written.

If I take the <% On Error Resume Next %> out I get an MSaccess database error

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.


telling me that I cannot add record !!!!!!

Can anybody help?

All non duplicate inserts work fine.

Thank you charles

 

[mlawson]

Charles,

I have had this problem too, it is generated by the server based on the indexing status of the Access Database (ie allow dups, no dups etc). If there is a way around this I too would like to hear of it.

In your case, I'm not sure what you want to achieve; is your other unique identifier used for a selection, and what field type do you generate you initial primary key?

If you could post your fields and explain what you want to do.

Thanks

M

 

[Dorff]

Well, you really have already captured the error message when you are trying to duplicate.

The code:

<%If Err.Number = &quot;&quot; Then
End If%>

Is trapping the error and letting you know you cannot enter a new record, which I believe is what you wanted. Just change the code in the middle to be what you want it to be, such as a new error message. No one said you had to keep it a generic error message or even use the information in the Error object.

If you are saying that you're error message is not showing up when you use the On Error Resume Next, that makes perfect sense. Get rid of that line and your custom message should pop up. I could just be misunderstanding your problem.

More information never hurts.
Ryan
rmindorff@hotmail.com

 

[derren]

Hi

I have done this in duplicate sensitive record insertion:

select onefield from table where dodgyfield1=dodgyfield1new or dodgyfield2=dodgyfield2new or dodgyfield3=dodgyfield3new etc...

Basically i do a quick query of the database to see if any of my sensitive values are already in the table. If the recordset is EOF then I know I can proceed to insert the record, else I can handle the error gracefully without hassle.

It is worth doing.

If it is just the primary key generation you could keep a &quot;nextid&quot; application variable. I covered this in

thread333-235484 Derren
[Mediocre talent - spread really thin]

 

[charlesb]

Thanks I have tested and handled the duplicate okay now. Thanks for your help

 

[Guest_imported]

can anyone help me with this issue please. Preferably with a ste-by-step guide

thanks in advance