MSFlexGrid与online trendControl 访问局域网内远程数据库

本文主要是介绍MSFlexGrid与online trendControl 访问局域网内远程数据库，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

项目场景：

wincc访问局域网内远程数据库（vbs脚本）

数据库配置：

添加一个一个用户作为远程登录名（也可以直接用sa用户和密码），登录属性的用户映射选择特定可以访问的数据库，数据库成员身份选择db_datareader和db_datawriter和pbulic
打开SQL server配置管理器，启用TCP/IP远程访问，并设置远程访问端口。端口设置为1433
防火墙关闭或者，防火墙-高级设置-出站规则，设置1433端口允许。

代码展示：

Sub OnOpen()                                     
Dim LocalBeginTime, LocalEndTime, riqi
Dim objRecordset, n, i, z, sql_command, objComand, strcn, objConnection
Dim lngFactor
Dim dblAxisX
Dim dblAxisY
Dim objTrendControl
Dim objTrend_Ia, objTrend_Ib, objTrend_Ic, objTrend_p
Dim Time_Values, Y_Values
Dim DT
Dim var
Dim Tag_num '用来记录这个有多少个变量，便于提前开辟表格 的行数
Dim pre_time

'变量定义
Dim EA,Ia,Ib,Ic,P,PF,Uab,Ubc,Uca,Va,Vb,Vc,Q,ER

Set EA = HMIRuntime.Tags("EA")
EA = EA.Read

Set Ia = HMIRuntime.Tags("Ia")
Ia = Ia.Read

Set Ib = HMIRuntime.Tags("Ib")
Ib = Ib.Read

Set Ic = HMIRuntime.Tags("Ic")
Ic = Ic.Read

Set P = HMIRuntime.Tags("P")
P = P.Read

Set PF = HMIRuntime.Tags("PF")
PF = PF.read

Set Uab = HMIRuntime.Tags("Uab")
Uab = Uab.read

Set Ubc = HMIRuntime.Tags("Ubc")
Ubc = Ubc.read

Set Uca = HMIRuntime.Tags("Uca")
Uca = Uca.read

Set Va = HMIRuntime.Tags("Va")
Va = Va.read

Set Vb = HMIRuntime.Tags("Vb")
Vb = Vb.read

Set Vc = HMIRuntime.Tags("Vc")
Vc = Vc.read

Set Q = HMIRuntime.Tags("Q")
Q = Q.read

Set ER = HMIRuntime.Tags("ER")
ER = ER.read



riqi = Now
'获取当前时间的年月日
Dim y, m
y = CStr(Year(riqi))
m = Month(riqi)
d = Day(riqi)


'数据库查询（数据库表名需要修改），从数据库中读出数据
sql_command = "select * from (select top 200 * from my_table" + " where TagID = '" +  Ia +"' Or TagID = '"+ Ib +"' Or TagID = '"+ Ic +"' or TagID = '"+ p +"'   order by TagHistoryDt desc) as a order by TagHistoryDt "

'设置连接字符串
Dim sPro
sPro = "Provider=SQLOLEDB.1;persist Security Info=True;User ID=sa;Password=123456;"	'远程数据库的用户名与密码
Dim sDsn
sDsn = "Initial Catalog = CHEMSHistory;"	'远程数据库名字
Dim sSer
sSer = "Data Source = 192.168.1.1, 1433;"		'远程服务器的ip地址
strcn = sPro+sDsn+sSer


'创建连接对象
Set objConnection = CreateObject("ADODB.Connection")
	objConnection.ConnectionString = strcn	'设置连接对象的属性
	objConnection.CursorLocation = 3	'使用客户端游标
	objConnection.Open	 '建立连接
Set objRecordset = CreateObject("ADODB.Recordset")	'创建记录集对象
Set objComand = CreateObject("ADODB.Command")	'创建命令对象
 
objComand.CommandType = 1
Set objComand.ActiveConnection = objConnection
objComand.CommandText = sql_command   '执行检索功能
Set objRecordset = objComand.Execute
n = objRecordset.RecordCount  '获得检索结果的总数

'这些需要在wincc online TrendControl中设置好多个趋势
Set objTrendControl = ScreenItems("timeTrend")	'wincc online TrendControl控件的对象名称
Set objTrend_Ia = objTrendControl.GetTrend("Ia")	' 选择对象中的趋势（与控件内的趋势名字对应）
objTrend_Ia.RemoveData
Set objTrend_Ib = objTrendControl.GetTrend("Ib")	' 选择对象中的趋势
objTrend_Ib.RemoveData
Set objTrend_Ic = objTrendControl.GetTrend("Ic")	' 选择对象中的趋势
objTrend_Ic.RemoveData
Set objTrend_p = objTrendControl.GetTrend("p")	' 选择对象中的趋势
objTrend_p.RemoveData


If (n > 0)Then 
    objRecordset.MoveFirst		'移动指针到检索结果的第一行
    i = 0
    While Not  objRecordset.eof 	'是否到达记录的末尾
        DT = objRecordset.Fields(1).Value	'(DT)
	    ' 时间格式：2016-3-10 10:20:00（固定格式）
        dblAxisX = Year(DT) & "-" & Month(DT) & "-" & Day(DT) & " " & Hour(DT) & ":" & Minute(DT) & ":" & Second(DT)
        dblAxisY = objRecordset.Fields(2).Value	'数值
        var = objRecordset.Fields(3).Value		'数据的具体标签，根据这个标签来选择填充哪条曲线
        If(i> n-200)Then		'曲线只显示200个数据
        		Select Case var		'根据不同的标签来选择填充不同的曲线
				Case "{"+Ia+"}"
					objTrend_Ia.InsertData dblAxisY, dblAxisX
				Case "{"+Ib+"}"
					objTrend_Ib.InsertData dblAxisY, dblAxisX
				Case "{"+Ic+"}"
					objTrend_Ic.InsertData dblAxisY, dblAxisX
				Case "{"+p+"}"
					objTrend_p.InsertData dblAxisY, dblAxisX
			End select
	   End If
        i = i + 1
        objRecordset.MoveNext
    Wend
End If

'=======================================================================================
'开始设置表格
Dim MSFlexGrid   
Set MSFlexGrid = ScreenItems("MSFlexGrid") 	'对应MSFlexGrid控件名称
sql_command = "select * from (select * from TagHistory"+LocalTime+" where TagID = '" +  Ia +"' Or TagID = '"+ Ib +"' Or TagID = '"+ Ic +"' or TagID = '"+ p +"' or TagID = '"+ EA +"' or TagID = '"+ PF+"' or TagID = '"+ Uab+"' or TagID = '"+ Ubc+"' or TagID = '"+ Uca+"' or TagID = '"+ Va+"' or TagID = '"+ Vb+"' or TagID = '"+ Vc+"' order by TagHistoryDt desc) as a order by TagHistoryDt "
Tag_num = 12		'要显示多少个变量
	
objComand.CommandText = sql_command   '执行检索功能
Set objRecordset = objComand.Execute
n = objRecordset.RecordCount  '获得检索结果的总数
'清空控件内容
MSFlexGrid.Clear
MSFlexGrid.ColWidth(0) = 700	'第一列宽度设置
MSFlexGrid.ColWidth(1) = 3000
MSFlexGrid.ColWidth(2) = 800
MSFlexGrid.ColWidth(3) = 800
MSFlexGrid.ColWidth(4) = 800	
MSFlexGrid.ColWidth(5) = 800
MSFlexGrid.ColWidth(6) = 800
MSFlexGrid.ColWidth(7) = 800
MSFlexGrid.ColWidth(8) = 800	
MSFlexGrid.ColWidth(9) = 800
MSFlexGrid.ColWidth(10) = 800
MSFlexGrid.ColWidth(11) = 800
MSFlexGrid.ColWidth(12) = 800
MSFlexGrid.ColWidth(13) = 800
MSFlexGrid.ColWidth(14) = 800	
MSFlexGrid.ColWidth(15) = 800
MSFlexGrid.ColWidth(16) = 800
MSFlexGrid.ColWidth(17) = 800
MSFlexGrid.ColWidth(18) = 800	
MSFlexGrid.ColWidth(19) = 800
MSFlexGrid.ColWidth(20) = 800


MSFlexGrid.RowHeight(0) = 400	'第一行高度
MSFlexGrid.RowHeight(1) = 380
 
MSFlexGrid.Row = 0
For z = 0 To 20
  MSFlexGrid.CellFontSize = 12	'字体大小
  MSFlexGrid.Col = z
  MSFlexGrid.Text = "历史数据"
  Next 
  MSFlexGrid.MergeCells = 4		'相同内容合并单元格
  MSFlexGrid.MergeRow(0) = True	'合并单元格
  MSFlexGrid.Row = 1
  
 For z = 0 To 20	 '根据上面的列数来决定
 	MSFlexGrid.Col = z
 	
  MSFlexGrid.CellBackColor = vbCyan   '第二行颜色设置为青色
 Next
   MSFlexGrid.TextMatrix(1,0) = "序号"	'单位，需要根据实际情况修改
   MSFlexGrid.TextMatrix(1,1) = "日期"
   MSFlexGrid.TextMatrix(1,2) = "Ia"
   MSFlexGrid.TextMatrix(1,3) = "Ib"
   MSFlexGrid.TextMatrix(1,4) = "Ic"
   MSFlexGrid.TextMatrix(1,5) = "p"
   MSFlexGrid.TextMatrix(1,6) = "EA"
   MSFlexGrid.TextMatrix(1,7) = "PF"
   MSFlexGrid.TextMatrix(1,8) = "Uab"
   MSFlexGrid.TextMatrix(1,9) = "Ubc"
   MSFlexGrid.TextMatrix(1,10) = "Uca"
   MSFlexGrid.TextMatrix(1,11) = "Va"
   MSFlexGrid.TextMatrix(1,12) = "Vb"
   MSFlexGrid.TextMatrix(1,13) = "Vc"
   MSFlexGrid.TextMatrix(1,14) = "Q"
   MSFlexGrid.TextMatrix(1,15) = "ER"

 
  
   MSFlexGrid.ColAlignment(0) = 4		'对其方式为居中对齐
   MSFlexGrid.ColAlignment(1) = 4
   MSFlexGrid.ColAlignment(2) = 4
   MSFlexGrid.ColAlignment(3) = 4
   MSFlexGrid.ColAlignment(4) = 4
   MSFlexGrid.ColAlignment(5) = 4
   MSFlexGrid.ColAlignment(6) = 4		'对其方式为居中对齐
   MSFlexGrid.ColAlignment(7) = 4
   MSFlexGrid.ColAlignment(8) = 4
   MSFlexGrid.ColAlignment(9) = 4
   MSFlexGrid.ColAlignment(10) = 4
   MSFlexGrid.ColAlignment(11) = 4
   MSFlexGrid.ColAlignment(12) = 4
   MSFlexGrid.ColAlignment(13) = 4
   MSFlexGrid.ColAlignment(14) = 4
   MSFlexGrid.ColAlignment(15) = 4

 	
 If (n > 0)Then 
   MSFlexGrid.Rows = n/Tag_num + 3  '提前将行数给够，否则会出错，2是加上标题两行
    objRecordset.MoveFirst		'移动指针到检索结果的第一行
    i = 1
    pre_time = ""
    While Not  objRecordset.eof 	'是否到达记录的末尾，循环填写表格
        	var = objRecordset.Fields(3).Value
    		If(objRecordset.Fields(1).Value = pre_time)Then
    			MSFlexGrid.TextMatrix(i,0) = i-1	'将查询到的每一行结果写入表格中
        		MSFlexGrid.TextMatrix(i,1) = objRecordset.Fields(1).Value
    			Select Case var
				Case "{"+Ia+"}"
					MSFlexGrid.TextMatrix(i,2) = objRecordset.Fields(2).Value
				Case "{"+Ib+"}"
					MSFlexGrid.TextMatrix(i,3) = objRecordset.Fields(2).Value
				Case "{"+Ic+"}"
					MSFlexGrid.TextMatrix(i,4) = objRecordset.Fields(2).Value
				Case "{"+p+"}"
					MSFlexGrid.TextMatrix(i,5) = objRecordset.Fields(2).Value
				Case "{"+EA+"}"
					MSFlexGrid.TextMatrix(i,6) = objRecordset.Fields(2).Value
				Case "{"+PF+"}"
					MSFlexGrid.TextMatrix(i,7) = objRecordset.Fields(2).Value
				Case "{"+Uab+"}"
					MSFlexGrid.TextMatrix(i,8) = objRecordset.Fields(2).Value
				Case "{"+Ubc+"}"
					MSFlexGrid.TextMatrix(i,9) = objRecordset.Fields(2).Value
				Case "{"+Uca+"}"
					MSFlexGrid.TextMatrix(i,10) = objRecordset.Fields(2).Value
				Case "{"+Va+"}"
					MSFlexGrid.TextMatrix(i,11) = objRecordset.Fields(2).Value
				Case "{"+Vb+"}"
					MSFlexGrid.TextMatrix(i,12) = objRecordset.Fields(2).Value
				Case "{"+Vc+"}"
					MSFlexGrid.TextMatrix(i,13) = objRecordset.Fields(2).Value
				Case "{"+Q+"}"
					MSFlexGrid.TextMatrix(i,14) = objRecordset.Fields(2).Value
				Case "{"+ER+"}"
					MSFlexGrid.TextMatrix(i,15) = objRecordset.Fields(2).Value
			End Select
    		Else
    			i = i + 1
    			pre_time = objRecordset.Fields(1).Value
    			MSFlexGrid.TextMatrix(i,0) = i-1	'将查询到的每一行结果写入表格中
    			Select Case var
				Case "{"+Ia+"}"
					MSFlexGrid.TextMatrix(i,2) = objRecordset.Fields(2).Value
				Case "{"+Ib+"}"
					MSFlexGrid.TextMatrix(i,3) = objRecordset.Fields(2).Value
				Case "{"+Ic+"}"
					MSFlexGrid.TextMatrix(i,4) = objRecordset.Fields(2).Value
				Case "{"+p+"}"
					MSFlexGrid.TextMatrix(i,5) = objRecordset.Fields(2).Value
				Case "{"+EA+"}"
					MSFlexGrid.TextMatrix(i,6) = objRecordset.Fields(2).Value
				Case "{"+PF+"}"
					MSFlexGrid.TextMatrix(i,7) = objRecordset.Fields(2).Value
				Case "{"+Uab+"}"
					MSFlexGrid.TextMatrix(i,8) = objRecordset.Fields(2).Value
				Case "{"+Ubc+"}"
					MSFlexGrid.TextMatrix(i,9) = objRecordset.Fields(2).Value
				Case "{"+Uca+"}"
					MSFlexGrid.TextMatrix(i,10) = objRecordset.Fields(2).Value
				Case "{"+Va+"}"
					MSFlexGrid.TextMatrix(i,11) = objRecordset.Fields(2).Value
				Case "{"+Vb+"}"
					MSFlexGrid.TextMatrix(i,12) = objRecordset.Fields(2).Value
				Case "{"+Vc+"}"
					MSFlexGrid.TextMatrix(i,13) = objRecordset.Fields(2).Value
				Case "{"+Q+"}"
					MSFlexGrid.TextMatrix(i,14) = objRecordset.Fields(2).Value
				Case "{"+ER+"}"
					MSFlexGrid.TextMatrix(i,15) = objRecordset.Fields(2).Value
			End Select	
		End If	
		objRecordset.MoveNext	
    Wend   
    MSFlexGrid.TopRow = MSFlexGrid.Rows - 1	'移动到最后一行

End If
End Sub

这篇关于MSFlexGrid与online trendControl 访问局域网内远程数据库的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！

C/C++教程

MSFlexGrid与online trendControl 访问局域网内远程数据库

项目场景：

数据库配置：

代码展示：

前端开发

后端开发

移动端开发

数据库

服务器运维

人工智能

区块链

游戏开发

网站运营

大数据/云计算

软件工程

软件/开发工具使用

资讯