20.1 The First Search

The easiest way to explain basic searching using ADO is with an example. Here we'll build an ADO query to search and display the ADsPaths of all users in Active Directory. You can create a simple script to do this search in six steps.

20.1.1 Step 1—Define the Constants and Variables

For this script, you need to define one constant and three variables. The constant is adStateOpen, which we set to 1. If you're using VBScript, you use this constant later to determine whether you made a successful connection to the database. If you're using Visual Basic (VB), you don't have to include this constant because VB has already defined it. The two main variables are objConn (an ADO Connection object that lets you connect to the AD database) and objRS (an ADO Recordset object that holds the retrieved resultset). The third variable holds the output of the resultset, as shown in the following example:

Option Explicit
   
Const adStateOpen = 1
   
Dim objConn    'ADO Connection object
Dim objRS      'ADO Recordset object
Dim strOutput  'The output of the search

The Option Explicit statement at the beginning of the script is optional, but we recommend that you include it. This statement forces the script to declare variables, so you can quickly spot errors.

20.1.2 Step 2—Establish an ADO Database Connection

To perform an ADO query, you need to establish an ADO connection, which is completely separate from any ADSI connections you may have opened with IADsOpenDSObject::OpenDSObject. Before you can establish this connection, you must create an ADO Connection object to use. This object can be created the same way you create a file system object: use the CreateObject method, with "ADODB.Connection" as a parameter. You use the ADODB prefix to create all ADO objects, and Connection is the top-level object in the ADO object model:

Set objConn = CreateObject("ADODB.Connection")

Just as you use different programmatic identifiers (ProgIDs) (e.g., WinNT:, LDAP:) to tell ADSI which directory to access, you use different OLE DB providers to tell ADO which query syntax to use. An OLE DB provider implements OLE DB interfaces so that different applications can use the same uniform process to access data. The ADSI OLE DB connector supports two forms of syntax: the SQL dialect and the LDAP dialect. Although you can use the SQL dialect to query the ADSI namespace, most scriptwriters use the LDAP dialect because Microsoft defined it specifically for ADO queries to directory services. However, the default for the Connection object's read/write property, objConn.Provider, is MSDASQL, which specifies the use of SQL syntax. Because you want to use the ADSI provider, you need to set objConn.Provider to "ADsDSOObject", which specifies the use of the LDAP syntax. By setting this specific provider, you force the script to use not only a specific syntax but also a specific set of arguments in the calls to the Connection object's methods.

objConn.Provider = "ADSDSOObject"

20.1.3 Step 3—Open the ADO Connection

You can open a connection to the directory by calling the Connection::Open method. When describing the methods and property methods of COM interfaces in text, the established notation is to use a double colon (::) separator. For example, Connection::Open specifies the Open method of the Connection object, as shown in the following example:

  objConn.Open _
    "", "CN=Administrator,CN=Users,dc=mycorp,dc=com", ""

As the code shows, the Open method takes three parameters. The first parameter is the Connection::ConnectionString parameter, which contains information that the script needs to establish a connection to the data source. In this case, it is blank. The second parameter contains the user DN to bind with, and the third is the user's password.

In this code, you're authenticating with a username DN or UPN (the second parameter) and that user's password (the third parameter). You can leave the first parameter blank. Here's why: in ADO, you can perform the same task many ways because the Command, Connection, and Recordset objects heavily interrelate. If you set the properties of one object, you can use those same properties to open the connection of another object as long as you're not setting any new options. Such is the case in the preceding section of code; you're opening the connection without setting any new options. You then use an If...Then...Else statement to see whether the Open call worked. If the call succeeded (i.e., the connection state has a value of 1), the script prints the message "Authentication Successful" and proceeds to the query. If the call didn't work (i.e., the connection state has a value of 0), the script prints the message "Authentication Failed" and quits, setting the returned error code to 1:

If objConn.State = 1 Then
  WScript.Echo "Authentication Successful!"
Else
  WScript.Echo "Authentication Failed."
  WScript.Quit(1)
End If

20.1.4 Step 4—Execute the Query

The Connection::Execute method is used to perform a query. Connection::Execute accepts a string containing four arguments separated by semicolons:

Set objRS = objConn.Execute _
  ("<LDAP://dc=mycorp,dc=com>;(objectclass=user);" _
  & "Name,ADsPath;SubTree")

If the string contains spaces before or after the semicolons, the code will fail. It's easy to forget this, and it's very annoying to try to debug, as the error just states that a parameter is invalid. You must also remember to enclose the parameter in parentheses because you're passing the Execute method's result to a variable.

The four arguments for any LDAP query you want to execute are:

Search base

The search base specifies the point in the directory from which the search will start. You must use a full ADsPath to specify the search base and enclose the ADsPath in angle brackets (< >). In this script, we are starting from the directory's root (i.e., LDAP://dc=mycorp,dc=com).

Filter

The filter defines criteria to match objects with. You must enclose this argument in parentheses. You also must use the format defined in RFC 2254. Filters are covered in greater detail later in Section 20.3. The previous script used the search filter (objectclass=user) which means that only user objects will be returned.

Attributes

The attributes argument is a comma-delimited list of attributes to return. You must specify each attribute individually. Unlike the IADs::Get method, which executes an implicit GetInfo call to obtain all attributes, this ADO search returns only the specified attributes in the resultset. In this case, the ADO search will return the Name and ADsPath attributes. The ADsPath is a useful attribute to retrieve because it lets you use ADSI to bind to that object. You then can perform an explicit GetInfo to obtain all the attributes for that object.

Scope

The scope specifies how far down from the query's starting point (i.e., search base) to search. You can specify one of three string constants: Base, OneLevel, or Subtree. If you set the scope to Base, the ADO search will only match the object specified by the search base only if the search filter matches as well. If you set the scope to OneLevel, the ADO search checks any object directly under the search base, one level down. If you set the scope to Subtree, as this script does, the ADO search checks every container under the search base but does not include the search base.

20.1.5 Step 5—Navigate Through the Resultset

The objRS variable holds the resultset, also known as the recordset. Recordset objects have a table-like structure. The structure's columns are fields, and the rows are records. Fields correspond to the attributes you want to return and assume the titles of those attributes (e.g., Name or ADsPath). ADO also numbers the fields from left to right, starting with 0. Thus, you can access fields using attribute names or index numbers. Records correspond to the values of those attributes.

To manage the members of objRS, the simplest approach is to use the Recordset::MoveNext method (which navigates to the next record in the resultset) while checking the Recordset::EOF (end-of-file) method. The RecordSet::EOF method returns true if you're at the end of the resultset. The following code sample uses both of these methods:

While Not objRS.EOF
  Wscript.Echo objRS.Fields.Item("Name").Value _
    & vbCrLf & objRS.Fields.Item("ADsPath").Value
  objRS.MoveNext
Wend

As this section of code shows, we're using these two methods in a simple while loop to move through each record. If Recordset::EOF returns a value of false (i.e., you're not at the end of the resultset), the script stores the contents of the record for each field into the output variable and moves on to the next record. If Recordset::EOF returns a value of true (i.e., end of the resultset), the script exits the while loop.

To access the values of each matching object, we are using objRS.Fields, which is a Fields collection object. As with all collections, Fields has a method called Item. The Fields::Item method takes an argument that equates to either the name of the field or its index number. The Fields::Item method returns a Field object, which has a Value property method that allows us to get the value for that specific property of the object. In other words, the code:

objRS.Fields.Item("Name").Value

returns the value of the individual field called Name from the collection of all possible fields in the recordset. We'll come back to this more in the later examples on navigating resultsets.

20.1.6 Step 6—Close the ADO Connection

The Connection::Close method is used to close the ADO connection to the directory. To be complete, you may also want to set the Recordset object to Nothing to make sure it doesn't mistakenly get reused. That isn't mandatory if your script is done at that point, because it will automatically get cleaned up, but it is good practice nonetheless. That way, if you later add code to the end of the script, you can't mistakenly reuse the now-defunct objRS variable without reinitializing it first. Here is example code illustrating how to properly close down an ADO session:

objConn.Close
Set objRS = Nothing

20.1.7 The Entire Script for a Simple Search

The following is the entire script:

Option Explicit

Const adStateOpen = 1

Dim objConn  'ADO Connection object
Dim objRS    'ADO Recordset object

Set objConn = CreateObject("ADODB.Connection")
objConn.Provider = "ADSDSOObject"
objConn.Open "","CN=Administrator,CN=Users,dc=mycorp,dc=com", ""
If objConn.State = adStateOpen Then
  WScript.Echo "Authentication Successful!"
Else
  WScript.Echo "Authentication Failed."
  WScript.Quit(1)
End If
   
Set objRS = objConn.Execute _
  ("<LDAP://dc=mycorp,dc=com>;(objectclass=User);" _
  & "Name,ADsPath;SubTree")
   
While Not objRS.EOF
  Wscript.Echo objRS.Fields.Item("Name").Value _
    & vbCrLf & objRS.Fields.Item("ADsPath").Value
  objRS.MoveNext
Wend
   
objConn.Close
Set objRS = Nothing

While we open and close the connection within the short script, we could keep the connection open for every query if we had many queries to execute. This is how ADO normally is used.