wincc与数据库sql server之间的数据存储 |
您所在的位置：网站首页 › wincc如何安装 › wincc与数据库sql server之间的数据存储 |
wincc与数据库sql server之间的数据存储
|
这里写自定义目录标题
实现目标：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、最终结果
|
【本文地址】
今日新闻 |
推荐新闻 |