CODEWITHSUNDEEP
c#asp.netsql
user527330
user527330

Reputation: 65

Using a variable from a Foreach Loop

I'd like to be able to check to see if an item with the same id has already been placed in the database, if so to then update the quantity for that item, however due to the fact I have this in a foreach loop it will update the quantity for each item.

When I placed the Command outside of the loop I am unable to use 'ItemID' as it's not in context, is there anyway I can get around this?

Thank You

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();                 
}

Upvotes: 1

Views: 394

Answers (4)

ABI
ABI

Reputation: 1754

I think if command (IF EXISTS ... ) not required.

Try this code:

    ConclusionPage.UpdateCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CurrentUser", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Input, 0, 0, "UserID", System.Data.DataRowVersion.Current, false, null, "", "", ""));
    ConclusionPage.UpdateCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ItemID", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Input, 0, 0, "ItemID", System.Data.DataRowVersion.Current, false, null, "", "", ""));
    ConclusionPage.CommandType = System.Data.CommandType.Text;
    ConclusionPage.UpdateCommand = "UPDATE tblUserItems SET Quantity = Quantity+1 WHERE (UserID = @CurrentUser) AND (ItemID = '@ItemID')";

    foreach (UserItem ItemID in (List<UserItem>)Session["UserSession"])
    {
        ConclusionPage.Parameters[0].Value = CurrentUser;
        ConclusionPage.Parameters[1].Value = ItemID;
        ConclusionPage.ExecuteNonQuery();
    }

Upvotes: 1

user527330
user527330

Reputation: 65

I'm not sure if this is the problem, when I've been moving between the main page and the conclusion page when I moved to the conclusion page the quantites updates themselves :S So maybe it could be a Lifecycle issue? I have this in Page_Load and I moved it to Page_init and still have the same issue

EDIT:

The amount of times I click on the 'Add' Button is the amount of number the quantity is increased by e.g. Click twice the quantity increments by two on each page load

Upvotes: 0

MrEyes
MrEyes

Reputation: 13750

Are you actually seeing the code update all quantities in your DB? In theory the IF EXISTS part should prevent that from happening.

Having said that I would recommend that you change the SQL command to something like this instead, the IF EXISTS check is superfluous as you can use WHERE on the UPDATE to handle the check.

UPDATE tblUserItems SET Quantity = Quantity + 1 WHERE (UserID = '@CurrentUser') AND  AND (ItemID = '@ItemID')

Having said that, if you have a lot of UserItems that is still a lot of work for the DB to do as it would could potentially trigger a lot of unnecessary calls to the database. Without knowing the ins and outs of your architecture it is impossible to say but it might be worth looking at an alternative.

Upvotes: 0

Marcelo Cantos
Marcelo Cantos

Reputation: 185978

The IF EXISTS is redundant. Since the UPDATE statement uses the same WHERE clause, it will only apply the update to records that match the same criteria.

Upvotes: 1

Related Questions