Solved

Access error message for duplicate index values

Posted on 2007-11-27
17
1,416 Views
Last Modified: 2012-05-05
I have a field in an access table which is indexed with no duplicates
I want to raise and error message in this aspvb page
at the page top I placed
<%On Error Resume Next %>
at the bottom of the page I placed
<%' to catch the error:
    '// Code to execute query
    If Err.number <> 0 Then
        If InStr(Err.Description, "changes you requested to the table were not successful because they would create duplicate values") >0 Then
            Response.Write "<strong>You are trying to insert a duplicate Fund Name into the DataBase. Please select another assignment from the drop down menu.</strong>"
        Else
            Response.Write "An error occurred. (Here you don't know what error has occured but it is NOT the PK violation error. So show the actual error) " & Err.Description
        End If
    End If
    On Error Goto 0
%>

When a duplicate is added the message does not appear and the record is not inserted.
Can anyone see where I am going wrong please
Thanks John
0
Comment
Question by:johnhardy
  • 6
  • 6
  • 3
  • +1
17 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20357541
try using the error number instead of the message

if err.number=?? then
0
 
LVL 11

Expert Comment

by:L00M
ID: 20357716
I may be wrong here, but it seems I read a long time ago that the err.number can be reset to 0 by running a valid statement after an error has occured.

Have you tried adding something like the following to several places in your script? Especially where you suspect the errors will occur?

If err.number > 0 Then Response.Write "Line 123: " & err.number & " | " & err.description & "<br>"
0
 

Author Comment

by:johnhardy
ID: 20357764
Thanks

If I insert a duplicate value into  the table no error number shows, just more of the above text

I am using Access 2003
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20357793
if you want an error to show when duplicate value is inserted to the table
you have to set the unique id or primary key to No duplicates
0
 

Author Comment

by:johnhardy
ID: 20358876
Thanks capricorn1:

yes I did that pls see top line
0
 
LVL 11

Expert Comment

by:L00M
ID: 20358976
If you comment out 'On Error Resume Next, does it bomb out?
If you receive a generic 500 error page, make sure you turn off friendly error messages:

http://www.webwizguide.com/kb/asp_knowledgebase/friendly_HTTP_error_messages.asp
0
 

Author Comment

by:johnhardy
ID: 20359549
If I comment out 'On Error Resume Next, then I get
Microsoft JET Database Engine (0x80004005)
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.
/prestigeadmin/FundAdd.asp, line 39
Line 39 is     MM_editCmd.Execute
0
 
LVL 11

Expert Comment

by:L00M
ID: 20359619
Ok, let's find out what SQL statement is being passed in. Just prior to line 39, it should be assigning the SQL statement. Just after that line, put:

Response.Write <the sql statement variable>
Response.End

Refresh the page. Is it trying to assign the ID instead of allowing the database to do it? Can you copy and paste that SQL statement directly into the database? If so, does it bomb there too?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20359629
try creating error handling routine

On error goto MyErrorHandler






MyErrorhandler:
     If Err.number <> 0 Then .....

     ..
     .
     Err.Clear
     resume next

     end if
     
0
 
LVL 11

Expert Comment

by:L00M
ID: 20359659
Can't do that in classic ASP capricorn
0
 

Author Comment

by:johnhardy
ID: 20361107
Thanks
Line 39 now reads MM_editCmd.Execute
Response.Write MM_editCmd.Execute
Response.End
    MM_editCmd.ActiveConnection.Close      
%>
When that is run the same page appears and is empty
0
 

Author Comment

by:johnhardy
ID: 20577999
I think I will have to find another method for this.
Any further ideas please
0
 
LVL 11

Expert Comment

by:L00M
ID: 20581962
It's been a while....

Let's see.....

Instead of
Response.Write MM_editCmd.Execute

I wanted, instead, to output the actual SQL statement being executed. Have you found where that's being assigned?
If so, response.write it out, then run it within the database to see if the desired error occurs.
0
 

Accepted Solution

by:
johnhardy earned 0 total points
ID: 20582278
Thanks for your answer and your previous help.
The long time interval was due to a hard disc failure. Although I have a mirror disc as a first line back up, when it came to the restore it was not so simple!
I have now found what I think (for me  as a Dreamweaver user)  a very simple answer.
In Dreamweaver there is an extension called Check new user name.
I have removed  the no duplicates propoerty from the access field and use the DW extension Check new user name.
I had not thought to use this extension in this manner but it appears to work fine.
As I have used my own solution I will ask a moderator what I should do about awarding the points. I Hope that is OK

The code this extension produces is as follows
<%
' *** Redirect if username exists
MM_flag = "MM_insert"
If (CStr(Request(MM_flag)) <> "") Then
  Dim MM_rsKey
  Dim MM_rsKey_cmd
 
  MM_dupKeyRedirect = "FundAdd_Duplicate.asp"
  MM_dupKeyUsernameValue = CStr(Request.Form("FundName"))
  Set MM_rsKey_cmd = Server.CreateObject ("ADODB.Command")
  MM_rsKey_cmd.ActiveConnection = MM_PrestConn_STRING
  MM_rsKey_cmd.CommandText = "SELECT FundName FROM Funds WHERE FundName = ?"
  MM_rsKey_cmd.Prepared = true
  MM_rsKey_cmd.Parameters.Append MM_rsKey_cmd.CreateParameter("param1", 200, 1, 100, MM_dupKeyUsernameValue) ' adVarChar
  Set MM_rsKey = MM_rsKey_cmd.Execute
  If Not MM_rsKey.EOF Or Not MM_rsKey.BOF Then
    ' the username was found - can not add the requested username
    MM_qsChar = "?"
    If (InStr(1, MM_dupKeyRedirect, "?") >= 1) Then MM_qsChar = "&"
    MM_dupKeyRedirect = MM_dupKeyRedirect & MM_qsChar & "requsername=" & MM_dupKeyUsernameValue
    Response.Redirect(MM_dupKeyRedirect)
  End If
  MM_rsKey.Close
End If
%>
0
 
LVL 11

Expert Comment

by:L00M
ID: 20583081
No worries.
I would request a PAQ and refund since you found your own solution. Up on the original question, there is now a 'Delete Question' button. Click on that and follow the directions.

Happy New Year!
0
 
LVL 2

Expert Comment

by:Lunchy
ID: 20610296
Closed, 500 points refunded.
Lunchy
Friendly Neighbourhood Community Support Moderator
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question