CODEWITHSUNDEEP
c#asp.netsqllinq
thatuxguy
thatuxguy

Reputation: 2528

Query not updating database

Morning,

I have an issue with some of my code... Basically i am trying to update or insert into the database. the first if statement if for when adding a new product. the else should then update any existing products.

However when i run it, it is not updating the existing products in the database. It is however setting the items ready to be updated. Any ideas?

Many thanks...

using (aboDataDataContext dc = new aboDataDataContext())
            {
                foreach (abcProduct p in abcProducts)
                {

                    var match = (from t in dc.abcProducts
                                 where t.sku == p.productcode
                                 select t).FirstOrDefault();

                    if (match == null)
                    {

                        // Watch out here; there is some type conversion required for certain fields!
                        abcProduct prod = new abcProduct();

                        prod.sku = p.productcode;
                        prod.categoryId = dc.Categories.Single(c => c.Name == p.category).Id;
                        prod.title = p.name;
                        prod.brand = p.manufacturer;
                        prod.description = p.description;
                        prod.abcPrice = p.price;
                        prod.size = decimal.TryParse(p.size.Replace("cl", ""), out size) == true ? (int?)size : null;
                        prod.country = p.country;
                        prod.region = p.region;
                        prod.vintage = int.TryParse(p.vintage, out vintage) == true ? (int?)vintage : null;
                        prod.weight = Convert.ToDecimal("1.50");
                        prod.strength = p.strength;
                        prod.bottler = p.bottler;
                        prod.age = int.TryParse(p.age, out age) == true ? (int?)age : null;
                        prod.caskType = p.casktype;
                        prod.series = p.series;
                        prod.flavour = p.flavour;
                        if (p.freestock <= 0) { prod.stock = 0; } //check to see if stock is 0
                            else { prod.stock = p.freestock; }
                        prod.abcUpdated = false;
                        prod.stockUpdated = false;
                        prod.priceUpdated = false;
                        prod.pricePublished = false;
                        prod.stockPublished = false;
                        prod.imgPublished = false;
                        prod.prodPublished = false;
                        prod.lastUpdated = DateTime.Now;

                        // Add the new object to the abcProducts table (only in memory here)
                        dc.abcProducts.InsertOnSubmit(prod);
                    }
                    else
                    {
                        // update row
                        match.abcUpdated = true;
                        //Set if an item has been updated or not.
                        if (match.stock == p.freestock) { match.stockUpdated = false; }
                        else { match.stockUpdated = true; }
                        if (match.abcPrice == p.price) { match.priceUpdated = false; }
                        else { match.priceUpdated = true;}
                        match.sku = p.productcode;
                        match.categoryId = dc.Categories.Single(c => c.Name == p.category).Id;
                        match.title = p.name;
                        match.brand = p.manufacturer;
                        match.description = p.description;
                        match.stock = p.freestock;
                        match.abcPrice = p.price;
                        match.size = decimal.TryParse(p.size.Replace("cl", ""), out size) == true ? (int?)size : null;
                        match.weight = Convert.ToDecimal("1.50");
                        match.country = p.country;
                        match.region = p.region;
                        match.vintage = int.TryParse(p.vintage, out vintage) == true ? (int?)vintage : null;
                        match.strength = p.strength;
                        match.bottler = p.bottler;
                        match.age = int.TryParse(p.age, out age) == true ? (int?)age : null;
                        match.caskType = p.casktype;
                        match.series = p.series;
                        match.flavour = p.flavour;
                        if (p.freestock <= 0) { match.stock = 0; } //check to see if stock is 0
                            else { match.stock = p.freestock; }
                        match.abcUpdated = true;
                        match.pricePublished = false;
                        match.stockPublished = false;
                        match.imgPublished = false;
                        match.prodPublished = false;
                        match.lastUpdated = DateTime.Now;
                    }
                }

                // Finally, request Linq to perform the database updates.
                dc.SubmitChanges();
            }
            return null;
        }

Upvotes: 1

Views: 137

Answers (2)

marvc1
marvc1

Reputation: 3699

The context is loosing track of the object when setting match.

At the bottom of the else statement insert

dc.abcProducts.Attach(match);

Upvotes: 4

Habib
Habib

Reputation: 223257

There is problem in your code, you are iterating through the product table and getting the values in the match variable. In the part of the if statement where match is not null, you are setting the object to the new values, but you are not calling the dc.SubmitChanges();, that object match is not getting stored any where in your code, and in the next iteration in the loop, it is being assigned the new values.

You need to call dc.SubmitChanges(); after update the match values. 

foreach (abcProduct p in abcProducts)
                {

                    var match = (from t in dc.abcProducts
                                 where t.sku == p.productcode
                                 select t).FirstOrDefault();

                    if (match == null)
                    {
                       //insertion code commented out
                        dc.abcProducts.InsertOnSubmit(prod);
                    }
                    else
                    {
                        ///.... setting up all fields. 
                        match.stockPublished = false;
                        match.imgPublished = false;
                        match.prodPublished = false;
                        match.lastUpdated = DateTime.Now;
                        dc.SubmitChanges(); //Call this otherwise you will
                                            //loose the match values in the next iteration
                    }
                }

Upvotes: 3

Related Questions