I am building up an ecommerce website. I have already build the shopping cart, populate products, etc even the admin part where the admin can add delete update products etc. Now what is left is to tackle the stock level.

In the product table I have made a column with quantity. Now what I want is that when a customers add the product to the shopping cart the quantity is decrease by 1. If the quantity is 1 or less than the quantity is set to 0. At first I though of making it that when quantity ends up 0 the system will delete the product but since the customer can delete the product from the shopping cart, I ll have a problem to re add it again. Thats why I was going to set is as 0. Am I right about this one or not??

Another problem is where I am going to implement it.

This is the algorithm that I think I need. Correct me if wrong !!

ie. if quantity 1

set quantity = quantity – 1

else

if quantity = 1

set quantity = 0

The Question is can I implement this algorithm in the stored procedure used to add an item in a shopping cart. (see code below). If not where do I have to implement it. I really do appreciate if you write some example since I have tried to do it unsuccessfully.

your help is really appreciated.

thanks in advance

// the stored procedure to add a new item in the shopping cart

1 ALTER Procedure ShoppingCartAddItem
2 (@CartID char(36),
3 @ProductID int)
4 AS
5 IF EXISTS
6 (SELECT CartID
7 FROM ShoppingCart
8 WHERE ProductID = @ProductID AND CartID = @CartID)
9 UPDATE ShoppingCart
10 SET Quantity = Quantity + 1
11 WHERE ProductID = @ProductID AND CartID = @CartID
12
13 SELECT Quantity FROM Product
14 UPDATE
15 SET Quantity = Quantity + 1
16 WHERE ProductID = @ProductID AND CartID = @CartID
17 ELSE
18 IF EXISTS
19 (SELECT ProductName FROM Product WHERE ProductID=@ProductID)
20 INSERT INTO ShoppingCart (CartID, ProductID, Quantity, DateAdded)
21 VALUES (@CartID, @ProductID, 1, GETDATE())

//CODE OF THE ADD BUTTON WHEN CLIKED

1 // Add the product to cart
2 protected void addToCartButton_Click(object sender, EventArgs e)
3 {
4 // Retrieve ProductID from the query string
5 string productId = Request.QueryString[ProductID];
6 // Add the product to the shopping cart
7 ShoppingCartAccess.AddItem(productId);
8
9
10
11 }

// THE CODE TO ADD THE ITEM TO A SHOPPING CART

1 // Add a new shopping cart item
2 public static bool AddItem(string productId)
3 {
4 // get a configured DbCommand object
5 DbCommand comm = GenericDataAccess.CreateCommand();
6 // set the stored procedure name
7 comm.CommandText = ShoppingCartAddItem;
8 // create a new parameter
9 DbParameter param = comm.CreateParameter();
10 param.ParameterName = @CartID;
11 param.Value = shoppingCartId;
12 param.DbType = DbType.String;
13 param.Size = 36;
14 comm.Parameters.Add(param);
15 // create a new parameter
16 param = comm.CreateParameter();
17 param.ParameterName = @ProductID;
18 param.Value = productId;
19 param.DbType = DbType.Int32;
20 comm.Parameters.Add(param);
21 // returns true in case of success or false in case of an error
22 try
23 {
24 // execute the stored procedure and return true if it executes
25 // successfully, or false otherwise
26 return (GenericDataAccess.ExecuteNonQuery(comm) != -1);
27 }
28 catch
29 {
30 // prevent the exception from propagating, but return false to
31 // signal the error
32 return false;
33 }
34 }