在WinCC中通过VBS操作SQL Server2005

在WinCC中通过VBS操作SQL Server2005

在项目中需要在一定条件满足时，保存一些数据到数据库中，并可根据条件查询。考虑到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