WINCC SQL报警记录和变量记录数据读取查询

WINCC SQL报警记录和变量记录数据读取查询

按时间查询报警记录

用到控件为listView,textbox,progctrl,ImageComboCtrl

Sub X6309X94AE1X0000X59CB_X6309X94AE1X0000X0000_X6309X94AE1X0000X0058_X6309X94AE1X00009_OnClick(ByVal Item)

Dim sPro

Dim sDsn

Dim sSer

Dim sCon

Dim sSql

Dim oRs

Dim conn

Dim oCom

Dim oItem

Dim comItem

Dim m, n, s

Dim oList

Dim T1,T2,T3,T4,T5,T6,record1,record2

Dim a,b

 

Set oList = ScreenItems("ListView1")

Set T1=ScreenItems("T1")

Set T2=ScreenItems("T2")

Set T6=ScreenItems("2")

Set T3=ScreenItems("3")

Set T4=ScreenItems("4")

Set T5=ScreenItems("pro")

Set record1=ScreenItems("5")

Set record2=ScreenItems("6")

 

oList.View =3

oList.ListItems.Clear

T1.ComboItems.Clear

T2.ComboItems.Clear

sPro = "Provider=WinCCOLEDBProvider.1;"

sDsn = "Catalog=CC_HJ_07_08_19_17_28_13R;"

sSer = "Data Source=.\WINCC"

sCon = sPro + sDsn + sSer

sSql = "ALARMVIEW:Select * FROM AlgViewCHT WHERE DateTime>'"+Trim(T1.Text)+"' AND DateTime<'"+Trim(T2.Text)+"'"

 

 

Set conn = CreateObject("ADODB.Connection")

conn.ConnectionString = sCon

conn.CursorLocation = 3

conn.Open

 

Set oRs = CreateObject("ADODB.Recordset")

Set oCom = CreateObject("ADODB.Command")

oCom.CommandType = 1

Set oCom.ActiveConnection = conn

oCom.CommandText = sSql

Set oRs = oCom.Execute

 

m =oRs.RecordCount

T6.OutputValue=m

 

With oList

  .ColumnHeaders.Clear

  .ColumnHeaders.Add , , CStr(oRs.Fields(2).Name), 120

  .ColumnHeaders.Add , , CStr(oRs.Fields(0).Name),60

  .ColumnHeaders.Add , , CStr(oRs.Fields(1).Name), 60

  .ColumnHeaders.Add , , CStr(oRs.Fields(37).Name), 120

End With

 

If (m > 0) Then

 

oRs.MoveLast

record2.OutputValue=oRs.Fields(2).value

 

oRs.MoveFirst

 

Do While Not oRs.EOF

 

If n=0  Then

record1.OutputValue=oRs.Fields(2).value

End If

 

n = n + 1

T5.Max=m

T5.Value=n

 

s = oRs.Fields(2).value

 

Set comItem=T1.ComboItems.Add()

comItem.Text = s

Set comItem=T2.ComboItems.Add()

comItem.Text = s

Set oItem = oList.ListItems.Add()

oItem.Text = s

oItem.SubItems(1) = CStr(oRs.Fields(0).value)

oItem.SubItems(2) = CStr(oRs.Fields(1).value)

oItem.SubItems(3) = CStr(oRs.Fields(37).value)

 

If Trim(oRs.Fields(37).value)="上限值" then

a=a+1

End If

T3.OutputValue=a

 

If Trim(oRs.Fields(37).value)="下限值" then

b=b+1

End If

T4.OutputValue=b

 

oRs.MoveNext

Loop

oRs.Close

Else

End If 

Set oRs = Nothing

Set conn = Nothing 

End Sub

如有交流，请致信QQ:  1625785563