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

Name	Accepts/Returns	Access	Description
CommandTimeout	Long	Read/Write	Used to set or obtain the number of seconds a Connection object will wait when executing a command (see Execute method) before giving up; has no effect on the CommandTimeout property of the Command object.
ConnectionString	String	Read/Write	Used to set or obtain the connection details for a data source, including provider type, data source location, user name, and password.
ConnectionTimeout	Long	Read/Write	Used to set or obtain the number of seconds a Connection object will wait when connecting to a data source (see Open method) before giving up.
CursorLocation	Long ( CursorLocationEnum )	Read/Write	Used to set or obtain the location of the ³cursor ³ of any Recordset objects opened with a Connection object; a cursor can be ³client-side ³ or ³server-side.
³ Errors	Collection	Read Only	If an error occurs during usage of a Connection object, this collection will contain one or more Error objects, which have properties exposing error number, description, source, and so on.
State	Long ( ObjectStateEnum )	Read Only	Returns information about the status of a connection to a data source. Most often, the status is either ³open ³ or ³closed ³; other statuses apply when using asynchronous methods on ADO objects using the connection.

Connection Object Methods

Name	Arguments	Returns	Description
BeginTrans	None	Long	Used to begin a transaction at the data source; must be followed, eventually, by a call to either CommitTrans or RollbackTrans; the Long return value (seldom used) indicates the ³nesting level ³ of the new transaction
CommitTrans	None	N/A	Used to ³commit ³ a transaction that was started with a call to BeginTrans
RollbackTrans	None	N/A	Used to abort a transaction that was started with a call to BeginTrans
Cancel	None	N/A	Used to abort a method call (such as Connection.Execute or Recordset.Open ) that was executed asynchronously
Close	None	N/A	Used to close an open connection; important to call this when done using a Connection object, before setting it to Nothing
Execute	CommandText - String value containing an SQL query, stored procedure call, or URL to execute at the data source RecordsAffected -Optional Long in/out argument that will contain the number of records returned by the data source Options -Optional Long value indicating the type of command in the CommandText argument; can be any combination of values from CommandTypeEnum and ExecuteOptionEnum	Recordset object	To execute a command on adata source, you can use either the Execute method of the Connection object or the Command object; use a Command object when you need to pass parameters to a stored procedure; otherwise Connection.Execute is a convenient shortcut; for an SQL query, use adCmdText ( 1 ) in the Options argument; for a stored procedure, use adCmdStoredProc ( 4 )
Open	ConnectionString -Optional String value that can be used in place of, or to override, the ConnectionString property UserID -Optional String value that can be used in place of specifying a user ID in the connection string Password -Optional String value that can be used in place of specifying a password in the connection string Options -Optional Long ( ConnectOptionEnum ) value; default value of adConnectUnspecified causes normal, synchronous connection; adAsyncConnect will cause asynchronous connection	N/A	Opens a connection object; traditionally, most programmers set the ConnectionString property before calling Open instead of using the arguments of the Open method

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

Name	Accepts/Returns	Access	Description
ActiveConnection	Connection object (in a Variant )	Read/Write	Used to set or obtain a reference to a Connection object used by the Command; all ADO objects must go through a Connection object to get to the data source; this is usually the first property you would set after instantiating a Command object
CommandStream	Stream objec t	Read/Write	A mutually exclusive alternative to the CommandText property; whereas CommandText accepts/returns a String value and CommandStream accepts/returns a Stream object
CommandText	String	Read/Write	Used to set or obtain the string representing the ³command ³ that you wish the Command object to execute; can be an SQL query, stored procedure call, or URL; you should also set the CommandType property to the type value corresponding to the type of command
CommandTimeout	Long	Read/Write	Used to set or obtain the number of seconds a Command object will wait before giving up on a call to the Execute method
CommandType	Long ( CommandTypeEnum )	Read/Write	Must be set to match the type of command value placed into the CommandText or CommandStream property; use 1 ( adCmdText ) for SQL queries and 4 ( adCmdStoredProc ) for stored procedure calls
NamedParameters	Boolean	Read/Write	If the Command object is using the Parameters collection to pass parameters to a stored procedure, this property controls how those parameters are interpreted; True means that the parameters will be matched up by Parameter.Name with the parameter names defined in the stored procedure; False (the default) means that Parameter.Name is ignored and the parameters will be sent to the stored procedure in the order in which they are added to the Parameters collection
Parameters	Collection of Parameter objects	Read/Write	Holds a collection of Parameter objects; used when calling stored procedures that accept parameters; initially the collection is empty; Parameter objects must be added manually or can be auto- populated using the Parameters.Refresh method
Prepared	Boolean	Read/Write	Used to set or obtain whether the Execute method should tell the data source to cache a compiled version of the command, which can increase performance when repeatedly executing the same command

Command Object Methods

Name	Arguments	Returns	Description
Cancel	None	N/A	If the Execute method was called asynchronously, a call to this method will abort the pending or in-progress command
CreateParameter	Name -Optional String value representing the parameter name Type -Optional Long value ( DataTypeEnum ) representing the data type of the parameter Direction -Optional Long value ( ParameterDirectionEnum ) indicating whether the parameter is input, output, or both Size -Optional Long value indicating the maximum parameter value length in bytes or characters Value -Optional Variant representing the value for the parameter (must correspond to the Type argument)	Parameter object	When calling a stored procedure and manually creating Parameter objects to add to the Parameters collection, use this method to create a new Parameter object; note that this method returns only a Parameter object-it does not add it to the Parameters collection; you can either use the arguments to initialize the Parameter object or omitthe arguments and manually set the properties on the returned Parameter object; if you use the arguments, the returned Parameter object will already have its properties set
Execute	RecordsAffected -Optional Long in/out argument that ADO will use to return the number of records affected by the command Parameters -Optional Variant array of parameters to pass to a stored procedure; does not support in/out parameters Options -Optional Long value indicating the type of command in the CommandText argument; can be any combination of values from CommandTypeEnum and ExecuteOptionEnum	Either returns a Recordset object, a Stream object, or Nothing , depending on the command sent to the data source	When you have prepared a Command object with properties such as CommandText , CommandType , and the Parameters collection, the Execute method will actually issue the command to the data source; depending on the content of the command, the data source may or may not return data, which influences the return type of the Execute method

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

Name	Accepts/Returns	Access	Description
Direction	Long ( ParameterDirectionEnum )	Read/Write	Used to set or obtain the ³direction ³ for a stored procedure parameter; parameters can be input only ( adParamInput ), output only ( adParamOutput ), both input and output ( adParamInputOutput ), or return values ( adParamReturn )
Name	String	Read/Write before appended to Parameters , Read Only after appended	Depending on the value of Command.NamedParameters , the Name of a Parameter object may or may not be used in the execution of the stored procedure; if NamedParameters is True , Parameter.Name must match exactly the name of one of the stored procedure's parameters
NumericScale	Byte	Read/Write	Used to set or obtain the number of decimal places to which numeric parameter values are resolved
Precision	Byte	Read/Write	Used to set or obtain the precision for numeric parameters
Size	Long	Read/Write	Used to set or obtain the maximum size, in characters or bytes (depending on the Type ), for a parameter
Type	Long ( DataTypeEnum )	Read/Write	Used to set or obtain the data type of a parameter
Value	Variant	Read/Write	Used to set or obtain the value of a parameter; for output or input/output parameters, the value will be set by the stored procedure

Parameter Object Methods

Name	Arguments	Returns	Description
AppendChunk	Data - Variant value to be appended to the Value property of the Parameter	N/A	In situations in which you are sending large amounts of text or binary data in a Parameter object, use the AppendChunk method to gradually build up the Value property rather than setting the Value property directly;for example, if you have 10,000 bytes of data you might call AppendChunk 10 times in a loop, appending 1000 bytes each time; can only be used when Type is adFldLong

Parameters Object Properties

Name	Accepts/Returns	Access	Description
Count	Long	Read Only	Used to obtain the number of Parameter objects in the Parameters collection
Item	Parameter	Read Only	Used to obtain a reference to a certain Parameter object based on its index in the Parameters collection

Parameters Object Methods

Name	Arguments	Returns	Description
Append	Parameter -A Parameter object that has already been initialized with Name , Type , Value , and so on	N/A	Used to add a new Parameter object to the Parameters collection; the Parameter object should have been created with Command.Create Parameter; its Type and Name properties must be set, but the Value can optionally be set or changed after appending
Delete	Index -The numeric collection index of the Parameter object you wish to remove from the Parameters collection	N/A	Used to remove a Parameter object from the Parameters collection
Refresh	None	N/A	If the ( Command.CommandText ) or CommandStream property has already been set with the name of a stored procedure, the Parameters.Refresh method will make a trip to the database to obtain the parameter list for the stored procedure and automatically populate the Parameters collection, after which you can loop through the collection to set the Value properties, if necessary

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

Name	Accepts/Returns	Access	Description
BOF and EOF	Boolean	Read Only	Indicates whether the cursor is at the beginning of the Recordset or at the end; if the cursor is one position before the first record, BOF will be True; if one after the last record, EOF will be True; if directly on one of the records, both BOF and EOF will be False
CursorLocation	Long(CursorLocationEnum)	Read/Write before open; Read Only after open	As explained in the ³Overview ³ section above, CursorLocation is one of the essential properties for controlling cursor behavior and feature support; value can be either adUseClient or adUseServer
CursorType	Long (CursorTypeEnum)	Read/Write before open; Read Only after open	As explained in the previous section Overview , CursorType is one of the essential properties for controlling cursor behavior and feature support; different cursor types support different features; set the CursorType based on the features you need
Fields	Fields collection holding Field objects	Read Only	A Recordset is a matrix of columns and rows; the Fields property exposes the collection of columns, represented as Field objects; when reading a Recordset returned by a data source, you do not add or remove Field objects from the collection, but this can be done when working with a Recordset that is not associated with a data source
LockType	Long (LockTypeEnum)	Read/Write before open; Read Only after open	As explained in the previous section Overview , CursorType is one of the essential properties for controlling cursor behavior and feature support; if you do not need to update the data in the Recordset , use adLockReadOnly; for updates use either adLockOptimistic or adLockPessimistic , depending whether you need to protect against other processes changing data at the same time
RecordCount	Long	Read Only	Returns the number of records in the Recordset; will return -1 if the CursorType and CursorLocation do not support this feature; count may not be reliable if the cursor does not support ³approximate positioning ³ or ³bookmarks ³(see Supports method); you must use the exactly correct combination of CursorType and CursorLocation in order to depend on this property based on the underlying provider
Source	String or Command object	Read/Write before open; Read Only after open	If you wish to set properties such as CursorType and CursorLocation before opening the Recordset , create a Command object and set it into this property; you can also put an SQL query or stored procedure call string in this property if you also set the ActiveConnection property to a Connection object, but using a Command object that already has a Connection is more explicit
State	Long (ObjectStateEnum)	Read Only	Indicates the status of the Recordset; for example, adStateOpen or adStateClosed

Recordset Object Methods

Name	Arguments	Returns	Description
AddNew	FieldList -Optional String or array of names or ordinal positions that must correspond to the fields in the Recordset. Fields collection Values -Optional String or array of values for the fields; elements must line up with the FieldList	N/A	If you are working with an updateable Recordset , use this to add a new record; you can either pass the fields and values as arguments to this method or omit the arguments and then set the value of each field as separate calls
Close	None	N/A	Closes an open Recordset; it's a good idea to always close a Recordset when you're done with it; will return an error if the Recordset is not open, so check the State property before calling Close
Delete	AffectRecords -Optional Long ( AffectEnum ) value indicating which records to include in the delete operation	N/A	Use this method to delete records from an updateable Recordset; most often, you would call this method while the cursor is positioned on the record you wish to delete-in which case you can omit the AffectRecords argument since the default is adAffectCurrent
MoveFirst MoveLast MoveNext MovePrevious	None	N/A	These four methods are used to move the cursor from its current position to another position; MoveNext is most often used since normally you start at the beginning of the Recordset and move through it one row at a time; if the CursorType and CursorLocation settings support it, you can use MoveFirst and MovePrevious to move the cursor backwards
Open	Source -Optional Variant that can contain a Command object, SQL string, or stored procedure call string; can be omitted if Source property is set ActiveConnection -Optional Variant that can contain an open Connection object or a connection string; can be omitted if ActiveConnection property is set or if the Command object has an open connection CursorType -Optional Long ( CursorTypeEnum ) indicating desired cursor type; can be omitted if CursorType property is set LockType -Optional Long ( LockTypeEnum ) indicating desired locking behavior; can be omitted if LockType property is set Options -Optional Long value indicating how to treat Source argument if other than a Command object; can be any combination of values from CommandTypeEnum and ExecuteOptionEnum	N/A	As demonstrated in the Overview section earlier, used to open a Recordset using a certain source with certain options; you can either set the properties you want first and call Open with the arguments omitted, or you can pass the arguments to Open , which will set the corresponding properties automatically
Supports	CursorOptions - Long ( CursorOptionEnum ) value indicating which feature/behavior you wish to test	Boolean	Since the various features and behaviors of a Recordset object may or may not be available depending on CursorType and CursorLocation , this method is helpful in determining whether a given operation is supported
Update	Fields -Optional Variant containing a single field name or an array of field names or ordinal positions that must correspond to the fields in the Recordset.Fields collection Values -Optional Variant containing a single value or an array of values; elements must line up with Fields	N/A	Call this method after performing any edits of Field.Value or after a call to AddNew and before moving the cursor; data will not be saved until Update is called

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.

Name	Values
AffectEnum	adAffectAll-3
	adAffectAllChapters-4
	adAffectCurrent-1
	adAffectGroup-2
CommandTypeEnum	adCmdUnspecified-1
	adCmdText-1
	adCmdTable-2
	adCmdStoredProc-4
	adCmdUnknown-8 (Default)
	adCmdFile-256
	adCmdTableDirect-512
ConnectOptionEnum	adAsyncConnect-16
	adConnectUnspecified-1
CursorLocationEnum	adUseClient -3
	adUseNone-1 (obsolete-do not use)
	adUseServer-2
CursorOptionEnum	adAddNew-16778240 adApproxPosition-16384
	adBookmark-8192
	adDelete-16779264adFind-524288
	adHoldRecords-256 adIndex-1048576
	adMovePrevious-512
	adNotify-262144
	adResync-131072
	adSeek-2097152
	adUpdate-16809984
	adUpdateBatch-65536
CursorTypeEnum	adOpenDynamic-2
	adOenForwardOnly-0
	adOenKeyset-1
	adOpenStatic-3
	adOpenUnspecified-1
DataTypeEnum	adArray-8192
	adBigInt-20
	adBinary-128
	adBoolean-11
	adBSTR-8
	adChapter-136
	adChar-129
	adCurrency-6
	adDate-7adDBDate-133
	adDBTime-134
	adDBTimeStamp-135
	adDecimal-14
	adDouble-5
	adEmpty-0
	adError-10
	adFileTime-64
	adGUID-72
	adInteger-3
	adLongVarBinary-205
	adLongVarChar-201
	adLongVarWChar-203
	adNumeric-131
	adPropVariant-138
	adSingle-4
	adSmallInt-2
	adTinyInt-16
	adUnsignedBigInt-21
	adUnsignedInt-19
	adUnsignedSmallInt-18
	adUnsignedTinyInt-17adUserDefined-132
	adVarBinary-204adVarChar-200
	adVarNumeric-139
	adVarWChar-202
	adWChar-130
ExecuteOtionEnum	adAsyncExecute-16
	adAsyncFetch-32
	adAsyncFetchNonBlocking-64
	adExecuteNoRecords-128
	adExecuteStream-1024
	adOptionUnspecified-1
LockTypeEnum	adLockBatchOptimistic-4
	adLockOptimistic-3
	adLockPessimistic-2
	adLockReadOnly-1
	adLockUnspecified-1
ObjectStateEnum	adStateClosed-0
	adStateOpen-1
	adStateConnecting-2
	adStateExecuting-4
	adStateFetching-8
ParameterDirectionEnum	adParamInput-1
	adParamInputOutput-3
	adParamOutput-2
	adParamReturnValue-4
	adParamUnknown-0