专家坐堂-[求助]移动访问代码求助 9楼更新问题Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.com/bbs/index.asp)
--  专家坐堂  (http://foxtable.com/bbs/list.asp?boardid=2)
----  [求助]移动访问代码求助 9楼更新问题  (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=120226)

--  作者：huangfanzi
--  发布时间：2018/6/10 14:07:00
--  [求助]移动访问代码求助 9楼更新问题

此处为电脑访问的代码：
Tables(e.Form.Name & "_Table1").Fill("S elect Distinct 执行状态,合同类型,简称 As 客户,合同主号,合同,日期,内定交货日,名称,牌号,规格_厚度 As 厚度,规格_宽度 As 宽度,规格_长度 As 长度,单价,硬度_状态 As Hv状态,硬度_MIN As HvMin,硬度_MAX As HvMax,单位,数量 As 合同量,0.00 As 发货量,0.00 As 退货量,0.00 As 欠交量 F rom {销售及加工合同} Where " & flt & " ORDER BY 执行状态,合同类型,简称,合同主号,合同","ShcsErpSql",True)

For Each r As Row In Tables(e.Form.Name & "_table1").Rows
r("发货量") = Val(_dt工序合并表.SQLCompute("Sum(数量)","合同 = \'" & r("合同") & "\'And 表名 = \'销售及加工出库\'"))
r("退货量") = Val(_dt工序合并表.SQLCompute("Sum(数量)","合同 = \'" & r("合同") & "\'And 工序 = \'退货入库\'"))
r("欠交量") = r("合同量") - r("发货量") + r("退货量")
Next

下面是移动访问的代码，不知如何实现发货量、退货量及欠交量的计算，请老师帮我写下代码，我好参考，谢谢！
Dim e As RequestEventArgs = args(0)
Dim page As Integer = 0 \'默认page为0,显示第一页
Dim pageRows As Integer = 20 \'每页20行
If e.GetValues.ContainsKey("page") Then \'如果地址中有page参数
Integer.TryParse(e.GetValues("page"), page) \'提取page参数
End If
Dim StartRow As Integer = page * pageRows \'此页第一行
Dim EndRow As Integer = (page + 1) * pageRows - 1 \'此页最后一行
Dim cmd As New SQLCommand
cmd.C \'记得设置数据源名称
cmd.CommandText = "S elect Count(*) F rom {销售及加工合同}"
Dim Count As Integer = cmd.ExecuteScalar() \'获取总的行数
cmd.CommandText = "S elect RowNum,* ,0.00 As 发货量,0.00 As 退货量,0.00 As 欠交量 From (Select Row_Number() Over(Order by [合同]) As RowNum, * F rom 销售及加工合同 Where 执行状态 = \'执行中\') As a "
cmd.CommandText = cmd.CommandText & " Where RowNum >= " & StartRow & " And RowNum <= " & EndRow
Dim dt As DataTable = cmd.ExecuteReader \'获取该页数据
Dim sb As New StringBuilder
Dim nms() As String = {"客户","合同量","发货量","退货量","欠交量"}
sb.AppendLine("<Table border=\'1\'>")
sb.AppendLine("<caption>合同浏览<caption></br>")
sb.Append("<tr>")
For Each nm As String In nms
sb.Append("<td>" & nm & "</td>")
Next
sb.AppendLine("</tr>")
For Each r As DataRow In dt.DataRows
sb.Append("<tr>")
For Each nm As String In nms
sb.Append("<td>" & r(nm) & "</td>")
Next
sb.AppendLine("</tr>")
Next
sb.AppendLine("</Table></br></br>")
If page > 0 Then
sb.Append("<a href=\'htcx.htm?page=" & page - 1 & "\'>上一页</a> ")
End If
If EndRow < Count -1 Then
sb.Append("<a href=\'htcx.htm?page=" & page + 1 & "\'>下一页 </a>" )
End If
sb.Append("<a href=\'default.htm\'>返回 </a>" )
e.WriteString(sb.ToString)

[此贴子已经被作者于2018/6/11 23:11:56编辑过]

--  作者：有点甜
--  发布时间：2018/6/10 22:31:00
--

1、这句代码得到dt临时表吧？

Dim dt As DataTable = cmd.ExecuteReader \'获取该页数据

2、循环临时表，计算值，即可。

For Each dt As DataRow In dt.DataRows

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

或者换一种方法：直接写sql语句，如

select 第一列, 第二列 from {表A}

改成

select 第一列, 第二列, (select sum(数量) from {表B} as b where a.第一列=b.第一列) as 总数量 from {表a} as a

--  作者：huangfanzi
--  发布时间：2018/6/11 14:10:00
--
脑子昨天没反应过来，光想着如何在生成的网页表格上进行循环，不知如何写代码了，其实是先循环临时表
第二种方法是用子查询，但效率是不是比第一种差啊？

--  作者：有点甜
--  发布时间：2018/6/11 14:14:00
--

如果效率问题，你 SQLCompute 的效率更低，如果执行多次请尽量使用Compute。

--  作者：huangfanzi
--  发布时间：2018/6/11 21:55:00
--
s elect 合同,合同量, 发货量, (select sum(数量) f rom {表B} as b where a.合同=b.合同) as 退货量, 合同量 - 发货量 + 退货量 = 欠交量 f rom {表a} as a

老师，用子查询确实比原来的SQLCompute快，但上面的欠交量能直接计算出来吗？

--  作者：有点蓝
--  发布时间：2018/6/11 22:01:00
--
select *,合同量 - 发货量 + 退货量 as 欠交量 from (select 合同,合同量, 发货量, (select sum(数量) f rom {表B} as b where a.合同=b.合同) as 退货量  from {表a} as a) as c

--  作者：huangfanzi
--  发布时间：2018/6/11 22:25:00
--
太复杂了，改不来，老师帮我改下，下面是我目前用的代码，欠交量是另外算的，帮我按您上面方法改下，谢谢！

Tables(e.Form.Name & "_Table1").Fill("Select Distinct a.执行状态,a.合同类型,a.简称 As 客户,a.合同主号,a.合同,a.日期,a.内定交货日,a.名称,a.牌号,a.规格_厚度 As 厚度,a.规格_宽度 As 宽度,a.规格_长度 As 长度,a.单价,a.硬度_状态 As Hv状态,a.硬度_MIN As HvMin,a.硬度_MAX As HvMax,a.单位,a.数量 As 合同量, (select sum(数量) f rom {工序合并表} as b where a.合同 = b.合同 And b.表名 = \'销售及加工出库\') as 发货量 ,(select sum(数量) f rom {工序合并表} as b where a.合同 = b.合同 And b.工序 = \'退货入库\') as 退货量,0.00 As 欠交量 F rom {销售及加工合同} As a Where " & flt & " ORDER BY a.执行状态,a.合同类型,a.简称,a.合同主号,a.合同","ShcsErpSql",True)

For Each r As Row In Tables(e.Form.Name & "_table1").Rows
r("欠交量") = r("合同量") - r("发货量") + r("退货量")
Next

--  作者：有点蓝
--  发布时间：2018/6/11 22:50:00
--

Tables(e.Form.Name & "_Table1").Fill("select *,合同量 - 发货量 + 退货量 as 欠交量 from (Select Distinct a.执行状态,a.合同类型,a.简称 As 客户,a.合同主号,a.合同,a.日期,a.内定交货日,a.名称,a.牌号,a.规格_厚度 As 厚度,a.规格_宽度 As 宽度,a.规格_长度 As 长度,a.单价,a.硬度_状态 As Hv状态,a.硬度_MIN As HvMin,a.硬度_MAX As HvMax,a.单位,a.数量 As 合同量, (select sum(数量) f rom {工序合并表} as b where a.合同 = b.合同 And b.表名 = \'销售及加工出库\') as 发货量 ,(select sum(数量) from {工序合并表} as b where a.合同 = b.合同 And b.工序 = \'退货入库\') as 退货量 F rom {销售及加工合同} As a) as a Where " & flt & " ORDER BY a.执行状态,a.合同类型,a.简称,a.合同主号,a.合同","ShcsErpSql",True)

--  作者：huangfanzi
--  发布时间：2018/6/11 23:11:00
--
进行了测试，应该是下面的代码：
Tables(e.Form.Name & "_Table1").Fill("Select *,合同量 - IsNull(发货量,0) + IsNull(退货量,0) as 欠交量 f rom (Select Distinct a.执行状态,a.合同类型,a.简称 As 客户,a.合同主号,a.合同,a.日期,a.内定交货日,a.名称,a.牌号,a.规格_厚度 As 厚度,a.规格_宽度 As 宽度,a.规格_长度 As 长度,a.单价,a.硬度_状态 As Hv状态,a.硬度_MIN As HvMin,a.硬度_MAX As HvMax,a.单位,a.数量 As 合同量, (select sum(数量) f rom {工序合并表} as b where a.合同 = b.合同 And b.表名 = \'销售及加工出库\') as 发货量 ,(select sum(数量) f rom {工序合并表} as b where a.合同 = b.合同 And b.工序 = \'退货入库\') as 退货量 F rom {销售及加工合同} As a ) As c Where " & flt & " ORDER BY 执行状态,合同类型,客户,合同主号,合同","ShcsErpSql",True)

现在又有个新问题，代码中欠交量的计算不精确，例如应该是-0.3，通过后续的SetFormat代码在查询表上看到的是-0.30，把数据复制到EXCEL时看到的是-0.300000000000011，如何解决这个问题？

--  作者：有点甜
--  发布时间：2018/6/12 8:59:00
--

合同量 - IsNull(发货量,0) + IsNull(退货量,0) as 欠交量

改成

round(合同量 - IsNull(发货量,0) + IsNull(退货量,0), 2) as 欠交量