搬家第25天-citectv7.4citectVBA向SQLServer数据表添加记录

前一篇博客记录了如何从sql数据库表中读取记录显示在msflexgrid控件中，这一篇记录如何向sqlserver写数据。在citect建立三个本地标签flow1,flow2,flow3

在cicode编辑器写下面的脚本

Sub AddRecordToSQLDB()
Dim conn As Object
Dim strcon As string
Dim ors As Object
Dim ocom As Object
Dim ssql As string
Dim curmonth As string
Dim curday As string
Dim curhour As string
Dim curminute As string
Dim cursecond As string
Dim curdate As string
Dim curtime As string
flow1=200*rnd+100
flow2=300*rnd+200
flow3=400*rnd+300
If month(now())<10 Then
 curmonth="0" & cstr(month(now()))
Else
 curmonth= cstr(month(now()))
End If
If day(now())<10 Then
 curday="0" & cstr(day(now()))
Else
 curday= cstr(day(now()))
End If
If hour(now())<10 Then
 curhour="0" & cstr(hour(now()))
Else
 curhour= cstr(hour(now()))
End If
If minute(now())<10 Then
 curminute="0" & cstr(minute(now()))
Else
 curminute= cstr(minute(now()))
End If
If second(now())<10 Then
 cursecond="0" & cstr(second(now()))
Else
 cursecond= cstr(second(now()))
End If
curdate=cstr(year(now())) & "-" & curmonth & "-" & curday
curtime=curhour & ":" & curminute & ":" & cursecond
strcon = "Provider = SQLOLEDB.1;password = 3390011;user id = sa;Initial Catalog =CitectDB;Data Source =ES\SQLEXPRESS"

ssql="insert into citectdb.dbo.Rpt1(CurDate,CurTime,flow1,flow2,flow3) values('" & curdate & "','" & curtime & "'," _
   & flow1 & "," & flow2 & "," & flow3 & ")"
Set conn=CreateObject("ADODB.Connection")
conn.connectionstring=strcon
conn.Cursorlocation=3
conn.open
Set ors=CreateObject("ADODB.RecordSet")
Set ocom=CreateObject("ADODB.Command")
ocom.commandtype=1
Set ocom.ActiveConnection=conn
ocom.CommandText=ssql
Set ors=ocom.Execute
Set ors=Nothing
conn.close
Set conn=Nothing
End Sub

 

由于练习没有连接实际的PLC，所以使用了随机函数rnd，分别让flow1在100至200之间变化,flow2在200到300之间变化，flow3在300到400之间变化。

在工程编辑器菜单“系统-事件”中定义一个事件Addrecord，每秒触发一次，每次调用AddRecordToSQLDB脚本

编译后在citect管理器“计算机设置向导”中设置启用Addrecord事件。运行项目后，就能按照1秒1次的频率向sql server的数据表写数据了。