wincc与数据库sql server之间的数据存储 |
您所在的位置：网站首页 › Wincc数据库备份 › wincc与数据库sql server之间的数据存储 |
wincc与数据库sql server之间的数据存储
|
本测试是在哔哩哔哩上找的教程https://www.bilibili.com/video/BV1vV411y7VM?from=search&seid=8697992749928944635，自己按照讲解去做的，主要是把代码提供出来，自己敲的 实现目标：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 Sub6、全局动作--在控件中循环显示当前最新数据 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 Function7、全局动作--往数据库中写数据 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 Function8、按时间检索数据--控件显示 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 Sub9、最终结果
|
【本文地址】
今日新闻 |
推荐新闻 |