Add Records to a Database with VBScript

發行項
08/18/2016

嗨，Scripting Guy！重返資料庫指令碼

The Microsoft Scripting Guys

下載本文程式碼: HeyScriptingGuy2008_04.exe (151KB)

大家都知道，如果說 Scripting Guy 有什麼弱點 (拜託，我們只是假設而已)，那就是：我們對於有做和沒做的事可能的後果過於憂心。我們不是像為了自己的利益而小心翼翼地採取行動，而總是為了眾人的利益而打拼。而且無論採取的行動對我們會產生什麼影響，我們仍然勇往直前。

就以資料庫指令碼的相關文章為例。坦白說，要寫關於資料庫指令碼的文章，對 Scripting Guy 來說並不容易。但原因並不是因為資料庫指令碼很難寫；資料庫指令碼其實蠻簡單的。問題在於，Scripting Guy 沒什麼機會處理資料庫；因此每次寫到資料庫的相關主題時，我們都得要停下來思考我們的行動。您可能早已經發現，要成為 Scripting Guy，思考絕對不是必要條件。

事實上，如果當初有人深思熟慮過，今天就沒有 Scripting Guy 了。

既然撰寫有關資料庫指令碼的文章對 Scripting Guy 來說這麼困難，為什麼我們還要繼續寫呢？答案很簡單：我們擔心如果我們不再寫這些文章，整個世界可能發生什麼不好的事。當推理作家科南道爾決定處死福爾摩斯 (讓他在《最後一案》中墜崖)，還以為只不過是擺脫了一個不想再寫的角色。但是，這麼做引起舉世激憤。據報導指出，真的有些倫敦市民在聽到消息後換上喪服。《最後一案》於 1893 年發表在史傳德雜誌 (The Strand Magazine) 上；為此，20,000 名讀者取消訂閱該雜誌。

這可不妙。

Scripting Guy 擔心如果停筆不寫有關資料庫指令碼的文章，可能也會導致類似的全球效應。我們不想讓這麼多人痛苦絕望，更不希望 20,000 名讀者取消訂閱《TechNet Magazine》。有鑑於此，我們宣布 Scripting Guy 將繼續撰寫有關資料庫指令碼的文章。何時呢？不如就現在吧！

將記錄加入資料庫

本月我們打算示範一些簡潔的小技巧來處理資料庫。當然，這些技巧不同於我們曾在 2007 年 9 月份專欄 (https://technet.microsoft.com/zh-tw/magazine/cc137809.aspx) 中討論過的技巧；就算是 Scripting Guy 也不至於卑劣到將完全相同的文章重複發稿兩次。

當然啦，除非我們有脫身之計，才會這麼做。

一開始，我們先教您一個簡單的方法將記錄加入資料庫中。假設有一個名為 C:\Scripts\Inventory.mdb 的資料庫，而且此資料庫中包含一個名為 Computers 的資料表。Computers 資料表包含下列欄位：

ComputerName
SerialNumber
UserName
Department

現在，我們要如何將新的電腦加入到該資料庫中？請看一下 [圖 1] 中的程式碼。

Figure 1 Adding a record to the Computers table

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = C:\Scripts\Inventory.mdb"

objRecordSet.Open "INSERT INTO Computers (ComputerName, SerialNumber, UserName, Department)" &  _
    "VALUES ('atl-ws-07', '11234', 'Ken Myer', 'Finance')", _
        objConnection, adOpenStatic, adLockOptimistic

這段指令碼的前幾行我們不討論；如果您需要相關的背景資訊，您可以參考指令碼中心提供的一些資源，網址是：https://www.microsoft.com/taiwan/technet/scriptcenter/default.mspx。重點是我們使用常數 adOpenStatic 和 adLockOptimistic 來控制資料錄集的指標類型和記錄鎖定 (的確，聽起來很了不起，但其實都是一些簡單的東西)。建立 ADODB.Connection 和 ADODB.Recordset 物件的執行個體後，接著使用以下命令來開啟資料庫：

objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " & _
"Data Source = C:\Scripts\Inventory.mdb"

附帶一提，該命令會開啟 Microsoft® Access® 2003 資料庫。若要開啟 Access 2007 資料庫，請改用以下命令：

objConnection.Open _
"Provider = Microsoft.ACE.OLEDB.12.0; " & _
"Data Source = C:\Scripts\Inventory.accdb"

雖然一開始有點偏離主題，不過您可以使用與下面類似的命令來開啟 SQL Server® 資料庫：

objConnection.Open _
"Provider=SQLOLEDB;" & _
"Data Source=atl-sql-01;" & _
"Trusted_Connection=Yes;" & _ "InitialCatalog=Inventory;" & _
"User ID=fabrikam\kenmyer;Password=34DE6t4G!;"

到目前為止，我們所示範的都是現成的程式碼；您馬上就會看到，幾乎所有資料庫指令碼都是以這幾行程式碼起頭。我們想要強調的是下行程式碼：

objRecordSet.Open _
  "INSERT INTO Computers " & _
  "(ComputerName, SerialNumber, " & _
  "UserName, Department) " & _
    "VALUES ('atl-ws-07', 'A14B1234', " & _
      "'Ken Myer', 'Finance')", _
        objConnection, adOpenStatic, _
        adLockOptimistic

在此，我們使用 INSERT INTO 查詢將新記錄插入到 Computers 資料表中。請注意，緊接在資料表名稱後面的是所有具有值的欄位，我們使用逗號分隔這些欄位名稱，並將這些名稱都放入括號內。

您可能會問：Scripting Guy 所說的「所有具有值的欄位」是什麼意思？問得好。假設這台新電腦尚未指派給 Department。假定資料庫可讓我們在 Department 欄位輸入 Null 值，我們就可以直接在清單中忽略 Department，而且不指派值給它。換句話說，我們可以撰寫如下的查詢：

objRecordSet.Open _
  "INSERT INTO Computers " & _
  "(ComputerName, SerialNumber, " & _
  "UserName) " & _
    "VALUES ('atl-ws-07', 'A14B1234', " & _
      "'Ken Myer')", _
        objConnection, adOpenStatic, _
        adLockOptimistic

說到指派值，欄位名稱後緊接著是什麼：VALUES 關鍵字再加上我們要為各個欄位指派的值 (這些值也會以括號括住)。請記住，當我們列出欄位名稱時，這些名稱可能以任何順序排列；比方說，我們把 ComputerName 放在第一位，雖然它可能不是資料庫中的第一個欄位。這對欄位名稱沒差，但是值可就不行。值必須完全依照欄位順序排列。如果第一個列出的欄位是 ComputerName，那麼第一個值必須是電腦名稱。不然就麻煩大了 (舉例來說，我們最後可能會將使用者名稱或序號指派到 ComputerName 欄位)。

您已經看到了，指派值並不複雜；只要確保您是根據資料類型而使用值格式就行了：日期和字串值應該包含在單引號中；數字和布林值則不應該包含在單引號中。

對了，如果您的值自己已經有單引號了 (例如名稱 O'Brien)，您就必須用兩個單引號來「逸出」：

'O''Brien'

出乎意料吧，但這是真的。

從資料庫刪除記錄

實在蠻酷的：我們只要執行單一 SQL 查詢就可以將記錄加入資料庫。還有什麼比這更棒的？據我們所知是沒有。

呃，除非能夠使用單一查詢來刪除資料庫的多筆記錄。

假設您的公司決定消滅人事部 (這不是開玩笑的，我們跟 Microsoft 人事部之間的麻煩事還真不少)。您如何從 Inventory 資料庫刪除所有 Human Resources 電腦？[圖 2] 顯示方法之一。

Figure 2 Deleting multiple records

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = C:\Scripts\Inventory.mdb"

objRecordSet.Open "DELETE * FROM Computers Where Department='Human Resources'", _
    objConnection, adOpenStatic, adLockOptimistic

如您所見，此特定指令碼的開頭與我們剛才加入新記錄的指令碼一模一樣：定義一些常數，建立幾個物件，然後連接到 Inventory.mdb 資料庫。接著我們直接使用 DELETE 查詢來刪除 Computers 資料表中的所有記錄 (*)，否則至少要刪除 Department 欄位等於 Human Resources 的所有記錄：

objRecordSet.Open _
  "DELETE * FROM Computers " & _
   "Where Department='Human Resources'", _
    objConnection, adOpenStatic, _
    adLockOptimistic

這就跟掉下懸崖一樣簡單。

無冒犯之意，福爾摩斯。

更新資料庫中的記錄

相信每個人都同意，刪除人事部的所有記錄真是其樂無窮 (僅致 Microsoft 人事部：我們是指對其他人和其他公司的人事部來說很有趣，不是我們，我們覺得一點兒都不好玩)。然而，從 Inventory 資料庫刪除所有 HR 電腦可能不是最佳的作法。為什麼？因為除非人事部員工帶著所有硬體潛逃 (聽起來正像他們的所作所為 — 算了，講好不開玩笑的)，那些電腦仍屬於貴公司；也就是說，那些電腦還是應該列入資料庫中。知道這一點之後，就來著手進行。我們不從資料庫刪除這些記錄，而只是更新每筆記錄；我們要直接將這些電腦的部門名稱從 Human Resources 改為 None。換言之，我們將執行如 [圖 3] 所示的指令碼。

Figure 3 Updating records

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = C:\Scripts\Inventory.mdb"

objRecordSet.Open "UPDATE Computers Set Department = 'None' " & _
    "Where Department = 'Human Resources'", _
    objConnection, adOpenStatic, adLockOptimistic

同樣地，這段程式碼中唯一的差異就是 SQL 查詢。在本例中，我們要使用 UPDATE 查詢來更新 Computers 資料表中的記錄。除了 UPDATE 關鍵字以外，我們還需要加入兩個參數，告訴查詢如何更新記錄和更新哪些記錄：

Set Department = 'None'，這只是指定 Department 欄位的新值。實際上，您可以使用類似下面的語法，同時設定不只一個欄位值：Set Department = 'None', UserName = 'None'。誠如您所看到的，這麼做會同時將 Department 和 UserName 欄位設為 None。
Where Department = 'Human Resources' 是標準的 Where 子句，表示要更新的記錄；在此，這代表所有 Department 欄位等於 Human Resources 的記錄。之所以要加入這個子句，原因很簡單：我們不是要修改資料庫中的所有記錄，而只要修改原先屬於人事部的記錄 (電腦)。

您可用 Update 查詢做一件蠻酷的事情，假設貴公司為了因應物價上漲，給每位員工加薪 10% (Scripting Guy 顯然與科南道爾有一點很像：我們的作品經常出現虛構的故事)。下面的指令碼可開啟名為 Employees 的資料表，並修改每位員工的 Salary 欄位。但是它要將 Salary 欄位設為什麼呢？查詢會將此欄位設為員工目前的薪水乘以 1.1；換句話說，也就是為每位員工加薪 10% (有這麼容易就好了！)請看看以下的查詢：

objRecordSet.Open _
  "Update Employees " & _
  "Set Salary = (Salary * 1.1)", _
    objConnection, adOpenStatic, _
    adLockOptimistic

這到底棒在哪裡呢？如您所見，您可以在 Update 查詢中進行計算。要是您決定將所有短期雇員的合約延長 60 天呢？此時，類似下面的查詢就派上用場了：

objRecordSet.Open _
  "Update TempEmployees " & _
  "Set ContractExpirationDate = " & _
  "(ContractExpirationDate + 60)", _
    objConnection, adOpenStatic, _
    adLockOptimistic

擷取資料的好方法

既然談到方便好用的查詢，我們就一鼓作氣介紹一些從資料庫擷取資料的好方法。舉例來說，有一個簡單但非常好用的指令碼。假設我們的 Inventory 資料庫包含名為 Price 的欄位，這個欄位理所當然地代表電腦的價格。想要知道公司中最貴的前五台電腦嗎？[圖 4] 中的範例程式碼可以告訴您答案。

Figure 4 Sorting the recordset

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = C:\Scripts\Test.mdb" 

objRecordSet.Open "SELECT Top 5 * FROM Computers Order By Price", _
     objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

Do Until objRecordset.EOF
    Wscript.Echo objRecordset.Fields.Item("ComputerName")
    objRecordset.MoveNext
Loop

objRecordSet.Close
objConnection.Close

您可以看到，在這個 SQL 查詢中我們做了兩件事：我們按照 Price 排序資料錄集 (Order By Price)，而且只要求取得前五名電腦 (也就是最貴的五台電腦)；SELECT Top 5 就是負責執行這個工作。若要取得前 10 名最貴電腦的清單，我們會使用以下查詢：

objRecordSet.Open _
  "SELECT Top 10 * FROM Computers " & _
  "Order By Price", _
    objConnection, adOpenStatic, _
    adLockOptimistic

注意：我們也加入了可遞迴處理資料錄集並顯示各台電腦名稱的程式碼。前面的指令碼不需要此程式碼，因為那些指令碼並不會傳回和顯示資料。

或者，我們可以使用以下查詢來取得前 10% 的結果：

objRecordSet.Open _
  "SELECT Top 10 PERCENT * " & _
  "FROM Computers Order By Price", _
    objConnection, adOpenStatic, _
    adLockOptimistic

現在，假如我們想要知道最便宜的電腦呢？沒問題；只要使用相同的方法，但這次我們是以遞減順序來排序資料錄集 (也就是從最低價排到最高價)。換句話說，我們使用這個查詢再加上 DESC 來指定以遞減順序排序資料錄集：

objRecordSet.Open _
  "SELECT Top 5 * FROM Computers " & _
  "Order By Price DESC", _
    objConnection, adOpenStatic, _
    adLockOptimistic

看到了嗎？早就說很好用吧。

另外還有兩件事您可能會感興趣。假設您的資料庫包含名為 Budgeted 的欄位，用來追蹤電腦原本的預算金額。想要比較實際成本與預算金額嗎？此查詢會傳回成本高於預算金額的電腦清單：

objRecordSet.Open _
  "SELECT * FROM Computers " & _
  "Where (Budgeted < Price)", _
    objConnection, adOpenStatic, _
    adLockOptimistic

此外，這項查詢還會計算所有電腦的平均價格 (SELECT AVG(Price) FROM Computers)，然後傳回所有成本低於平均價格的電腦清單：

objRecordSet.Open _
  "SELECT * FROM Computers " & _
  "WHERE Price < " & _
  "(SELECT AVG(Price) FROM Computers)", _
    objConnection, adOpenStatic, _
    adLockOptimistic

您說的沒錯，我們的確在 Where 子句中使用了 SELECT 查詢。究竟是怎麼運作的？說來話長，請待下期分曉。不過如您所見，本文的指令碼為我們開啟一扇大門，讓您利用 SQL 查詢擷取各式各樣有趣的資訊。

這個故事的寓意

我們在本月專欄一開始就說過了，我們不知道是否該寫另一篇關於資料庫指令碼的文章。不過我們很高興這麼做了，一部份的原因是我們相信您會覺得這些查詢很有用，另一部分的原因則是：如果現在不寫這篇專欄，遲早也要寫。畢竟，就連科南道爾也被逼得讓福爾摩斯死而復生。科南道爾先寫了一篇據說是發生在神探死亡之前的新的福爾摩斯故事，藉此試探風聲。不過由此以後，他就屈服於廣大讀者的壓力，編造出福爾摩斯裝死這樣的薄弱理由。結果是每個人、每件事都安然無恙。

有趣的是，推出最新的福爾摩斯故事後，又有 30,000 名新讀者訂閱史傳德雜誌 (The Strand Magazine)。心懷感激的英美出版商因此讓科南道爾成為世界上稿費最高的作家。

嗯，科南道爾帶回了眾人深愛的角色，成為全世界稿酬最高的作家；現在 Scripting Guy 帶回了大家最愛的主題 — 還不必裝死。不曉得《TechNet Magznie》那些仁慈的同事注意到沒？

Dr. Scripto 的指令碼謎題 (Scripting Perplexer)

這個每月一次的挑戰不僅測試您的解謎功力，更要測試您的指令碼技巧。

2008 年 4 月：選個字母

在本月的謎題中，您必須在藍色空格中插入 A 到 O 的字母，以便顯示 VBScript 函數的名稱。每個字母 (A 到 O) 都只能使用一次，而且這些字母不會按照字母順序顯示。插入的字母可以是函數名稱開頭、函數名稱結尾，或是函數名稱中的任何位置。比方說，在下行中您會插入字母 D 來完成函數名稱 IsDate：

(按影像可放大)

現在您可以試試看；插入字母 A 到 O 來顯示格子中的 VBScript 函數名稱：

**** (按影像可放大)

ANSWER:

Dr. Scripto 的指令碼謎題 (Scripting Perplexer)

答案是：選個字母，2008 年 4 月

(按影像可放大)

The Microsoft Scripting Guys 為 Microsoft 做事，也就是受雇於 Microsoft。他們在不玩、不教或不看棒球 (或者其他各種活動) 的時候，就負責管理 TechNet 指令碼中心。請造訪他們的網站：https://www.microsoft.com/taiwan/technet/scriptcenter/default.mspx。

Share via

其他資源