Member Avatar for ariel.cepeda.35

ys!

I'm developing a software for a furniture company for keeping furniture cost to date.I'm Saving cost with the quantity and unit price for each material and keep them up to date by updating the material's unit price.For example:
A chair need 3 Gallons of Thinner at $5,but today the price went up and it now cost $6.50.So when I update the unit price of the Thinner now the cost comes up $4.50 more.I have a Products table and a ProductMaterialCost table.What I want to do is update the Materials cost calculation and then update de Products table so the totalcost is now $4.50 more.How do I do this?Can I update the sum directly from database?

  • 4 Contributors
  • 4 Replies
  • 292 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Eternal Newbie
Member Avatar for Reverend Jim

You shouldn't be storing the sum in the database (or any field that depends upon a calculation for its value). The sum can be calculated on the fly in the query. Having said that, you can update a database field via an UPDATE query. If you show us the database fields (it would help if you gave a before/after example of a typical record) then we can show you the appropriate query.

Member Avatar for acepeda

I forgot to say that I dont want only to update a product but all the product that includes the material im updating.

Member Avatar for acepeda

I have updated the fields by searching the materialsdetails table in my database and displaying all the products where the material searched is used in a datagrid and by pressing the update button it changes its subtotal in the materialsdetails table,but i want to update the TotalCost Field in the Products Table.How can I update this?

Edited by acepeda
Member Avatar for Eternal Newbie

I'm not an expert in SQL but I'll give it a try:
1. Creating a procedure to update 'materialsdetails' table, but update both 'materialsdetails' and 'Products' together. That proc might contain query such as (my suggestion only):

UPDATE Products SET TotalCost = 'OldTotalCostValue' + 'NewMaterialsValue' - 'NewMaterialsValue'
WHERE ProductsId IN (SELECT * FROM Material WHERE MaterialId ='EditMaterialsId')
  1. Excute the new procedure with your application instead of your update LINQ query. Done!
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.