Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
paulc1976
Starting Member
4 Posts |
 Posted - 2009-04-20 : 16:07:27
|
| Hi everyone,I have made a simple e-commerce site for uni using asp.net, when the user views a single product they have the option to add a product to their own wishlist which is stored permanently in a table created for this purposeThe wishlist table contains 3 fields:Wishlist ID (Primary key)Product IDCustomer IDHowever for each Customer ID I only want to enter 1 instance of each product, so if the user tries to enter the same product into the wishlist more than once it will not get entered.Is there an INSERT statement that will let me do this?At the moment I'm using the auto generated INSERT statement from the query builder which is simplyINSERT (Customer ID, Product ID) INTO Wishlist tablewhich is ok but will insert multiple instances of the same product |
|
|
Skorch
Constraint Violating Yak Guru
300 Posts |
Posted - 2009-04-20 : 16:20:50
|
| You can use an IF statement for this.IF NOT EXISTS (SELECT 1 FROM Wishlist WHERE ProductID = @YourProductID AND CustomerID = @YourCustomerID)INSERT INTO Wishlist (ProductID, CustomerID) VALUES (@YourProductID, @YourCustomerID)(This is assuming your WishlistID is an identity column, which I strongly suggest it to be)Some days you're the dog, and some days you're the fire hydrant. |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-04-20 : 16:22:38
|
[code]IF NOT EXISTS (SELECT * FROM WishList WHERE CustomerID = @CustomerID AND ProductID = @ProductID) INSERT WishList (CustomerID, ProductID) VALUES (@CustomerID, @ProductID)[/code]
E 12°55'05.63"N 56°04'39.26" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-04-20 : 16:23:07
|
E 12°55'05.63"N 56°04'39.26" |
|
|
|
paulc1976
Starting Member
4 Posts |
Posted - 2009-04-20 : 17:41:27
|
| thank you both very much for your help, that was just the job! |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2009-04-20 : 22:32:14
|
| You would also benefit from a unique constraint on customerID & productID in case not everyone plays by the rules. Protect your data from all applications. |
|
|
|
aprichard
Yak Posting Veteran
62 Posts |
Posted - 2009-04-21 : 05:04:50
|
| You can restrict input data by applying UNIQUE constraint on CustomerID and ProductID together |
|
|
|
|
|
|
only want to insert one instance of a product