Solved

Access error message for duplicate index values

Posted on 2007-11-27
17
1,407 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 119

Expert Comment

by:Rey Obrero
Comment Utility
try using the error number instead of the message

if err.number=?? then
0
 
LVL 11

Expert Comment

by:L00M
Comment Utility
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
Comment Utility
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
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
Thanks capricorn1:

yes I did that pls see top line
0
 
LVL 11

Expert Comment

by:L00M
Comment Utility
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
Comment Utility
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
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
Can't do that in classic ASP capricorn
0
 

Author Comment

by:johnhardy
Comment Utility
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
Comment Utility
I think I will have to find another method for this.
Any further ideas please
0
 
LVL 11

Expert Comment

by:L00M
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Closed, 500 points refunded.
Lunchy
Friendly Neighbourhood Community Support Moderator
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now