Finding Records in a Recordset

  • Article

Microsoft® Windows® 2000 Scripting Guide

Many enterprise scripts require you to perform an action on multiple records in the database. For example, you might have a script that updates the hardware inventory for a set of computers. The script needs to connect to the database, locate the record for the first computer, and apply any required updates. The script must then repeat the process for each computer being inventoried.

It is possible to create an SQL query that returns only the name of computer being updated (for example, "SELECT * FROM Inventory WHERE ComputerName = 'WebServer'"). However, if your script must update 100 computers, this would require 100 separate SQL queries being passed to the database server, one for each computer being updated. A more efficient approach might be to download the entire set of computers and then use the Recordset object Find method to locate each computer as needed.

The Find method requires two steps:

  1. Set the criteria for the method. For example, to find a computer named WebServer, set the criteria as follows:

    strSearchCriteria = "ComputerName = 'WebServer'"

  2. Apply the method. For example:

    objRecordSet.Find strSearchCriteria

You can use the Recordset object EOF (end-of-file) property to determine whether a record was found. If EOF is True, this means the recordset has been searched from beginning to end and no record was found. This is important because your script will fail if it attempts to take action on a record that does not exist. For example, you might use the Find method to locate a particular record and then use the Delete method to delete that record. If the record does not exist, however, both the Delete method and your script will fail.

Using the Find method also helps you eliminate duplicate records within a database. For example, when creating an inventory of your computers, you probably want one record per computer; you do not want multiple records for the computer WebServer. To prevent the possibility of creating duplicate records, follow a procedure similar to this:

  1. Take the inventory on a computer (for example, WebServer).

  2. Search the recordset for a computer named WebServer.

    • If a computer named WebServer is found, update the record with the current inventory information.

    • If a computer named WebServer is not found, use the AddNew method to create a record for WebServer.

Scripting Steps

Listing 17.17 contains a script that finds a record in a recordset. 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 the EOF property to verify whether the record can be found:

    • If the record cannot not be found (because EOF = True), echo the string, "Record cannot be found."

    • If the record is found (because EOF = False), echo the string, "Record found."

  10. Close the recordset.

  11. Close the connection.

Listing 17.17 Finding Records in a Recordset

   
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

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
If objRecordset.EOF Then
 Wscript.Echo "Record cannot be found."
Else
 Wscript.Echo "Record found."
End If
objRecordset.Close
objConnection.Close