一、聲名全局變量
在全局腳本中聲明全局變量，如下：Public gocn
2、建立連接，如下：
Set gocn = CreateObject("ADODB.Connection")
gocn.open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=GJS_SY;Data Source=LGL"
3、執行代碼。例如插入一條記錄：
gocn.Execute "insert into TB_FJ_DATA(LSH,GKH,T_time) VALUES('1',99,GETDATE( ))"
二、訪問數據記錄和查詢
1、查詢。代碼如下：
On Error Resume Next
Dim NoOfRecords
Set NoOfRecords = HMIRuntime.Tags("NoOfRecords")
Dim CurrentRec
Set CurrentRec = HMIRuntime.Tags("CurrentRec")
Dim AuthorID
Set AuthorID = HMIRuntime.Tags("Au_ID")
Dim AuthorFirstName
Set AuthorFirstName = HMIRuntime.Tags("AuthorFirstName")
Dim AuthorLastName
Set AuthorLastName = HMIRuntime.Tags("AuthorLastName")
Dim AuthorsQueryRan
Set AuthorsQueryRan = HMIRuntime.Tags("AuthorsQueryRan")
ActivateDBHandling()
If gocn.State = 0 Then
HMIRuntime.Trace "no connection ... reconnecting"
ConnectDB()
End If
gorsAuthors.open "SELECT au_id, au_fname, au_lname, contract FROM Authors ORDER BY au_lname", gocn, 1, 3
If Not gorsAuthors.Eof Then
NoOfRecords.Write gorsAuthors.RecordCount
gorsAuthors.MoveFirst
AuthorID.Write gorsAuthors.Fields(0).Value
AuthorFirstName.Write gorsAuthors.Fields(1).Value
AuthorLastName.Write gorsAuthors.Fields(2).Value
CurrentRec.Write 1
AuthorsQueryRan.Write 1
Else
MsgBox "No Data Returned", vbOK, "Query Results"
End If
On Error Goto 0
2、插入
On Error Resume Next
Dim AuthorID
Set AuthorID = HMIRuntime.Tags("Au_ID_INSERT")
Dim AuthorFirstName
Set AuthorFirstName = HMIRuntime.Tags("AuthorFirstName_INSERT")
Dim AuthorLastName
Set AuthorLastName = HMIRuntime.Tags("AuthorLastName_INSERT")
Dim sSql
ActivateDBHandling()
If gocn.State = 0 Then
HMIRuntime.Trace "no connection ... reconnecting"
ConnectDB()
End If
Set gorsAuthorsChange = CreateObject("ADODB.Recordset")
sSql = "INSERT INTO Authors(au_id, au_fname, au_lname, contract) VALUES('" + AuthorID.Read + "','" + AuthorFirstName.Read + "', '" + AuthorLastName.Read + "',1)"
gorsAuthorsChange.open sSql, gocn, 1
MsgBox "Added new author", vbOKOnly, "INSERT"
Set gorsAuthorsChange = Nothing
On Error Goto 0
3、修改
On Error Resume Next
Dim AuthorID
Set AuthorID = HMIRuntime.Tags("Au_ID_UPDATE")
Dim AuthorFirstName
Set AuthorFirstName = HMIRuntime.Tags("AuthorFirstName_UPDATE")
Dim AuthorLastName
Set AuthorLastName = HMIRuntime.Tags("AuthorLastName_UPDATE")
Dim sSql
ActivateDBHandling()
If gocn.State = 0 Then
HMIRuntime.Trace "no connection ... reconnecting"
ConnectDB()
End If
Set gorsAuthorsChange = CreateObject("ADODB.Recordset")
sSql = "UPDATE Authors SET au_fname = '" + AuthorFirstName.Read + "', au_lname = '" + AuthorLastName.Read + "' WHERE au_id = '" + AuthorID.Read + "'"
gorsAuthorsChange.open sSql, gocn, 1
MsgBox "Modified author details", vbOKOnly, "UPDATE"
Set gorsAuthorsChange = Nothing
On Error Goto 0
4、刪除
On Error Resume Next
Dim AuthorID
Set AuthorID = HMIRuntime.Tags("Au_ID_DELETE")
Dim sSql
ActivateDBHandling()
If gocn.State = 0 Then
HMIRuntime.Trace "no connection ... reconnecting"
ConnectDB()
End If
Set gorsAuthorsChange = CreateObject("ADODB.Recordset")
sSql = "DELETE FROM Authors WHERE au_id = '" + AuthorID.Read + "'"
gorsAuthorsChange.open sSql, gocn, 1
MsgBox "Deleted author", vbOKOnly, "DELETE"
Set gorsAuthorsChange = Nothing
On Error Goto 0
更多技術資料，請加微信公眾帳號“gongkong8”
工控技術博客： http://blog.sina.com.cn/wincchome
啟程自動化培訓中心 http://www.gongkong8.com/
聯系電話：0755-85292922
