以下是引用liuruihua在2010-8-15 14:42:00的发言:
一楼的代码在“物品入库”和“物品出库”两表都完全加载的情况下,则一楼的代码能得到正确的库存值,即dr("库存") = Val1 - Val2 能得到正确的结果,但,如果不加载,则库存值不正确。现在是想在两表数据不加载的情况下,每出库一物品,即能得到其正确的库存量。
你的要求好象不太现实,如果不加载数据就意味着从后台直接调取数据来计算出每个产品的库存量,但如果从后台直接调取数据,当进行出库输入出库数量时,此时后台数据并未生成,怎么来调取数据?难不成每输入一个出库数量就保存一次?
下面的代码只做为参考.
出库表DataColChanged事件代码:
Dim r As DataRow = e.DataRow
If e.DataCol.name = "出库数量" Then
If r.isnull("日期") = False And r.isnull("物品名称") = False Then
DataTables("物品出库").Save()
Dim Filter As String
Dim Val As Double
For Each dr As DataRow In DataTables("物品出库").DataRows
Filter = "物品名称 = '" & dr("物品名称") & "' And [_SortKey] <= " & dr("_SortKey")
Dim cmd As New SQLCommand
cmd.ConnectionName = "wp"
cmd.CommandText = "SELECT SUM(入库数量) FROM {物品入库} WHERE [物品名称]= '" & dr("物品名称") & "'"
Val = cmd.ExecuteScalar()
cmd.CommandText = "SELECT SUM(出库数量) FROM {物品出库} WHERE " & Filter
dr("库存") = Val - cmd.ExecuteScalar()
Next
End If
End If
加载出库表代码:
DataTables("物品出库").LoadFilter = "[日期] > #2010-07-01#"
DataTables("物品出库").Load
For Each dr As DataRow In DataTables("物品出库").DataRows
Dim Filter As String = "物品名称 = '" & dr("物品名称") & "' And [_SortKey] <= " & dr("_SortKey")
Dim cmd As New SQLCommand
cmd.ConnectionName = "wp"
cmd.CommandText = "SELECT SUM(入库数量) FROM {物品入库} WHERE [物品名称]= '" & dr("物品名称") & "'"
Dim Val As Double = cmd.ExecuteScalar()
cmd.CommandText = "SELECT SUM(出库数量) FROM {物品出库} WHERE " & Filter
dr("库存") = Val - cmd.ExecuteScalar()
Next
[此贴子已经被作者于2010-8-17 1:42:46编辑过]