Making sure figures match in tables

[Farhan_B]

Hi

i was just wondering how i would do this task. It seems fairly simple but i dont no how to to do it.

In my system i have a table where i have a total figure of the stock. what i am trying to do is when my saler order takes 5 i want it to project this in my overal amount. How would i do this.

I am not asking for codes just a method of doing it

Thank you

[Reverend Jim]

What do you mean by

when my saler order takes 5

Also

project this in my overall amount

is unclear.

[Farhan_B]

for example

if i have 50 coke in my stock table

and i my sales order i place 25
in my stock table i am left with 25
Was This Post Helpful? 0

[Reverend Jim]

The SQL statement for the update is something like (depending on your field names)

update StockTable set num_units = num_units - 25 where item_name = 'Coke'

I wasn't trying to be difficult. I just don't want to mislead you by answering a question unless I am sure what it is :)

[Farhan_B]

Table Stock has the following feilds
[StockID]
[itemName]
[itemDescription]
[supplyName]
[supplierId]
[onHand]
[onOrder]
[price]

Table Sales Order has the following Fields
SalesID]
[CustomerID]
[itemName]
[Qty]
[unitPrice]
[totalprice]
[date]

Now what i am trying to do is. when a customer places a order and in my tblSalesorder i put 5 in the qty.

What i want the field in table stock to update so if i have 15qty once the order has been saved then in my table stock it shows that i have 10 remaining

how would i do this

[Reverend Jim]

When you have to modify several tables with data that has to be consistent (e.g. removing quantities from one and adding them to another) you need to use Transactions. Transactions allow you to bundle changes so that if any of the steps fails then all of the changes are undone. The steps are like

Begin Transaction

Try
    change table 1
    change table 2
    etc
    Commit Transaction
Check error
    Rollback Transaction
End Try

If you are using ADO (the method I am most familiar with) you do it like:

Dim con As New ADODB.Connection
con.Open("Driver={SQL Server};Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=yes;")

con.BeginTrans()

Try
    con.Execute("update admin set [password]='orange' where username='Eric'")
    con.Execute("update admin set [password]='staple' where username='Fred'")
    con.CommitTrans()
Catch ex As Exception
    con.RollbackTrans()
    MsgBox("rolled back")
End Try

con.Close()

If you want to try this, just create an error in the SQL statement (replace "update" with "updat" for example). The same functionality exists with OLEDB.

[Farhan_B]

so how wouldn't it be in my scenario as i am confused. I kinda of get what you mean by the rollback phase. but how would i update the tblstock (onhold) field when i insert a value in my tblsalesorder(Qty)

[Reverend Jim]

You have to build two query strings - one to insert the new record into the SalesOrder table and one to update the Stock table. The two queries look like

insert into SalesOrder (list of field names) values(list of field values)
update Stock set onHand = onHand - ## where itemName = @@@

and you have to modify ## and @@@ with the appropriate values. As I suggested earlier, you should make the the queries into a transaction. I also suggest you don't use "date" as a field name. It is a reserved word and should be replaced with something more specific such as orderDate, especially since you may at some point want to add another date field (shippingDate, for example).