[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1465
  • Last Modified:

Access error message for duplicate index values

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
johnhardy
Asked:
johnhardy
  • 6
  • 6
  • 3
  • +1
1 Solution
 
Rey Obrero (Capricorn1)Commented:
try using the error number instead of the message

if err.number=?? then
0
 
L00MCommented:
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
 
johnhardyAuthor Commented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rey Obrero (Capricorn1)Commented:
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
 
johnhardyAuthor Commented:
Thanks capricorn1:

yes I did that pls see top line
0
 
L00MCommented:
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
 
johnhardyAuthor Commented:
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
 
L00MCommented:
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
 
Rey Obrero (Capricorn1)Commented:
try creating error handling routine

On error goto MyErrorHandler






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

     ..
     .
     Err.Clear
     resume next

     end if
     
0
 
L00MCommented:
Can't do that in classic ASP capricorn
0
 
johnhardyAuthor Commented:
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
 
johnhardyAuthor Commented:
I think I will have to find another method for this.
Any further ideas please
0
 
L00MCommented:
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
 
johnhardyAuthor Commented:
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
 
L00MCommented:
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
 
LunchyCommented:
Closed, 500 points refunded.
Lunchy
Friendly Neighbourhood Community Support Moderator
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 6
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now