[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Access error message for duplicate index values

Posted on 2007-11-27
17
Medium Priority
?
1,446 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

656 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