Inserting into Access DB - key problems

[Albuckj2]

I have a problem.
I am looking to insert data into a table in an Access Database. The data inserts into the table perfectly but brings up an error in the browser pointing to the line where it executes the sql. The error is:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] 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.

In my table I have 2 fields that I want as the primary key - Username, and ScreenName (because one user can have several different screen names, but none of the same name). I have clicked the key symbol in access on both of these rows (in the design view) to make these the primary key. This seems fine to me. Even when I insert the data from the website it inserts fine. But this error comes up and halts the user. I'm DEFINITELY not inserting some data which already has the same username/screen_name combination as a record in the table. I'm not big on my databases, so need to know how to get round this.

I'm using IIS and running everything on my local PC. I don't understand why it throws up this error but still inserts the correct data into the database...

Thanks for any help.

 

[ceeleelewis]

This is just a question, did you allow duplicates in either of the primary keys?

 

[Albuckj2]

I think your question is the heart of the problem.

Once I look at the 'indexes' section in access I can see my 2 fields. There is a 'unique' drop-down menu at the bottom with yes or no options. It is set to yes. If I try and change it to No it says "a primary key, by defintion, contains only unique values".

So how can I have these 2 fields as a primary key but still allow duplicates of each field? (as long as the combination isn't the same).

 

[palbano]

>> So how can I have these 2 fields as a primary key but
>> still allow duplicates of each field? (as long as the
>> combination isn't the same).

This is a database question not an ASP question. You need to remove those indexes and create a new one that uses both fields for the index definition. Go to the MS Access Forum to find out how to make those things happen in MS Access.

-pete