1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

stock control

Discussion in 'C#' started by salim, Jul 17, 2006.

  1. #1
    I have it all set up for my satock control, I have the primary keys and foreign keys too, so in SQL how is it to update my stock control when I sell item so I could have products deleted from stock and inserted to sold_product tables please.


    SEMrush

    tbl_Stock ( stockID Primary Key)
    tbl_product (productID PK stockID Foreign key)
    tblProduct_sold ( a result of m:m relationship between tbl_sales and tbl_Product)
    tbl_sales (saleID PK)
     
    salim, Jul 17, 2006 IP
    SEMrush
  2. DanInManchester

    DanInManchester Active Member

    Messages:
    116
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    53
    #2
    You need to use a transaction to ensure the successful completion of both tasks. Below is an example that assumes you have a connection object Cn already set up. You don't detail your full data structure but here is an example

    on error resume next
    cn.BeginTrans
    cn.Execute("UPDATE tbl_Stock SET StockLevel = StockLevel - 1 WHERE ProductId = 123")
    cn.Execute("INSERT INTO tblProduct_sold (SaleId,ProductId) VALUES (321,123) ")

    if cn.Errors.Count = 0 then
    cn.CommitTrans
    else
    cn.RollbackTrans
    end if


    this code ensures that only if both the stock level decrement and sales order insert are sucecssful do any changes occur.
     
    DanInManchester, Jul 17, 2006 IP
  3. jimrthy

    jimrthy Guest

    Messages:
    283
    Likes Received:
    13
    Best Answers:
    0
    Trophy Points:
    0
    #3
    I agree with Dan that that's the basic idea of what you want to do. But, in general, unless it's a small, unimportant site, you should probably put those commands into a stored procedure.

    In principle, it's generally a bad idea to run arbitrary SQL from code. (Running SQL that contains any user input is a worse idea, but that's probably a different thread).
     
    jimrthy, Jul 17, 2006 IP
  4. DanInManchester

    DanInManchester Active Member

    Messages:
    116
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    53
    #4
    Assuming it is SQL Server then a stored proc may have some advantages however parameterised queries would be as safe but you would have more round trips unless you wrote it as one command text string in which case the difference is negligable.

    You should certainly use transactions to ensure the process is all or nothing regardless of whether you use a stored procedure or SQL.
     
    DanInManchester, Jul 17, 2006 IP
  5. salim

    salim Peon

    Messages:
    52
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    Okay, I got a gist of it, but
    cn.Execute("UPDATE tbl_Stock SET StockLevel = StockLevel - 1 WHERE ProductId = 123")
    cn.Execute("INSERT INTO tblProduct_sold (SaleId,ProductId) VALUES (321,123) ")
    do I have to insert all the productID ? in the script? you have also 321, second line, I assume it's saleID, then do I have to insert them all ?
    and again, is there any need for SQL in this case?
    I am a bit..if not new to asp, could you please elaborate more when you say about connection..ect, I would like to know where I could fit the script.
    thanks
     
    salim, Aug 1, 2006 IP
  6. salim

    salim Peon

    Messages:
    52
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #6
    Okay, I got a gist of it, but
    cn.Execute("UPDATE tbl_Stock SET StockLevel = StockLevel - 1 WHERE ProductId = 123")
    cn.Execute("INSERT INTO tblProduct_sold (SaleId,ProductId) VALUES (321,123) ")
    do I have to insert all the productID ? in the script? you have also 321, second line, I assume it's saleID, then do I have to insert them all ?
    and again, is there any need for SQL in this case?
    I am a bit..if not new to asp, could you please elaborate more when you say about connection..ect, I would like to know where I could fit the script.
    thanks
     
    salim, Aug 1, 2006 IP
  7. salim

    salim Peon

    Messages:
    52
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #7
    Okay, I got a gist of it, but
    cn.Execute("UPDATE tbl_Stock SET StockLevel = StockLevel - 1 WHERE ProductId = 123")
    cn.Execute("INSERT INTO tblProduct_sold (SaleId,ProductId) VALUES (321,123) ")
    do I have to insert all the productID ? in the script? you have also 321, second line, I assume it's saleID, then do I have to insert them all ?
    and again, is there any need for SQL in this case?
    I am a bit..if not new to asp, could you please elaborate more when you say about connection..ect, I would like to know where I could fit the script.
    thanks
     
    salim, Aug 1, 2006 IP
  8. DanInManchester

    DanInManchester Active Member

    Messages:
    116
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    53
    #8
    What the script is doing is deducting 1 from your inventory which is just a numeric field that keeps track of how many you have in stock then adding a sale transaction to your product sold table.

    Product = 123 is a made up product ID and 321 is a made up sale ID.
     
    DanInManchester, Aug 1, 2006 IP