Adding New Records to a Database

Microsoft® Windows® 2000 Scripting Guide

« Previous | Next »   

Many enterprise scripts are designed to take data from multiple sources (for example, all the event logs on all your domain controllers) and then combine that information into a single database. This is typically done for two reasons: It creates a central repository for this data, and it makes it possible for you to use database tools to analyze the data.

To copy information to a database, your script must create a new record for each piece of information retrieved. (For example, a new record must be added to the database for each event retrieved from an event log.)

You use the AddNew method to add new records to a database. After you have opened a connection to a database and a recordset, do the following:

1. Call the AddNew method.
2. Specify the name of each field and the value for the new record. For example, if the database includes a field named ComputerName and you want to add a new computer named WebServer, use code similar to the following:

   objRecordset("ComputerName") = "WebServer" 

3. Use the Update method to write the new record to the database. The AddNew method by itself does not save the new record.

You can use the AddNew method only if the recordset can be updated. If you opened the recordset in read-only mode, any attempt to add a new record will fail.

Scripting Steps

Listing 17.16 contains a script that adds a new record the name of a sound card to 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 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. Use a GetObject call to connect to the WMI namespace root\cimv2, and set the impersonation level to "impersonate."
8. 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.

9. For each sound card in the collection, use the AddNew method to create a record for that sound card in the database.
10. For each sound card, use the values obtained from the Win32_SoundDevice class to update the database fields ComputerName, Manufacturer, and ProductName.
11. Use the Update method to write the new record to the database.
12. Close the recordset.
13. Close the connection.

Listing 17.16   Adding New Records to 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 

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 strComputer = "." Set objWMIService = GetObject("winmgmts:" _     & "{impersonationLevel=impersonate}!\\" & strComputer& "\root\cimv2") Set colSoundCards = objWMIService.ExecQuery _     ("SELECT * FROM Win32_SoundDevice") For Each objSoundCard in colSoundCards     objRecordset.AddNew     objRecordset("ComputerName") = objSoundCard.SystemName     objRecordset("Manufacturer") = objSoundCard.Manufacturer     objRecordset("ProductName") = objSoundCard.ProductName     objRecordset.Update Next objRecordset.Close objConnection.Close

---

 Send us your feedback

« Previous | Next »