专家坐堂-代码优化，外部SQL,提高复制，保存速度Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

-- 代码优化，外部SQL,提高复制，保存速度

Dim id As Integer \'销售单

id = DataTables("表A").SQLCompute("Max(flngSendOutID)")

If id = 0 Then

id = DataTables("CX").Compute("Max(flngSendOutID)")

End If

Dim Filter As String = "[flngSendOutID] > " & id

DataTables("CX").Load

Dim Cols1() As String = {"fdtmDate","flngSendOutID","fstrSendOutNO","fstrCustomerDesc","fcurAmount1","fblnCash"}

Dim Cols2() As String = {"日期","flngSendOutID","单号","客户","金额","现金"}

For Each dr1 As DataRow In DataTables("CX").SQLSelect(Filter)

Dim dr2 As DataRow = DataTables("表A").SQLFind("单号 = \'" & dr1("fstrSendOutNO") & "\' And flngSendOutID > 0")

If dr2 Is Nothing Then

dr2 = DataTables("表A").AddNew()

Else

If Vars("ts") = True Then

PopMessage("客户:" & dr1("fstrCustomerDesc") & "单号:" & dr1("fstrSendOutNO") & "已被修改,请知悉!", "提示", "PopIconEnum.Infomation", 5)

End If

dr2("客户") = dr1("fstrCustomerDesc")

dr2("金额") = dr1("fcurAmount1")

dr2("付款方式") = Nothing

dr2("flngSendOutID") = dr1("flngSendOutID")

dr2("核实") = 0

dr2("现金") = dr1("fblnCash")

dr2.Save()

End If

dr2("类型") = "销售"

For i As Integer = 0 To Cols1.Length -1

dr2(Cols2(i)) = dr1(Cols1(i))

Next \'销售单

Dim id1 As Integer \'销售退货单

id1 = DataTables("表A").SQLCompute("Max(flngUnSendOutID)")

If id1 = 0 Then

id1 = DataTables("TH").Compute("Max(flngUnSendOutID)")

End If

Dim Filter1 As String = "[flngUnSendOutID] > " & id1

DataTables("TH").Load

Dim Cols3() As String = {"fdtmDate","flngUnSendOutID","fstrUnSendOutNO","fstrCustomerDesc","fcurAmount1","fblnCash"}

Dim Cols4() As String = {"日期","flngUnSendOutID","单号","客户","金额","现金"}

For Each dr3 As DataRow In DataTables("TH").SQLSelect(Filter1)

Dim dr4 As DataRow = DataTables("表A").SQLFind("单号 = \'" & dr3("fstrUnSendOutNO") & "\' And flngUnSendOutID > 0")

If dr4 Is Nothing Then

dr4 = DataTables("表A").AddNew()

Else

If Vars("ts") = True Then

PopMessage("客户:" & dr3("fstrCustomerDesc") & "单号:" & dr3("fstrUnSendOutNO") & "已被修改,请知悉!","提示", "PopIconEnum.Infomation", 5)

End If

dr4("客户") = dr3("fstrCustomerDesc")

dr4("金额") = dr3("fcurAmount1")

dr4("付款方式") = Nothing

dr4("flngUnSendOutID") = dr3("flngUnSendOutID")

dr4("核实") = 0

dr4("现金") = dr3("fblnCash")

dr4.Save()

End If

dr4("类型") = "退货"

For n As Integer = 0 To Cols3.Length -1

dr4(Cols4(n)) = dr3(Cols3(n))

Next \'销售退货单\'

-----------------------------------------------------------------------------------------------

CX 和 TH 表是SQL语句生成的查询表

CX表

Select Top 1 flngSendOutID,fstrSendOutNO,fcurAmount1,fdtmDate,fblnCash,fstrCustomerDesc Fro m {SendOut} Inner JOIN {Customer} ON {Customer}.[fstrCustomerName] = {SendOut}.[fstrCustomerName] Order By fstrSendOutNO DESC

TH表

Select Top 1 flngUnSendOutID,fstrUnSendOutNO,fcurAmount1,fdtmDate,fblnCash,fstrCustomerDesc Fro m {UnSendOut} Inner JOIN {Customer} ON {Customer}.[fstrCustomerName] = {UnSendOut}.[fstrCustomerName] Order By fstrUnSendOutNO DESC