DataTable that saves data to multiple tables in datbase RRS feed

  • Question

  • I have two tables in my SQL database. OrderDetails and StockTransactions


    OrderDetails contains:


    Item, Description, QtyOrdered


    StockTransactions contains:


    OrderDetailID, ActionType, Qty, Date


    I would like to create a table to enter data as follows:



    Item, Description, QtyOrdered, InStock, ToShip, TotalShipped, Backordered



    the first three columns come directly from the OrderDetails table, InStock is computed by finding the sum of Qty of all of the lines in the StockTransactions table for this item. ToShip is a field for the user to enter a value they'd like to ship currently. TotalShipped is the sum of what is in the ToShip box and all previous entries in the StockTransactions table related to the current OrderDetail record. Backordered is computed by taking ToShip and subtracting InStock (if the value is a negative number, then display zero).


    The objective is to create a user friendly way to enter lines into the OrderDetailsTable. the values place by the user in the Qty field for the items listed in front of them should be saved as entries in the StockTransactions table with ActionType = 1.


    I'm not sure if creating a view in the database is the best way to go about this or if there is an easier way


    Any comments or suggestions?

    Saturday, December 6, 2008 8:43 PM