这里写自定义目录标题
实现目标:1、把wincc中变量,存储到SQL数据库中
2、查询数据库内容,在控件中显示
步骤:1、软件wincc 7.3、wincc安装自带的sql server 2008 R2
2、新建数据库
3、新建wincc项目--变量建立
4、所用到的控件
5、画面打开脚本:主要实现最新数据显示、连接数据库、控件设置等
-
Sub OnOpen()
-
-
-
-
Dim kj1,kj2,kj3,kj4,kj5,kj6
-
Dim QR
-
Dim MSFlexGrid1
'对应表格控件名称
-
Dim LocalBeginTime, LocalEndTime,riqi
-
Dim oRs,oRs1,n,n1,i,z,s1,s11,oCom,oCom1,strcn,conn,pj
-
Dim zxy1
-
'查询当天全部数据,除了控件名称要注意修改外,以上其他为标准
-
Set MSFlexGrid1 = ScreenItems(
"aaaa")
'对应表格控件名称
-
-
riqi = Now
-
-
LocalBeginTime = Year(riqi) &
"-" & Month(riqi) &
"-" & Day(riqi) &
"" &
"00:00:00"
-
LocalEndTime = Year(riqi) &
"-" & Month(riqi) &
"-" & Day(riqi) &
"" &
"23:59:59"
-
-
s1 =
"SELECT DT,name,a,b,c,d,e FROM en WHERE DT BETWEEN '" & LocalBeginTime &
"' And '" & LocalEndTime &
"'ORDER BY DT"
-
's1后面的内容要对应数据库中列的内容,后面的DT与前面的DT要名称一致,enen要对应数据库列表名称dbo的名称
-
strcn =
"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist SecurityInfo=False;Initial Catalog=wang;Data Source=.\wincc"
-
-
Set conn = CreateObject(
"ADODB.Connection")
-
-
conn.ConnectionString = strcn
-
-
conn.CursorLocation =
3
-
-
conn.Open
-
's1 = "SELECT DT,name,a,b,c,d,e FROM en WHERE DT BETWEEN '" & LocalBeginTime & "' And '" & LocalEndTime & "'ORDER BY DT"
-
'Set oRs = CreateObject("ADODB.Recordset")
-
Set oRs = CreateObject(
"ADODB.Recordset")
-
Set oCom = CreateObject(
"ADODB.Command")
-
-
oCom.CommandType =
1
-
Set oCom.ActiveConnection = conn
-
oCom.CommandText = s1
'执行检索功能
-
Set oRs = oCom.Execute
-
'n = oRs.RecordCount '获得检索到的总数
-
-
'HMIRuntime.Tags("I3").Write n
-
-
-
MSFlexGrid1.Clear
-
'MSFlexGrid1.Rows = oRs.RecordCount + 2
-
MSFlexGrid1.ColWidth(
0) =
1500
-
MSFlexGrid1.ColWidth(
1) =
2000
-
MSFlexGrid1.ColWidth(
2) =
1500
-
MSFlexGrid1.ColWidth(
3) =
1500
-
MSFlexGrid1.ColWidth(
4) =
1500
-
MSFlexGrid1.ColWidth(
5) =
1500
-
MSFlexGrid1.ColWidth(
6) =
1500
-
MSFlexGrid1.ColWidth(
7) =
1500
-
-
MSFlexGrid1.RowHeight(
0) =
1200
-
MSFlexGrid1.RowHeight(
1) =
600
-
-
MSFlexGrid1.Row =
0
-
For z =
0
To
7
-
MSFlexGrid1.CellFontSize =
12
-
MSFlexGrid1.Col = z
-
MSFlexGrid1.
Text =
"工况信息表"
-
Next
-
MSFlexGrid1.MergeCells =
4
-
MSFlexGrid1.MergeRow(
0) =
True
-
-
MSFlexGrid1.Row =
1
-
-
For z =
0
To
7
-
MSFlexGrid1.Col = z
-
-
MSFlexGrid1.CellBackColor = vbCyan
-
Next
-
MSFlexGrid1.TextMatrix(
1,
0) =
"序号"
-
MSFlexGrid1.TextMatrix(
1,
1) =
"日期"
-
MSFlexGrid1.TextMatrix(
1,
2) =
"名称"
-
MSFlexGrid1.TextMatrix(
1,
3) =
"重量(kg)"
-
MSFlexGrid1.TextMatrix(
1,
4) =
"高度(mm)"
-
MSFlexGrid1.TextMatrix(
1,
5) =
"流量"
-
MSFlexGrid1.TextMatrix(
1,
6) =
"压力"
-
MSFlexGrid1.TextMatrix(
1,
7 )=
"温度"
-
-
-
MSFlexGrid1.ColAlignment(
0) =
4
-
MSFlexGrid1.ColAlignment(
1) =
4
-
MSFlexGrid1.ColAlignment(
2) =
4
-
MSFlexGrid1.ColAlignment(
3) =
4
-
MSFlexGrid1.ColAlignment(
4) =
4
-
MSFlexGrid1.ColAlignment(
5) =
4
-
MSFlexGrid1.ColAlignment(
6) =
4
-
MSFlexGrid1.ColAlignment(
7) =
4
-
-
If (n >
0)
Then
-
-
oRs.MoveFirst
-
i =
0
-
Do
While
Not oRs.EOF
-
n = n +
1
-
-
MSFlexGrid1.TextMatrix(i+
2,
0) = i
-
MSFlexGrid1.TextMatrix(i+
2,
1) = oRs.Fields(
0).Value
-
MSFlexGrid1.TextMatrix(i+
2,
2) = oRs.Fields(
1).Value
-
MSFlexGrid1.TextMatrix(i+
2,
3) = oRs.Fields(
2).Value
-
MSFlexGrid1.TextMatrix(i+
2,
4) = oRs.Fields(
3).Value
-
MSFlexGrid1.TextMatrix(i+
2,
5) = oRs.Fields(
4).Value
-
MSFlexGrid1.TextMatrix(i+
2,
6) = oRs.Fields(
5).Value
-
MSFlexGrid1.TextMatrix(i+
2,
7) = oRs.Fields(
6).Value
-
-
-
i = i +
1
-
oRs.MoveNext
-
Loop
-
conn.Close
-
MSFlexGrid1.TopRow = MSFlexGrid1.Rows -
1
-
Else
-
MsgBox
"您所查询的时段没有数据......"
-
oRs.Requery
-
conn.Close
-
End
If
-
-
End
Sub
6、全局动作--在控件中循环显示当前最新数据
-
Function action
-
-
Dim sCon,conn,oRs,n,i,s1,oCom,strcn,z
-
Dim riqi,mazhi,qxlx,qxwz,ok
-
Dim MSFlexGrid1
-
Dim LocalBeginTime,LocalEndTime
-
Dim R_JC,Weight,R_FC,GBD
-
Dim J1
-
Set J1 = HMIRuntime.Tags(
"aa")
-
J1.Read
-
If J1.Read
Then
-
J1.Write
0
-
Else
-
J1.Write
1
-
Set MSFlexGrid1 = HMIRuntime.Screens(
"首页.画面窗口1:报表信息").ScreenItems(
"aaaa")
-
-
riqi = Now
-
-
Dim name
-
Set name = HMIRuntime.Tags(
"name1")
-
name.Read
-
-
Dim R1
-
Set R1 = HMIRuntime.Tags(
"a")
-
R1.Read
-
-
Dim R2
-
Set R2 = HMIRuntime.Tags(
"b")
-
R2.Read
-
-
Dim R3
-
Set R3 = HMIRuntime.Tags(
"c")
-
R3.Read
-
-
Dim R4
-
Set R4 = HMIRuntime.Tags(
"d")
-
R4.Read
-
-
Dim R5
-
Set R5 = HMIRuntime.Tags(
"e")
-
R5.Read
-
-
If name.Value =
""
Then
-
Else
-
'sCon = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist SecurityInfo=False;Initial Catalog=wang;Data Source=.\wincc"
-
'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
-
-
's1 = "insert into en (DT,name,a,b,c,d,e) Values ( '"&riqi&"','"&name.Value&"','"&R1.Value&"','"&R2.Value&"','"&R3.Value&"','"&R4.Value&"','"&R5.Value&"')"
-
'conn.Execute s1
-
'conn.Close
-
-
-
MSFlexGrid1.Rows = MSFlexGrid1.Rows +
1
-
-
n = MSFlexGrid1.Rows
-
MSFlexGrid1.TextMatrix(n-
1,
0) = n -
3
-
MSFlexGrid1.TextMatrix(n-
1,
1) = riqi
-
MSFlexGrid1.TextMatrix(n-
1,
2) = name.Value
-
MSFlexGrid1.TextMatrix(n-
1,
3) = R1.Value
-
MSFlexGrid1.TextMatrix(n-
1,
4) = R2.Value
-
MSFlexGrid1.TextMatrix(n-
1,
5) = R3.Value
-
MSFlexGrid1.TextMatrix(n-
1,
6) = R4.Value
-
MSFlexGrid1.TextMatrix(n-
1,
7) = R5.Value
-
-
-
MSFlexGrid1.TopRow = MSFlexGrid1.Rows -
1
-
End
If
-
End
If
-
-
End
Function
7、全局动作--往数据库中写数据
-
Option
Explicit
-
Function action
-
Dim sCon,conn,oRs,n,i,s1,oCom,strcn,z
-
Dim riqi,mazhi,qxlx,qxwz,ok
-
Dim MSFlexGrid1
-
Dim LocalBeginTime,LocalEndTime
-
Dim R_JC,Weight,R_FC,GBD
-
Dim J1
-
Set J1 = HMIRuntime.Tags(
"aa")
-
J1.Read
-
If J1.Read
Then
-
J1.Write
0
-
Else
-
J1.Write
1
-
'Set MSFlexGrid1 = HMIRuntime.Screens("首页.画面窗口1:报表信息").ScreenItems("aaaa")
-
-
riqi = Now
-
-
Dim name
-
Set name = HMIRuntime.Tags(
"name1")
-
name.Read
-
-
Dim R1
-
Set R1 = HMIRuntime.Tags(
"a")
-
R1.Read
-
-
Dim R2
-
Set R2 = HMIRuntime.Tags(
"b")
-
R2.Read
-
-
Dim R3
-
Set R3 = HMIRuntime.Tags(
"c")
-
R3.Read
-
-
Dim R4
-
Set R4 = HMIRuntime.Tags(
"d")
-
R4.Read
-
-
Dim R5
-
Set R5 = HMIRuntime.Tags(
"e")
-
R5.Read
-
-
If name.Value =
""
Then
-
Else
-
sCon =
"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist SecurityInfo=False;Initial Catalog=wang;Data Source=.\wincc"
-
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
-
-
s1 =
"insert into en (DT,name,a,b,c,d,e) Values ( '"&riqi&
"','"&name.Value&
"','"&R1.Value&
"','"&R2.Value&
"','"&R3.Value&
"','"&R4.Value&
"','"&R5.Value&
"')"
-
conn.Execute s1
-
conn.Close
-
-
-
'MSFlexGrid1.Rows = MSFlexGrid1.Rows + 1
-
-
'n = MSFlexGrid1.Rows
-
'MSFlexGrid1.TextMatrix(n-1,0) = n - 3
-
'MSFlexGrid1.TextMatrix(n-1,1) = riqi
-
'MSFlexGrid1.TextMatrix(n-1,2) = name.Value
-
'MSFlexGrid1.TextMatrix(n-1,3) = R1.Value
-
'MSFlexGrid1.TextMatrix(n-1,4) = R2.Value
-
'MSFlexGrid1.TextMatrix(n-1,5) = R3.Value
-
'MSFlexGrid1.TextMatrix(n-1,6) = R4.Value
-
'MSFlexGrid1.TextMatrix(n-1,7) = R5.Value
-
-
-
'MSFlexGrid1.TopRow = MSFlexGrid1.Rows - 1
-
End
If
-
End
If
-
End
Function
8、按时间检索数据--控件显示
-
Sub OnClick(
ByVal Item)
-
-
-
Dim MSFlexGrid1
-
Dim a1,a2,a3,a4,z
-
Dim LocalBeginTime,LocalEndTime,riqi
-
Dim oRs,n,i,s1,oCom,strcn,conn
-
Dim kj4,kj5,kj6
-
'以上其他为标准
-
Set MSFlexGrid1 = ScreenItems(
"qq")
'对应表格控件名称
-
-
Set a1 = ScreenItems(
"sd")
-
Set a2 = ScreenItems(
"st")
-
Set a3 = ScreenItems(
"ed")
-
Set a4 = ScreenItems(
"et")
-
-
-
'riqi = Now
-
-
LocalBeginTime =
Year(a1.Value) &
"-" &
Month(a1.Value) &
"-" &
Day(a1.Value) &
" " &
Hour(a2.Value) &
":" &
Minute(a2.Value) &
":" &
Second(a2.Value)
-
LocalEndTime =
Year(a3.Value) &
"-" &
Month(a3.Value) &
"-" &
Day(a3.Value) &
" " &
Hour(a4.Value) &
":" &
Minute(a4.Value) &
":" &
Second(a4.Value)
-
-
s1 =
"SELECT DT,name,a,b,c,d,e FROM en WHERE DT BETWEEN '" & LocalBeginTime &
"' And '" & LocalEndTime &
"'ORDER BY DT"
-
's1后面的内容要对应数据库中列的内容,后面的DT与前面的DT要名称一致,enen要对应数据库列表名称dbo的名称
-
strcn =
"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist SecurityInfo=False;Initial Catalog=wang;Data Source=.\wincc"
-
-
Set conn =
CreateObject(
"ADODB.Connection")
-
-
conn.ConnectionString = strcn
-
-
conn.CursorLocation =
3
-
-
conn.Open
-
-
Set oRs =
CreateObject(
"ADODB.Recordset")
-
Set oCom =
CreateObject(
"ADODB.Command")
-
-
oCom.CommandType =
1
-
Set oCom.ActiveConnection = conn
-
oCom.CommandText = s1
'执行检索功能
-
Set oRs = oCom.
Execute
-
n = oRs.RecordCount
'获得检索到的总数
-
-
'HMIRuntime.Tags("I3").Write n
-
-
-
MSFlexGrid1.Clear
-
MSFlexGrid1.Rows = oRs.RecordCount +
2
-
MSFlexGrid1.ColWidth(
0) =
1500
-
MSFlexGrid1.ColWidth(
1) =
2000
-
MSFlexGrid1.ColWidth(
2) =
1500
-
MSFlexGrid1.ColWidth(
3) =
1500
-
MSFlexGrid1.ColWidth(
4) =
1500
-
MSFlexGrid1.ColWidth(
5) =
1500
-
MSFlexGrid1.ColWidth(
6) =
1500
-
MSFlexGrid1.ColWidth(
7) =
1500
-
-
MSFlexGrid1.RowHeight(
0) =
1200
-
MSFlexGrid1.RowHeight(
1) =
600
-
-
MSFlexGrid1.Row =
0
-
For z =
0
To
7
-
MSFlexGrid1.CellFontSize =
12
-
MSFlexGrid1.Col = z
-
MSFlexGrid1.Text =
"工况信息表"
-
Next
-
MSFlexGrid1.MergeCells =
4
-
MSFlexGrid1.MergeRow(
0) =
True
-
-
MSFlexGrid1.Row =
1
-
-
For z =
0
To
7
-
MSFlexGrid1.Col = z
-
-
MSFlexGrid1.CellBackColor = vbCyan
-
Next
-
MSFlexGrid1.TextMatrix(
1,
0) =
"序号"
-
MSFlexGrid1.TextMatrix(
1,
1) =
"日期"
-
MSFlexGrid1.TextMatrix(
1,
2) =
"名称"
-
MSFlexGrid1.TextMatrix(
1,
3) =
"重量(kg)"
-
MSFlexGrid1.TextMatrix(
1,
4) =
"高度(mm)"
-
MSFlexGrid1.TextMatrix(
1,
5) =
"流量"
-
MSFlexGrid1.TextMatrix(
1,
6) =
"压力"
-
MSFlexGrid1.TextMatrix(
1,
7 )=
"温度"
-
-
-
MSFlexGrid1.ColAlignment(
0) =
4
-
MSFlexGrid1.ColAlignment(
1) =
4
-
MSFlexGrid1.ColAlignment(
2) =
4
-
MSFlexGrid1.ColAlignment(
3) =
4
-
MSFlexGrid1.ColAlignment(
4) =
4
-
MSFlexGrid1.ColAlignment(
5) =
4
-
MSFlexGrid1.ColAlignment(
6) =
4
-
MSFlexGrid1.ColAlignment(
7) =
4
-
-
If (n >
0)
Then
-
-
oRs.MoveFirst
-
i =
0
-
Do
While
Not oRs.EOF
-
n = n +
1
-
-
MSFlexGrid1.TextMatrix(i+
2,
0) = i
-
MSFlexGrid1.TextMatrix(i+
2,
1) = oRs.Fields(
0).Value
-
MSFlexGrid1.TextMatrix(i+
2,
2) = oRs.Fields(
1).Value
-
MSFlexGrid1.TextMatrix(i+
2,
3) = oRs.Fields(
2).Value
-
MSFlexGrid1.TextMatrix(i+
2,
4) = oRs.Fields(
3).Value
-
MSFlexGrid1.TextMatrix(i+
2,
5) = oRs.Fields(
4).Value
-
MSFlexGrid1.TextMatrix(i+
2,
6) = oRs.Fields(
5).Value
-
MSFlexGrid1.TextMatrix(i+
2,
7) = oRs.Fields(
6).Value
-
-
-
i = i +
1
-
oRs.MoveNext
-
Loop
-
conn.Close
-
MSFlexGrid1.TopRow = MSFlexGrid1.Rows -
1
-
Else
-
MsgBox
"您所查询的时段没有数据......"
-
oRs.Requery
-
conn.Close
-
End
If
-
-
End
Sub
9、最终结果