Appendix L ActiveX Data Objects

The purpose of this appendix is to provide a brief overview of ActiveX Data Objects (ADO), which is the standard component provided by Microsoft for accessing databases and other structured data sources. Since ADO is a provided as a library of COM objects, you can use ADO in your VBScript programs to write to and read from databases. Our purpose here is not to discuss all of the details and powerful features of ADO. Rather, since ADO is so often used in combination with VBScript, our goal is to provide a brief overview and reference.

ADO is too large a subject to cover thoroughly in one appendix. If you are making significant use of ADO in your scripts, you may want to purchase a book dedicated to ADO. Given the brevity of this overview, we also assume that you are familiar with basic relational database concepts such as tables, columns , queries, stored procedures, and so on.

This guide to ADO covers ADO 2.8, which is the latest version of ADO available at press time. Please note that, since VBScript is a pre-.NET technology, we will not be discussing ADO.NET, which is the latest version of ADO. ADO.NET is intended for use by .NET languages such as C# and VB.NET. It is not natively compatible with VBScript. If you want to ensure that you have the latest version of ADO, you can download the latest release of Microsoft Data Access Components (MDAC) from msdn.microsoft.com/data .

Our discussion will include the following ADO objects:

• Connection -Used to connect to a data source and manage transactions.
• Command -Used to execute commands such as queries and stored procedures against a data source.
• Parameters and Parameter - Parameter objects are stored in the Command object's Parameters collection. Together, Parameter and Parameters are used to specifyparameters for a stored procedure being called through a Command object.
• Recordset -Used to store a series of structured data-usually data returned from a query or stored procedure-represented as a series of rows and columns. You can also use the Recordset object to manually construct a data set that you wish to submit to a database.
• Error -If one or more errors occur while using any of the ADO objects, the Connection object's Errors collection will contain one or more Error objects. An Error object has information about an error such as the number, description, and source.

Please note that we are not covering the Record or Stream objects as these are more advanced than our basic ADO overview. Also, we do not have separate sections for the Fields and Field objects, but the basic usage of these are covered in the section on the Recordset object.

The example code in this appendix is based on the Northwind sample database that ships with Microsoft Access. The downloadable script files are designed for running under the Windows Script Host (see Chapter 12), but the code is easily transferable to other hosts . Our discussion will focus on using ADO for relational database access, since that it by far the most common use for ADO, but keep in mind that since ADO is part of Microsoft's 'Universal Database' strategy, it can be used to access other structured data formats such as spreadsheets, e-mail systems, text files, and so on.

Finally, please note that certain advanced or seldom used properties and methods may be excluded for some objects.

The Connection Object

The Connection object is almost always required in order to do anything interesting with the other ADO objects. The Connection object represents the primary access point to a data source. If you want to read from or write to a data source, you need to use the Connection object to establish a connection with that data source. The following example script ( ADO_CONNECTION.VBS ) illustrates the basic technique for opening a connection.

Option Explicit Const adStateOpen = 1 Dim cnNorthwind Dim strStatus Set cnNorthwind = CreateObject(ADODB.Connection) cnNorthwind.ConnectionString = _ Provider=Microsoft.Jet.OLEDB.4.0; & _ User ID=Admin;Password=;Data Source= & _ C: Program Files Microsoft Office Office Samples & _ Northwind.mdb cnNorthwind.Open If cnNorthwind.State = adStateOpen Then MsgBox Connection is open. Else MsgBox Connection is not open. End If cnNorthwind.Close Set cnNorthwind = Nothing

After instantiating the Connection object, this code sets the ConnectionString property. The connection string is how you tell the Connection object what kind of data source to connect to ( Provider= ), how to locate that data source ( Data Source= ), and security information for logging into the data source ( User , ID= and Password= ). To get this connection string to work on your machine, you may have to change the path to the Northwind.mdb file.

Unfortunately, there are many different ³provider ³ types for different data source types and many different versions of these providers. There are also different styles of connection strings that different providers may or may not support. Unfortunately, there is not enough space here to include information on the dozens of different providers and connection string formats. Please consult the documentation for the type of database you are using. There is also an excellent comprehensive connection string reference available online at http://www.able-consulting.com/ADO_ Conn.htm.

Going back to the script, after setting the connection string, we call the Open method, which tells the Connection object to attempt to connect to the data source. Finally, just to prove that our connection was successful, we check the State property to find out the status of the connection. Notice also that we call the Close method when we are done with the connection. It is always a good idea to close any database connections as soon as you are done with them.

The following two sections describe in detail the important Connection object properties and methods.

Connection Object Properties

Connection Object Methods

The Command Object

The Command object is an optional alternative to using the Execute and Open methods of the Connection object. Some programmers prefer the explicitness of using a Command object insteadof Connection.Execute and Connection.Open . Other programmers prefer the brevity of using the Connection object. The choice is up to you. Note, however, that if you are calling a stored procedure that uses parameters, you pretty much have to use the Command object instead of Connection. Execute or Connection.Open .

The following script ( ADO_COMMAND.VBS ) demonstrates the basic technique of using an SQL query to open a Recordset object from the Northwind database's Suppliers table. This script example borrows from the Connection object example in the previous section and will be continued in the Recordset object section, later.

Option Explicit Const adCmdText = 1 Const adStateOpen = 1 Dim cnNorthwind Dim cmdQuery Dim rsSuppliers Set cnNorthwind = CreateObject(ADODB.Connection) cnNorthwind.ConnectionString = _ Provider=Microsoft.Jet.OLEDB.4.0; & _ User ID=Admin;Password=;Data Source= & _ C: Program Files Microsoft Office Office Samples & _ Northwind.mdb cnNorthwind.Open Set cmdQuery = CreateObject(ADODB.Command) With cmdQuery Set .ActiveConnection = cnNorthwind .CommandText = SELECT [SupplierID], [CompanyName] & _ FROM Suppliers & _ WHERE [Country] = 'Australia' .CommandType = adCmdText Set rsSuppliers = .Execute End With If rsSuppliers.State = adStateOpen Then MsgBox Recordset opened successfully. rsSuppliers.Close End If Set rsSuppliers = Nothing Set cmdQuery = Nothing cnNorthwind.Close Set cnNorthwind = Nothing

Notice that once we have an open Connection object, we instantiate a Command object and set the ActiveConnection property of the Command to the Connection object. This gives the Command object its link to the data source. Next we set the CommandText property to an SQL query and set the CommandType to 1 , which is the value for the adCmdText enumerated constant (see ADO Enumerated Constants at the end of this appendix). Then we use the Execute method to run the query and return a Recordset object.

The Execute method may or may not return a Recordset depending on the type of command sent through CommandText . For example, an SQL UPDATE statement would not return a Recordset . In that case, you would just call the Execute method as a procedure instead of as a function.

Another common use of the Command object is calling a stored procedure (which may or may not return a Recordset ). In this case, most programmers use the Parameters collection to specify any parameters accepted by the stored procedure. The Parameter object section later includes an example of this syntax. (You can also pass parameters to a stored procedure in two other ways: one, as part of the CommandText or CommandStream property; and two, using the Parameters argument of the Execute method (which is not the same as the Parameters collection). Keep in mind that these alternative methods only support input-only parameters.

The following two sections describe in detail the important Command object properties and methods.

Command Object Properties

Command Object Methods

The Parameters and Parameter Objects

The Parameter object is used in conjunction with the Command object and its Parameters collection. ³Parameter ³ in this context refers to parameters/arguments expected by a stored procedure that is being called with a Command object. There are actually a few ways to pass parameters to a stored procedure.

• Embedding the parameters in the text of CommandText or CommandStream
• Passing them in a Variant array to the Execute method's Parameters argument
• Explicitly creating Parameter objects and adding them to the Command object's Parameters collection

Only the third technique actually involves the use of the Parameter object. Using the third technique has two advantages: one, the code is more explicit and a little more readable; and two, it supports in/out parameters, which the other two methods do not. On the downside, using the third method is a little slower since you have to instantiate ( potentially ) several Parameter objects for a single call to a stored procedure.

Using Parameter objects for passing parameters to a stored procedure involves these steps:

1. Create a new Parameter object using Command.CreateParameter (using the arguments of the CreateParameter method to set the properties of the Parameter object)
2. Adding the Parameter object to the Parameters collection using the Parameters.Append method
3. Repeating the first two steps for as many times as the stored procedure's parameter list requires

All this must be done before calling the Execute method and, ideally , after setting the CommandText or CommandStream property of the Command object. Also, many programmers prefer to expedite the process by performing the first and second steps simultaneously . The following code snippet illustrates this technique ( please note that this code is not included in the downloadable code for this chapter since we are using Access for our examples and Access does not support stored procedures).

Const adCmdStoredProc = 4 Const adVarChar = 200 Const adParamInput = 1 Set cmdStoredProc = CreateObject(ADODB.Command) With cmdStoredProc Set .ActiveConnection = cnConnection .CommandText = GetSuppliersByCountry .CommandType = adCmdStoredProc .Parameters.Append .CreateParameter(strCountry, _ adVarChar, adParamInput, 15, Australia) Set rsSuppliers = .Execute End With

The benefit of the shortcut used here is that you do not have to explicitly declare or instantiate any Parameter objects. The CreateParameter method returns a Parameter object that is immediately passed to the Parameters.Append method.

Another technique is to call the Command.Parameters.Refresh method after setting the stored procedure ame in CommandText or

CommandStream . The Refresh method will retrieve the parameter list from the data source and automatically populate the Parameters collection. Then you can loop back through the Parameters collection to set all of the Value properties. However, while this is a cool technique, it is usually avoided because of the extra round trip to the database.

The following two sections describe in detail the important Parameter object properties and methods.

Parameter Object Properties

Parameter Object Methods

Parameters Object Properties

Parameters Object Methods

The Recordset Object

In general, setting aside the Stream object for a moment (see later), the Recordset is where a programmer can work with actual data. The typical scenario for the use of the Recordset object is that a programmer creates Connection and Command objects in order to execute a query or stored procedure in a database. The query or stored procedure returns a set of data, formatted as rows and columns , which is stored in a Recordset object. Then the programmer can use the Recordset object to read, update, add, or delete the data.

The Recordset object is also quite versatile beyond this typical scenario. It has a long list of properties and methods , and we could go on explaining all the cool things you can do with a Recordset . For example, you can programmatically create a Recordset object from scratch, create fields for it, and fill it up with data-all without using a data source at all. You can also save a Recordset to disk and re-create it later without having to connected to the original source of the Recordset . Recordset objects can even be nested inside of other Recordset objects. However, our focus here will be on the primary properties and methods used to work with Recordset objects that are returned by queries and stored procedures.

We will look at two example scripts: one that opens a Recordset from a query and loops through the data one time; and another that opens a Recordset , changes some of the data it contains, and updates the database with the changes. The following example script ( ADO _ RECORDSET_READ.VBS) illustrates the open and read technique.

Option Explicit Const adCmdText = 1 Const adStateOpen = 1 Const adOpenForwardOnly = 0 Const adUseClient = 3 Const adLockReadOnly = 1 Dim cnNorthwind Dim cmdQuery Dim rsSuppliers Dim strMsg Set cnNorthwind = CreateObject(ADODB.Connection) cnNorthwind.ConnectionString = Provider=Microsoft.Jet.OLEDB.4.0; & _ User ID=Admin;Password=;Data Source= & _ C: Program Files Microsoft Office Office Samples & _ Northwind.mdb cnNorthwind.Open Set cmdQuery = CreateObject(ADODB.Command) With cmdQuery Set .ActiveConnection = cnNorthwind .CommandText = SELECT [SupplierID], [CompanyName] & _ FROM Suppliers & _ WHERE [Country] = 'Australia' .CommandType = adCmdText End With Set rsSuppliers = CreateObject(ADODB.Recordset) Set rsSuppliers.Source = cmdQuery rsSuppliers.CursorType = adOpenForwardOnly rsSuppliers.CursorLocation = adUseClient rsSuppliers.LockType = adLockReadOnly rsSuppliers.Open If rsSuppliers.State = adStateOpen Then Do While Not rsSuppliers.EOF strMsg = strMsg & rsSuppliers.Fields(SupplierID).Name strMsg = strMsg & : & vbTab strMsg = strMsg & rsSuppliers.Fields(SupplierID).Value strMsg = strMsg & vbNewLine strMsg = strMsg & rsSuppliers.Fields(CompanyName).Name strMsg = strMsg & : & vbTab strMsg = strMsg & rsSuppliers.Fields(CompanyName).Value strMsg = strMsg & vbNewLine & vbNewLine rsSuppliers.MoveNext Loop rsSuppliers.Close MsgBox strMsg Else MsgBox Recordset not opened. End If Set rsSuppliers = Nothing Set cmdQuery = Nothing cnNorthwind.Close Set cnNorthwind = Nothing

If you've been following along, you'll see that this is an extension of the example script from the previous Command object section. However, notice that we have changed the way we open the Recordset . Instead of using Command.Execute , we use CreateObject to instantiate an empty Recordset and then set the Command object to the Recordset.Source . A few lines later, when we call Recordset.Open , the Command object we put in the Source property (and its Connection object) is used to communicate with the database. This alternative method is necessary if you want to have control over some important properties of the Recordset before opening it.

In this example, we have set the CursorType property to adOpenForwardOnly , the CursorLocation to adUseClient ., and the LockType to adLockReadOnly . What this means is that we only intend to loop through this Recordset one time and that we want to do this as fast as possible while using the least amount of resources. Other cursor types, such as adOpenDynamic and adOpenKeyset , are more flexible in that they allow you to move back and forth between records and to loop through the Recordset more than one, but these cursor types incur more overhead and are therefore slower.

As far as CursorLocation , server-side cursors can enable certain Recordset features, such as the ability to receive dynamic updates to the data in the Recordset as it changes in the data source, but use more resources on the server. In this case we're not using those features, so we use a client-side cursor since it puts less of a tax on the database.

Finally, we set the LockType to adLockReadOnly since we do not intend to make any updates to the data. This is not strictly necessary since adLockReadOnly happens automatically when using adOpenForwardOnly , but it illustrates the point that CursorType , CursorLocation , and LockType have primary influence over what you can and can't do with a Recordset , how fast you'll be able to do it, and how much resource overhead you're incur.

When working with the Recordset object, it is important to have in mind how you plan to use the Recordset and then to set properties such as CursorType , CursorLocation , and LockType so that the Recordset will have the features you need while at the same time using the least amount of resources and offering the best performance. If you want to experiment to find out which capabilities are supported by which cursor types and locations (and this different depending on the ³Provider ³ specified in the connection string), you can use the Recordset.Supports method with CursorOptionEnum to find out if a feature you want (such as ³move previous ³ support or the ability to update the Recordset ) is supported by a certain cursor type and location.

After we open the Recordset , we use a Do loop to move through each of the records. One key to making this work is that we use the EOF property to make sure that we stop looping when we have reached the end (and that we don't start looping if the Recordset is empty). Another key to the loop is the call to Recordset.MoveNext right before the Loop statement. MoveNext moves the cursor to the next record. This call is essential or you will create an endless loop that reads the first record in the Recordset over and over again.

Inside the loop, we use the Fields collection, which returns a Field object based on the Name of the field, to read the data out of each record. The two important Field properties for us here are Name and Value . The Name is the same as the column name we used in the SQL query. The Value is the value that came back from the data source for that column on that row.

We turn now to our second example ( ADO_RECORDSET_WRITE.VBS ), which uses a Recordset object to update data.

Option Explicit Const adCmdText = 1 Const adStateOpen = 1 Const adOpenKeyset = 1 Const adUseServer = 2 Const adLockOptimistic = 3 Dim cnNorthwind Dim cmdQuery Dim rsOrders Dim datOrder Set cnNorthwind = CreateObject(ADODB.Connection) cnNorthwind.ConnectionString = Provider=Microsoft.Jet.OLEDB.4.0; & _ User ID=Admin;Password=;Data Source= & _ C: Program Files Microsoft OfficesOfficesSamples & _ Northwind.mdb cnNorthwind.Open Set cmdQuery = CreateObject(ADODB.Command) With cmdQuery Set .ActiveConnection = cnNorthwind .CommandText = SELECT [OrderDate] & _ FROM Orders & _ WHERE [ShipCountry] = 'Italy' .CommandType = adCmdText End With Set rsOrders = CreateObject(ADODB.Recordset) Set rsOrders.Source = cmdQuery rsOrders.CursorType = adOpenKeyset rsOrders.CursorLocation = adUseServer rsOrders.LockType = adLockOptimistic rsOrders.Open If rsOrders.State = adStateOpen Then Do While Not rsOrders.EOF datOrder = rsOrders(OrderDate) If Not IsNull(datOrder) Then 'Add one second to the OrderDate datOrder = DateAdd(s, 1, datOrder) rsOrders(OrderDate) = datOrder rsOrders.Update End If rsOrders.MoveNext Loop rsOrders.Close MsgBox Finished updating recordset. Else MsgBox Recordset not opened. End If Set rsOrders = Nothing Set cmdQuery = Nothing cnNorthwind.Close Set cnNorthwind = Nothing

First, notice that we have changed our query to return a list of order dates that are to be shipped to Italy. We did this because we want to be able to demonstrate an update operation without messing up your Northwind database if you decide to run this script on your machine. As we'll explain in a minute, and as you can see from reading the code, to keep the update as harmless as possible, we are adding one second to the OrderDate value.

Second, we have change the values we are using for the CursorType , CursorLocation , and LockType properties to a combination that will open an updateable Recordset . The adOpenKeyset cursor type will give us an updateable cursor that will detect any new records that are added while we have the Recordset open, but that does not use as many resources as adOpenDynamic . We changed the cursor location to adUseServer to support the adOpenKeyset cursor, which needs to run on the server in order to detect new records. Finally, we changed the lock type to adLockOptimistic to indicate that we want to update records but that we don't care if other users update the same records while we have the Recordset open. (If we did care, we would use adLockPessimistic .)

Inside of the loop, we retrieve the value of the OrderDate field, add one second to the date, and then write the new date value back to the field. We call the Update method to save to the database the change to the current record. Notice that we have altered the syntax we use to access the Recordset.Fields collection. This new syntax is a shorthand that takes advantage of the fact that the Fields property is the default property of the Recordset object and that the Value property is the default property of the Field object. Typing rsOrders.Fields(³OrderDate ³) is equivalent to just typing rsOrders(³OrderDate ³)

The following two sections describe in detail the important Recordset object properties and methods.

Recordset Object Properties

Recordset Object Methods

ADO Enumerated Constants

A note regarding enumerated constants: enumerated constants are not directly supported by VBScript. This is unfortunate since they are an easy way to keep your code readable. For example, instead of setting

Command.CommandType with the value of 1 , you can set it to the value of adCmdText , making it a lot easier to understand what's going on in your code. However, there are two workarounds that can help you achieve that same code readability.

First, if you are writing an ASP (Active Server Pages) application, you can add this line to each of your pages that are going to include ADO code.

This include file is provided by Microsoft and should already be installed on your machine. This include file contains constant declarations equivalent to the ADO enumerated constants so that you can use them in your code just as if VBScript supported enumerated constants.

Second, if you are using another host, such as the Windows Script Host, you can declare named constants of your own as you need. We have used this technique with the example Windows Script Host scripts included with this appendix. Manually declaring your constants is a little tedious , but worth the trouble. Besides, you have to only declare the few that you're going to need in any given script. Take a look at these two equivalent lines of code and decide which one is more readable.

.Parameters.Append .CreateParameter(strCountry, _ 200, 4, 15, Germany) .Parameters.Append .CreateParameter(strCountry, _ adVarChar, adParamInput, 15, Germany)

If you're not using ASP, all that's required to achieve the improved readability of the second line is to add these two lines to your script.

Const adVarChar = 200 Const adParamInput = 1

Here are the ADO enumerated constants referred to in this ADO object reference.