WINCC V7.0多条件查询SQL2005语句-已测试

Dim sPro
Dim sDsn
Dim sSer
Dim sCon
Dim sSql
Dim oRs
Dim conn
Dim oCom
Dim oItem
Dim m, n, s
Dim hourdate
Dim secdate
Dim k
Dim oList
Dim d1,d2,t1,t2,dd1,dd2,tt1,tt2,d,t,dd,tt
Dim fd,ld,at,att
Set d1=ScreenItems("控件5")
Set t1=ScreenItems("控件6")
Set d2=ScreenItems("控件7")
Set t2=ScreenItems("控件8")
'at=DateAdd("h", -8,t1.value)
'att=DateAdd("h", -8,t2.value)
d=Split(d1.Value," ")
t=Split(t1.value," ")
dd=Split(d2.Value," ")
tt=Split(t2.value," ")
fd=d(0)&" "&t(1)
ld=dd(0)&" "&tt(1)
'MsgBox fd&" "&ld
Set oList = ScreenItems("控件1")
oList.View =3
sPro = "Provider=sqloledb.1;integrated security=sspi ; persist security info=false;" sDsn = "initial catalog=CC_test_14_05_14_17_06_26R;"
sSer = "Data Source=AH-ZhangWei.\WinCC;"
sCon = sPro + sDsn + sSer
Dim sa,sb
Set sa=HMIRuntime.tags("barcode_1")
sa.Read
If sa.Value = 0 Then
sSql= "select * from UA#CCS_line where dt between '"&fd&"' and '"&ld&"' "
Else
sSql= "select * from UA#CCS_line where barcode = "&sa.value&" and dt between '"&fd&"' and '"&ld&"' "
End If
'dt between '2014-07-01 0:0:0' and '2014-07-03 0:0:0'
'barcode =" &sa.Value& "and
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = sCon
conn.CursorLocation = 3
conn.Open
Set oRs = CreateObject("ADODB.Recordset")
Set oCom = CreateObject("mand")
mandType = 1
Set oCom.ActiveConnection = conn
mandText = ssql
Set oRs = oCom.Execute
MsgBox ("共" & CStr(oRs.RecordCount) & "条记录" & vbCrLf)
'//下面是输出到Listview的代码
m = oRs.RecordCount
With oList
.ColumnHeaders.Clear
.ColumnHeaders.Add , , CStr(oRs.Fields(1).Name), 90
.ColumnHeaders.Add , , CStr(oRs.Fields(2).Name), 90
.ColumnHeaders.Add , , CStr(oRs.Fields(3).Name), 90
.ColumnHeaders.Add , , CStr(oRs.Fields(4).Name), 90
.ColumnHeaders.Add , , CStr(oRs.Fields(5).Name), 90
End With
ScreenItems("控件1").listitems.clear
If (m > 0) Then
oRs.MoveFirst
n = 0
Do While Not oRs.EOF
n = n + 1
's = Left(CStr(oRs.Fields(1).Value), 23)
's = CDate(oRs.Fields(1).Value)
'k = DateAdd("h", 8, s)
Set oItem = oList.ListItems.Add() oItem.Text = (oRs.Fields(1).Value) oItem.SubItems(1) = (oRs.Fields(2).Value) oItem.SubItems(2) = (oRs.Fields(3).Value) oItem.SubItems(3) = (oRs.Fields(4).Value) oItem.SubItems(4) = (oRs.Fields(5).Value)
oRs.MoveNext
Loop
oRs.Close
Else
End If
Set oRs = Nothing
conn.Close
Set conn = Nothing。