Updating the Quantity through SQL

I use the code found below to update a "Quantity" column in my table however I think because the Select and Update is inside of a foreach loop it is updating all of the products.

Furthermore, the products are also updated when the page is reloaded and the amount is increased based on how many times the user clicks on the add button. E.g. Click the add button twice the quantity increments by two each time.

I ideally need to be able to use the ItemID outside of the foreach loop but can't.

Any suggestion开发者_Go百科s?

Code:

foreach (UserItem ItemID in (List<UserItem>)Session["UserSession"])
{
    ConclusionPage.InsertCommand = "IF EXISTS (SELECT ItemID FROM tblUserItems WHERE UserID='@CurrentUser' AND ItemID='@ItemID')  UPDATE tblUserItems SET Quantity = Quantity+1 WHERE (UserID = '@CurrentUser') AND (ItemID = '@ItemID')";
    ConclusionPage.Insert();                 
}

You can probably use the IN() function in SQL.

This can take either a result set, or a comma delimited list.

UPDATE tblUserItems
SET Quantity = Quantity+1
WHERE UserID = @CurrentUser
AND ItemID IN(123,456,789,001)

The code inside the loop is actually independent on the foreach. What I mean by this is that we are looping on Session["UserSession"], but the SQL command does not do anything with the values in Session["UserSession"].

All you should need to do is comment out the foreach line and the code should execute fine.

Somewhere above the code you have posted, there will be parameter variables (probably) named something like CurentUserCommand, ItemIdCommand (their types will be SqlCommand or DBCommand) - this are what the SQL statement is using.