Office中国论坛/Access中国论坛

标题: 报警问题讨论 [打印本页]

作者: fcj 时间: 2003-7-24 06:12
标题: 报警问题讨论
我想对一个入库表和出库表进行汇总然后取差值，然后和报警表进行比较，如果小于一个特定的数值，计算机显示报警，但是不知道为什么要是出错，我首先对出库表汇总，把产品名称字段和相应的数据汇总总量放到数组里面a(i)和b(i),然后我根据a(i)数组的产品名称字段对入库表按名称进行分类汇总，老是出错，odbc api 执行错误。不知道什么原因，希望高手们给于赐教。
还有一个问题，我要对产品进行分类汇总，要显示一些字段，但是我只要对数量进行汇总，其他的不汇总，只是显示，在SQL语句里用了一个with rollup命令，结果在用Ado控件的数据源 data resource运行的时候老是出问题，希望给于赐教。

sgoodsininfo 为入库表，sgoodsoutinfo 为出库表，alarminfo为报警表，goodsname为产品名称，goodsamount为产品数量 Dim Insum() As Single
   Dim Outsum() As Single
Dim Alarmsum() As Single
Dim Tname() As String
Dim Tmpstmt As String

Dim i, j, sum As Integer
Tmpstmt = "select sgoodsoutinfo.goodsname,sum(sgoodsininfo.goodsamount)," _
& "sum(sgoodsoutinfo.goodsamount) from sgoodsininfo,sgoodsoutinfo where " _
& "sgoodsininfo.goodsname=sgoodsoutinfo.goodsname" _
& " group by sgoodsoutinfo.goodsname"
DB_Connect ‘连接数据库
OdbcExt (Tmpstmt) ’执行语句tmpstmt
i = 0
Do While SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND  ‘获取结果集中1，2，3，列的数据
      ColVal = String(400, 0)
      Rc = SQLGetData(Hstmt, 1, 1, ColVal, Lench(ColVal), pcblen)
      ReDim Preserve Tname(i + 1)
      Tname(i) = TrimStr(ColVal)

      ColVal = String(400, 0)
      Rc = SQLGetData(Hstmt, 2, 1, ColVal, Lench(ColVal), pcblen)
      ReDim Preserve Insum(i + 1)
      Insum(i) = TrimStr(ColVal)

      ColVal = String(400, 0)
      Rc = SQLGetData(Hstmt, 3, 1, ColVal, Lench(ColVal), pcblen)
      ReDim Preserve Outsum(i + 1)
      Outsum(i) = TrimStr(ColVal)
   i = i + 1
   MsgBox i & "--" & Tname(i) & "--" & Str(Insum(i)) & "--" & Str(Outsum(i))‘测试结果
Loop
Rc = SQLFreeStmt(Hstmt, SQL_DROP)
DB_Disconnect
可是我用MsgBox i & "--" & Tname(i) & "--" & Str(Insum(i)) & "--" & Str(Outsum(i))来测试发现
结果是
1----0--0
2----0--0
3----0--0 也就是说数组里没有数据同时tname(i)为空
以下为报警比较信息
sum = UBound(Insum, 1)

Rc = SQLFreeStmt(Hstmt, SQL_DROP)
DB_Disconnect
DB_Connect
For i = 0 To sum - 1
      Tmpstmt = "select sum(goodsamount) from sgoodsininfo where goodsname='" & Tname(i) & "'"
      OdbcExt (Tmpstmt)
      If SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND Then
         ColVal = String(400, 0)
         Rc = SQLGetData(Hstmt, 1, 1, ColVal, Lench(ColVal), pcblen)
         ReDim Preserve Alarmsum(i + 1)
         Alarmsum(i) = ColVal
      End If

Next
Rc = SQLFreeStmt(Hstmt, SQL_DROP)
DB_Disconnect
i = 0
If DataListAlarm.Text <> "" Then
      If Tname(i) = DataListAlarm.Text Then
         If (Insum(i) - Outsum(i)) <= Alarmsum(i) Then
            DataListAlarm.SelectedItem = Tname(i)
            TxtSum.Text = Str(Insum(i) - Outsum(i))
            For j = 1 To 10
                  Beep
            Next
         End If
      Else
         i = i + 1
      End If
End If

欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)