此代码解说转自gdlgh老师置顶贴,听老师一言,胜读十遍帮助...![dvubb 图片点击可在新窗口打开查看](images/emot/em57.gif)
先看看这行超级代码...是不是有点晕?..![dvubb 图片点击可在新窗口打开查看](images/emot/em51.gif)
s1 = "SELECT '0' AS 序号, 库名, (SELECT COUNT(*) FROM (SELECT DISTINCT 库名,货品编码 FROM CKKC,仓库信息 WHERE 仓库 = 库名) WHERE 库名 = M.库名) AS 商品种类, (SELECT COUNT(*) FROM CYMX WHERE 方向='入库' AND 仓库 = M.库名 @#$) AS 本期入库_笔数, Round((SELECT SUM(金额) FROM CYMX WHERE 方向='入库' AND 仓库 = M.库名 @#$),2) AS 本期入库_金额, (SELECT COUNT(*) FROM CYMX WHERE 方向='出库' AND 仓库 = M.库名 @#$) AS 本期出库_笔数, Round((SELECT SUM(金额) FROM CYMX WHERE 方向='出库' AND 仓库 = M.库名 @#$),2) AS 本期出库_金额, (SELECT COUNT(*) FROM CYMX WHERE 仓库 = M.库名 AND (类型='调拨入库' OR 类型='调拨出库') @#$) AS 本期调拨_笔数, (SELECT COUNT(*) FROM CYMX WHERE 仓库 = M.库名 AND 类型='调拨入库' @#$) AS 本期调拨_调入笔数, (SELECT COUNT(*) FROM CYMX WHERE 仓库 = M.库名 AND 类型='调拨出库' @#$) AS 本期调拨_调出笔数, Round((SELECT SUM(金额) FROM CYMX WHERE 仓库 = M.库名 AND (类型='调拨入库' OR 类型='调拨出库') @#$),2) AS 本期调拨_金额, (SELECT COUNT(*) FROM CYMX WHERE 仓库 = M.库名 AND (类型='盘盈' OR 类型='盘亏') @#$) AS 本期盘点_笔数, (SELECT COUNT(*) FROM CYMX WHERE 仓库 = M.库名 AND 类型='盘盈' @#$) AS 本期盘点_盘盈笔数, (SELECT COUNT(*) FROM CYMX WHERE 仓库 = M.库名 AND 类型='盘亏' @#$) AS 本期盘点_盘亏笔数, Round((SELECT SUM(金额) FROM CYMX WHERE 仓库 = M.库名 AND (类型='盘盈' OR 类型='盘亏') @#$),2) AS 本期盘点_金额, '' AS 备注 FROM 仓库信息 AS M"
@#$符号的解释:
之所以用@#$,系用来简化代码!
===找到TreeView4控件查看代码=====
'生产明细表
s 13 = "SELECT M.序号, M.日期, M.部门, M.货品编码, N.货品名称, N.规格型号, N.计量单位, VAL(M.入库数量) AS 数量, M.单价, M.金额, N.备注 FROM CYMX AS M LEFT JOIN 库存商品 AS N ON M.货品编码=N.货品编码 WHERE 类型='生产入库' @#$ ORDER BY 日期, 部门, M.货品编码" '这里有个'@#$'
略..........
select case value2 '判断选定节点内容,赋值全局变量
略.........
case "生产明细表" '把SQL字符串赋值给全局变量Vars("str2")
Vars("str2") = s13
case "部门领用汇总表"
Vars("str2") = s14
case "物品领用汇总表"
Vars("str2") = s15
case "领用明细表"
Vars("str2") = s16
end select
========报表查询窗口查询按钮代码=============
with e.form
dim sql,str1,str2,str3,kz as string
kz = "TextBox4,ComboBox4,ComboBox3,ComboBox5,ComboBox6,TextBox1,TextBox3,DateTimePicker1,DateTimePicker2" '查询条件的控件数组
str1 = "单号='@$0',类型= '@$1',仓库= '@$2',供应单位= '@$3',经办人= '@$4',货品编码 = '@$5',部门= '@$6',日期 >= #@$7#,日期 <= #@$8#" '对应查询条件的条件数组(@$为替换符,作用是可简化代码!)
dim i as integer
For Each k as string In kz.split(",") '分解控件数组,循环组合查询条件串sql
if .Controls(k).Value isnot nothing then '若条件控件内容为空,跳过,否则组合查询条件串
if i=0 and .Controls("CheckBox1").Checked then '判断使用模糊单号还是精确单号查询
sql = sql & " AND " & str1.split(",")(i).Replace("='@$" & i," like '%" & .Controls(k).Value & "%") '组合条件串,条件值替换@$i。(模糊查询)
else
sql = sql & " AND " & str1.split(",")(i).Replace("@$" & i,.Controls(k).Value)
'组合条件串,条件值替换@$i。(精确查询)
end if
end if
i += 1
Next
Dim cmd As New SQLCommand
cmd.C
Dim dst As WinForm.DataList = Forms("窗口1").Controls("DataList7")
cmd.CommandText = Vars("str2").Replace("@#$",sql) '把 Vars("str2")=s13 中的'@#$'替换为条件串sql,组成一个完整的SQL查询语句!!
msgbox(Vars("str2").Replace("@#$",sql)) '要查看完整的SQL查询语句串,加入这条代码就明白了!!!!!!
'生成序列号
dst.DataTable = cmd.ExecuteReader()
dst.Build()
For i=1 to dst.count
Dim dr as DataRow = dst.GetDataRow(i-1)
dr("序号") = "" & i
Next
end with
[此贴子已经被作者于2009-1-3 10:13:27编辑过]