Many-to-many SQL Query (Failed to enable constraints)

Hi,

I have two tables (Accounts and Contacts) that have a many-to-many relationship, maintained by the AccountContactLinks table.

I would like to populate a Contacts DropDownList with all of the Contacts associated with the Account Selected in Accounts DropDownList.

Here is the (SP) SQL Query I'm trying to make work:

SELECT Contact.ContactID,
Contact.ContactLastName,
Contact.ContactFirstName,
Contact.ContactLastName+', '+ Contact.ContactFirstNameAS ContactName
FROM ContactINNERJOIN AccountContactLinksON Contact.ContactID= AccountContactLinks.ContactID
WHERE(AccountContactLinks.AccountID= @.AccountID)
ORDERBY Contact.ContactLastName
END

I keep getting the following error:

Failed to enable constraints. One or more rows contains values violating non-null, unique or foreign key constraints.

I haven't implemented any non-null, unique or foreign key constraints between any of these tables, so suspect that I've got the SQL Query wrong.

Thanks very much.

Regards

Gary

You must be trying to read that into a dataset that is set up wrong. If you are just trying to fill a dropdown, then put a dropdown control on the page, put a sqldatasource control on the page, put the SQL you have there (Or the name of the SP) as the query string, and set up the @.AccountID parameter.|||

Thanks, Motley - what you suggest is exactly what I'm doing. I've tried it both a SQL query (which runs fine in the Query Editor) and as an SP.

It's just when I test the Query in the SqlDataSource Wizard and when I try to run the page that it doesn't work.

Do you have any other ideas?

Thanks very much.

Regards

Gary

|||

It looks good to me... Well, except that you have an END statement, but I'm assuming that you cut and pasted from your SP which had a BEGIN before the select statement.

Are you sure there are no foreign key constraints? Sure looks like the AccountContactsLinks and Contacts table would be a good candidate for one.

|||

Thanks, Motley - I've found out what's wrong (by creating a new set of tables to ensure a clean start and making the same mistake, inadvertently).

The mistake that I made (twice) was to create identical records, which in a table without a primary key, introduces duplicates (i.e. creates non-unique records). I thought that I would have had to specify that records had to be unique, but now realise that, if the linking table doesn't have a key field, there is no way of identifying one record from another if the two fields are the same.

I'm concerned that SQL Server allowed this to happen and unless you or someone else has ways of preventing it from happening again, I'm going to re-introduce a Primary Key field (which someone else suggested I remove) as a safeguard.

So, I'll have to rebuild my linking table. Fortunately I've been playing around with a test database, so it won't take too long. I'll then conclude this thread with a confirmation, for the sake of people with the same problem in future.

Regards

Gary

|||I'm glad you found it. I always recommend that all tables have a primary key -- even if it's one that you create yourself via a uniqueidentifier or an identity field. In some rare cases, like in your link table, where there really is no "new" information, but just linking 2 different objects IDs to show a relationship, I'll put both fields into the primary key.