Updating Records in a Database

Microsoft® Windows® 2000 Scripting Guide

Scripts often need to update existing records rather than add new records to a database. For example, with an inventory script you do not want to add a new record each time the script is run. If you did, you would end up with multiple records for the same computer. Instead, you want to update the existing record for each computer, replacing the old inventory data with the newly retrieved data.

To update a record by using ADO, do the following:

1. Connect to the recordset.

2. Connect to the appropriate record. This is typically done by using the Find method to locate an individual record.

3. Set the new values as needed.

4. Use the Update method to apply the changes to the database.

Scripting Steps

Listing 17.18 contains a script that updates a record in a database. To carry out this task, the script must perform the following steps:

1. Create three constants - adOpenStatic, adLockOptimistic, and adUseClient - and set the value of each to 3.

   These constants will be used to configure the CursorLocation, CursorType, and LockType for the connection.

2. Create an instance of the ADO Connection object (ADODB.Connection).

   The Connection object makes it possible for you to issue queries and other database commands.

3. Create an instance of the ADO Recordset object (ADODB.Recordset).

   The Recordset object stores the data returned from your query.

4. Use the Connection object Open method to open the database with the DSN Inventory.

   Be sure to append a semicolon (;) to the DSN name.

5. Set the CursorLocation to 3 (client side) by using the constant adUseClient.

6. Use the Recordset object Open method to retrieve all the records from the Hardware table.

   The Open method requires four parameters:

   • The SQL query ("SELECT * FROM Hardware")

   • The name of the ADO connection being used (objConnection)

   • The cursor type (adOpenStatic)

   • The lock type (adLockOptimistic)

7. Set the variable strSearchCriteria to ComputerName = 'WebServer'.

   The variable strSearchCriteria will serve as the search criteria for the Find method.

8. Use the Find method to locate the computer named WebServer.

9. Use a GetObject call to connect to the WMI namespace root\cimv2, and set the impersonation level to "impersonate."

10. Use the ExecQuery method to query the Win32_SoundDevice class.

    This query returns a collection consisting of all the sound cards installed on the computer.

11. For each sound card in the collection, use the values obtained from the Win32_SoundDevice class to update the database fields ComputerName, Manufacturer, and ProductName.

12. Use the Update method to write the new record to the database.

13. Close the recordset.

14. Close the connection.

Listing 17.18 Updating Records in a Database

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "DSN=Inventory;"
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT * FROM Hardware" , objConnection, _
 adOpenStatic, adLockOptimistic
strSearchCriteria = "ComputerName = 'WebServer'"
objRecordSet.Find strSearchCriteria
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
 & "{impersonationLevel=impersonate}!\\" & strComputer& "\root\cimv2")
Set colSoundCards = objWMIService.ExecQuery _
 ("SELECT * FROM Win32_SoundDevice")
For Each objSoundCard in colSoundCards
 objRecordset("ComputerName") = objSoundCard.SystemName
 objRecordset("Manufacturer") = objSoundCard.Manufacturer
 objRecordset("ProductName") = objSoundCard.ProductName
 objRecordset.Update
Next
objRecordset.Close
objConnection.Close