2. ASPRunnerPro General questions
  3. add to tables with unique indexes
This topic is locked

add to tables with unique indexes

 		7/31/2005 5:28:03 PM
ASPRunnerPro General questions
author

I have a field in a table (MS Access) defined as Required|No Zero Length|Indexed|Duplicates Not OK.
When I (purposefully) try to add a record with the indexed field I know is a duplicate, I get an error...
Technical Information (for support personnel)
Error Type:

Microsoft OLE DB Provider for ODBC Drivers (0x80004005)

[Microsoft][ODBC Microsoft Access Driver]Error in row

/IS/HR/Employee_add.asp, line 1228
Browser Type:

Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1)
Page:

POST 3476 bytes to /IS/HR/Employee_add.asp
POST Data:

error '80020009'

Exception occurred.
/iisHelp/common/500-100.asp, line 223
<end quote>
Why isn't this Exception handled somehow?
admin 8/1/2005

There is nothing I can do when you insert a record with a duplicate value in field that don't allow duplicates. I can hide this error message however it won't help user understand what happened.
If you have such restrictions make this field autonumber and remove it from ADD/EDIT pages to keep off duplicates.
500312 8/1/2005

Well, don't expect my users to understand
HTTP 500.100 - Internal Server Error - ASP error

Internet Information Services
They will just think thre is something wrong with the software.
This exception should be handled in a more elegant way.
I can't do autonumber, these are usernames. I can't have multiple people with the same username. It breaks the whole permissions structure. And I want to have meaningful usernames.
There should be some code in edit and add for unique fields to check if the value already exists. If it does, the user should get the form back along with some message.
admin 8/2/2005

I see what you saying.
You need to add a code snippet that will check enetered username for uniqueness and prevent duplicate values. That caould be something like this. Place it before actually adding new record.

Set rsTmp = server.CreateObject ("ADODB.Recordset")

rs.Open "select * from members where username = '" & GetRequestForm("UserName") & "'", dbConnection, 1, 2

If not rs.Eof Then

    response.write "Username <b>" & GetRequestForm("UserName")& "</b>already exists."

    response.end

end if