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

合集下载
  1. 1、下载文档前请自行甄别文档内容的完整性,平台不提供额外的编辑、内容补充、找答案等附加服务。
  2. 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
  3. 3、如文档侵犯您的权益,请联系客服反馈,我们会尽快为您处理(人工客服工作时间:9:00-18:30)。

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

相关文档
最新文档