Question Creating a wishlist for use with ASP.NET Membership

[njsokalski]

I am working on a web application that uses ASP.NET Membership. My SQL Server Database therefore includes (along with all the others created when setting up ASP.NET Membership) a table named aspnet_Users which has the fields ApplicationId, UserId, UserName, LoweredUserName, MobileAlias, IsAnonymous, LastActivityDate. I also have a table named CatalogItems which has a primary key field named ID, which is of type Int. I want to allow my users to create wishlists. I created the following table to do this:

Create Table WishLists (UserId UniqueIdentifier Not Null Foreign Key References aspnet_Users(UserId) On Delete Cascade,
ItemId Int Not Null Foreign Key References CatalogItems(ID) On Delete Cascade,
Constraint PK_Id Primary Key (UserId,ItemId))

I am using an *.xsd dataset for both of these tables, and everything works fine for the CatalogItems table, and items get added to the WishLists table perfectly fine, but then I recieve the following error:

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

The correct data gets inserted into the WishLists table just like I would expect. I even tried redeclaring the WishLists table without any constraints, but I still recieve the error. What is the problem here, and what do I need to do to fix it? Any help would be appreciated. Thanks.

 

[Herman]

The error you get means that one or more row(s) of data violate the table rules you setup. Some rows might be missing a column, or entire rows might be missing. Run the import query in Management Studio to get more info.

 

[njsokalski]

The SQL used to create the tables involved are the following:

Create Table WishLists (UserId UniqueIdentifier Not Null Foreign Key References aspnet_Users(UserId) On Delete Cascade,
ItemId Int Not Null Foreign Key References CatalogItems(ID) On Delete Cascade,
Constraint PK_Id Primary Key (UserId,ItemId))

Create Table CatalogItems (ID Int Identity Not Null Primary Key,
Item varchar(MAX),
Size varchar(MAX),
Color varchar(MAX),
Price Smallmoney,
Category Tinyint,
Uses varchar(MAX),
Info varchar(MAX),
Catalog Bit,
Photogallery Bit,
Imagefile varchar(MAX),
Zoomimagefile varchar(MAX),
Thumbnailimagefile varchar(MAX))

and the aspnet_Users table is the one created by ASP.NET when setting up ASP.NET Membership. One thing I noticed just now (I don't know if it is causing the problem or not) is that the ID field in the CatalogItems table is both an Identity and Primary Key. I don't know a lot beyond the basics (and haven't used a lot of it in a while) for SQL, so I don't know if this would cause a problem (would it?) or not. I also haven't used many of the advanced database-related of Visual Studio 2010 (is the import query feature available in Visual Studio, or do I need to open Management Studio separately?) I know that the one row is not missing any columns, and both columns have the data (I have looked at the table manually to check). Thanks.

 

[Herman]

In that table you have 2 restrictions on column ID:
- It cannot be Null
- Every value must be unique

So my guess is you have one or more rows of data you are trying to import with an empty ID, or two or more rows with the same ID. Those rows are not getting imported, but the rest is. Column headers in the source data maybe?

 

[njsokalski]

OK, I found it. One of my stored procedure SELECT statements wasn't returning both fields for the WishLists table, so I just changed that stored procedure to select both fields and it fixed the problem. Thanks for your help!