博图VBS连接SQLServer

Sub VBFunction_Setting()
'提示：
' 1. 使用 <CTRL+SPACE> 或 <CTRL+I> 快捷键打开含所有对象和函数的列表
' 2. 使用 HMI Runtime 对象写入代码。
' 示例：HmiRuntime.Screens("Screen_1")。
' 3. 使用 <CTRL+J> 快捷键创建对象引用。
'从此位置起写入代码：
If SmartTags("IsLoadSettingVal") = True Then
Dim conn '定义类对象
Dim SCon '定义数据库连接字符串
Dim oRs1 '定义获取到的数据集
Dim oCom
Dim strSQL1
Dim Datetime,Count,Power
'---------打开数据库 -----------'
SCon= "Provider=SQLOLEDB; Integrated Security =SSPI;Persist Security Info=True;Initial Catalog=PlanCheck;Data Source=DESKTOP-OCMKPGO;uid=sa;pwd=password"
Set conn=CreateObject("ADODB.Connection")
conn.ConnectionString = SCon
conn.CursorLocation = 3
conn.Open
Set oRs1 = CreateObject("ADODB.Recordset")
Set oCom = CreateObject("ADODB.Command")
oCom.CommandType = 1

'读取误差值
strSQL1 = "SELECT id,iItem_name,item_value FROM setting WHERE iItem_name = 'error_val'"
Set oCom.ActiveConnection = conn
oCom.CommandText = strSQL1
Set oRs1 = oCom.Execute
SmartTags("ErrorVal") = oRs1.fields("item_value").value

'读取A面Z的设定值
Dim oRsAZ
oCom.CommandText = "SELECT id,iItem_name,item_value FROM setting WHERE iItem_name LIKE 'A_Z%' ORDER BY iItem_name ASC"
Set oRsAZ = CreateObject("ADODB.Recordset")
Set oRsAZ = oCom.Execute
Dim dataCount
dataCount = oRsAZ.RecordCount
oRsAZ.movefirst
Dim index
For index = 1 To dataCount
Dim tagName
tagName = "A_Z"&index
SmartTags(tagName) = oRsAZ.fields("item_value").value
oRsAZ.movenext
Next

'读取B面Y的设定值
Dim oRsBY
oCom.CommandText = "SELECT id,iItem_name,item_value FROM setting WHERE iItem_name LIKE 'B_Y%' ORDER BY iItem_name ASC"
Set oRsBY = CreateObject("ADODB.Recordset")
Set oRsBY = oCom.Execute
dataCount = oRsBY.RecordCount
oRsBY.movefirst
For index = 1 To dataCount
tagName = "B_Y"&index
SmartTags(tagName) = oRsBY.fields("item_value").value
oRsBY.movenext
Next

'读取C面Y的设定值
Dim oRsCY
oCom.CommandText = "SELECT id,iItem_name,item_value FROM setting WHERE iItem_name LIKE 'C_Y%' ORDER BY iItem_name ASC"
Set oRsCY = CreateObject("ADODB.Recordset")
Set oRsCY = oCom.Execute
dataCount = oRsCY.RecordCount
oRsCY.movefirst
For index = 1 To dataCount
tagName = "C_Y"&index
SmartTags(tagName) = oRsCY.fields("item_value").value
oRsCY.movenext
Next

'读取D面Z的设定值
Dim oRsDZ
oCom.CommandText = "SELECT id,iItem_name,item_value FROM setting WHERE iItem_name LIKE 'D_Z%' ORDER BY iItem_name ASC"
Set oRsDZ = CreateObject("ADODB.Recordset")
Set oRsDZ = oCom.Execute
dataCount = oRsDZ.RecordCount
oRsDZ.movefirst
For index = 1 To dataCount
tagName = "D_Z"&index
SmartTags(tagName) = oRsDZ.fields("item_value").value
oRsDZ.movenext
Next

 

'---------关闭数据库-----------'
Set oRsAZ = Nothing
Set oRsBY = Nothing
Set oRsCY = Nothing
Set oRsDZ = Nothing
Set oRs1 = Nothing
Set oCom = Nothing
conn.Close
Set conn = Nothing
SmartTags("IsLoadSettingVal") = False
End If
End Sub