在项目中需要在一定条件满足时,保存一些数据到数据库中,并可根据条件查询。考虑到WinCC6.2以后采用的就是SQL Server2005数据库,所以直接利用该数据库即可,通过SQL Server Management Studio(SSMS)可以创建自己的数据库,并安要求创建好表。
一、数据库连接
在SQL Server Management Studio(SSMS)中创建名为evcp的数据库,再创建名为evcp的表,然后根据需要创建Columns,在本项目中创建了norder(流水号)、pileno(桩号)、cardno(卡号)、operno(员工号)、energy(电量)、cost(金额)、period(时长)、rate(费率)、pdate(日期)和ptime(时间)。
在本项目中采用ODBC的方式连接数据库,首先在控制面板中创建好数据源,配置好SQL Server驱动数据源,命名为evcs。
二、数据写入
要求在一个状态量值为1的时候完成数据库的保存,等数据保存完后将状态量清0。
1、先在全局脚本VBS项目模块中创建函数savedata,代码如下:
Sub savedata Dim objConnection Dim objCommand Dim objRecordset Dim strConnectionString Dim strSQL Dim norder,pileno,cardno,operno,energy,cost,period,rate,pdate,ptime norder=HMIRuntime.Tags("norder").Read pileno= HMIRuntime.Tags("pileno").Read cardno=HMIRuntime.Tags("cardno").Read operno= HMIRuntime.Tags("operno").Read energy= HMIRuntime.Tags("energy").Read cost= HMIRuntime.Tags("cost").Read period= HMIRuntime.Tags("period").Read rate= HMIRuntime.Tags("rate").Read pdate= HMIRuntime.Tags("pdate").Read ptime= HMIRuntime.Tags("ptime").Read strConnectionString = "Provider=MSDASQL;DSN=evcs;UID=;PWD=;" Set objConnection = CreateObject("ADODB.Connection") objConnection.ConnectionString = strConnectionString objConnection.Open Set objRecordset = CreateObject("ADODB.Recordset") Set objCommand = CreateObject("ADODB.Command") objCommand.ActiveConnection = objConnection strSQL = "insert into evcp (norder,pileno,cardno,operno,energy,cost,period,rate,pdate,ptime) values ("&_ "'"&norder&"',"&_ "'"&pileno&"',"&_ "'"&cardno&"',"&_ "'"&operno&"',"&_ "'"&energy&"',"&_ "'"&cost&"',"&_ "'"&period&"',"&_ "'"&rate&"',"&_ "'"&pdate&"',"&_ "'"&ptime&"')" 'MsgBox (strSQL) objCommand.CommandText = strSQL objCommand.Execute Set objCommand = Nothing objConnection.Close Set objRecordset = Nothing Set objConnection = Nothing End Sub
2、在全局脚本VBS动作中创建1秒周期的周期性出发动作,并添加如下代码:
Option Explicit Function action Dim v1 v1=HMIRuntime.Tags("satuse").Read If v1 Then Call savedata HMIRuntime.Tags("satuse").Write 0 End if End Function
这样当satuse值为1时系统自动保存数据
三、数据查询
数据的查询要复杂一些,需要用到MSFlexGrid控件、MS Form2 ComboBox控件和MS Form2 TextBox,这几个控件可以单独注册也可以安装VB6后自动添加。
在查询页面上添加打开页面执行脚本如下:
Sub OnOpen() Dim MSFlexGrid1,cb1,tb1,tb2 Set MSFlexGrid1 = ScreenItems("控件1") Set cb1 = ScreenItems("cb1") Set tb1 = ScreenItems("tb1") Set tb2 = ScreenItems("tb2") MSFlexGrid1.Clear MSFlexGrid1.ColWidth(0) = 620 MSFlexGrid1.ColWidth(1) = 1500 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.ColWidth(8) = 1600 MSFlexGrid1.ColWidth(9) = 2000 MsFlexGrid1.ColWidth(10) = 2000 MSFlexGrid1.TextMatrix(0,0) = "编号" MSFlexGrid1.TextMatrix(0,1) = "流水号" MSFlexGrid1.TextMatrix(0,2) = "桩号" MSFlexGrid1.TextMatrix(0,3) = "卡号" MSFlexGrid1.TextMatrix(0,4) = "操作员号" MSFlexGrid1.TextMatrix(0,5) = "电量(度)" MSFlexGrid1.TextMatrix(0,6) = "金额(元)" MSFlexGrid1.TextMatrix(0,7) = "时长(分)" MSFlexGrid1.TextMatrix(0,8) = "费率(元/度)" MSFlexGrid1.TextMatrix(0,9) = "日期" MSFlexGrid1.TextMatrix(0,10) = "时间" 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 MSFlexGrid1.ColAlignment(8) = 4 MSFlexGrid1.ColAlignment(9) = 4 MSFlexGrid1.ColAlignment(10) = 4 Dim i For i= 1 To 39 Step 1 MSFlexGrid1.TextMatrix(i,0) = i Next cb1.Text="*" cb1.AddItem "*" cb1.AddItem "1" cb1.AddItem "2" tb1.Text="*" tb2.Text="*" End Sub
这段代码主要是用来初始化控件的显示。
在查询按钮加入相应的代码实现三个键值(桩号、卡号、操作员号)条件组合的查询,代码如下:
Sub Click(Byval Item) Dim objConnection Dim objCommand Dim objRecordset Dim strConnectionString Dim strSQL Dim MSFlexGrid1,cb1,tb1,tb2 Dim i1,i2,cv1,cv2,cv3,cv Set MSFlexGrid1 = ScreenItems("控件1") Set cb1 = ScreenItems("cb1") Set tb1 = ScreenItems("tb1") Set tb2 = ScreenItems("tb2") '清除原有记录 For i1 = 1 To 39 Step 1 For i2=1 To 10 Step 1 MSFlexGrid1.TextMatrix(i1, i2) ="" Next Next strConnectionString = "Provider=MSDASQL;DSN=evcs;UID=;PWD=;" Set objConnection = CreateObject("ADODB.Connection") objConnection.ConnectionString = strConnectionString objConnection.Open Set objRecordset = CreateObject("ADODB.Recordset") Set objCommand = CreateObject("ADODB.Command") objCommand.ActiveConnection = objConnection cv1=0 cv2=0 cv3=0 If cb1.Text<>"*" Then cv1=4 End If If tb1.Text<>"*" Then cv2=2 End if If tb2.Text<>"*" Then cv3=1 End If cv=cv1+cv2+cv3 Select Case cv Case 7 strSQL = "select * from evcp where pileno like '"&cb1.Text&"'And cardno like '"&tb1.Text&"'And operno like '"&tb2.Text&"'" Case 6 strSQL = "select * from evcp where pileno like '"&cb1.Text&"'And cardno like '"&tb1.Text&"'" Case 5 strSQL = "select * from evcp where pileno like '"&cb1.Text&"'And operno like '"&tb2.Text&"'" Case 4 strSQL = "select * from evcp where pileno like '"&cb1.Text&"'" Case 3 strSQL = "select * from evcp where cardno like '"&tb1.Text&"'And operno like '"&tb2.Text&"'" Case 2 strSQL = "select * from evcp where cardno like '"&tb1.Text&"'" Case 1 strSQL = "select * from evcp where operno like '"&tb2.Text&"'" Case Else strSQL = "select * from evcp" End Select objCommand.CommandText = strSQL Set objRecordset = objCommand.Execute Dim i i=0 If (objRecordset.Bof And objRecordset.Eof) Then MsgBox("没有符合要求的记录") Else While Not objRecordset.EOF i=i+1 MSFlexGrid1.TextMatrix(i, 1) = CStr(objRecordset.Fields(0).Value) MSFlexGrid1.TextMatrix(i, 2) = CStr(objRecordset.Fields(1).Value) MSFlexGrid1.TextMatrix(i, 3) = CStr(objRecordset.Fields(2).Value) MSFlexGrid1.TextMatrix(i, 4) = CStr(objRecordset.Fields(3).Value) MSFlexGrid1.TextMatrix(i, 5) = CStr(objRecordset.Fields(4).Value) MSFlexGrid1.TextMatrix(i, 6) = CStr(objRecordset.Fields(5).Value) MSFlexGrid1.TextMatrix(i, 7) = CStr(objRecordset.Fields(6).Value) MSFlexGrid1.TextMatrix(i, 8) = CStr(objRecordset.Fields(7).Value) MSFlexGrid1.TextMatrix(i, 9) = CStr(objRecordset.Fields(8).Value) MSFlexGrid1.TextMatrix(i, 10) = CStr(objRecordset.Fields(9).Value) objRecordset.movenext Wend End If Set objCommand = Nothing objConnection.Close Set objRecordset = Nothing Set objConnection = Nothing End Sub