CHAPTER 11 ActiveX Data Objects 1.5

One of the most popular reasons for constructing an ASP application is to enable people on an intranet or the Internet to manipulate data in a database remotely. When Microsoft first released Internet Information Server, you were able to connect from your web server applications to a database through the Internet Database Connector (IDC). This method involved a connection file that actually set up the connection to the database and a second file that formatted the information or results of your query. Anyone who has spent any time with IDC files can tell you that, though better than what came before (straight CGI applications, for example), the Internet Database Connector left much to be desired. You could retrieve the results of simple queries and you could even perform simple updating tasks using IDC. However, if you wanted to use a specific cursor type supported by the underlying database or change the structure of the database’s tables, etc, IDC fell short. This forced many web developers to go back to (or stay in) CGI applications. With the release of Internet Information Server 3.0, Microsoft changed that by introducing OLE DB, a C++ API that provides a set of COM interfaces for universal data access. OLE DB can run in any environment that supports COM and DCOM. Furthermore, OLE DB will (in the future) support any type of data, not just database information. For example, Microsoft envisions a time when you will be able to do heterogeneous joins between information in your SQL Server database and messages held by your Exchange server. For this reason, it will be increasingly important to know more about OLE DB in the immediate future, since you can probably assume Microsoft will discontinue further enhancements to ODBC. Unfortunately, the details of OLE DB are not only complex but also outside the scope of this book. If you want to learn more about OLE DB, pick up O’Reilly’s forthcoming ADO: The Definitive Guide, by Jason T. Roff. Jason covers ADO and the underlying OLE DB in great detail and covers it from the standpoint of using it not only from Active Server Pages but also from other programming methods such as Visual Basic and C/C++. 160 Chapter 11 – ActiveX Data Objects 1.5 ActiveX Data Objects 1.5 ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. How does OLE DB help us connect to and manipulate data from our ASP applications? Well, it doesn’t directly. Though OLE DB provides a far more object-oriented access method than ODBC’s C API, it still leaves CGI applications and custom ISAPI filters as your only options for direct access to database information from your web applications. So we’re back to IDC and ODBC, right? Wrong. ActiveX Data Objects (ADO) provides an automation wrapper for OLE DB. This means that we can access OLE DB through regular COM objects exactly as we access the underlying web server components through ASP’s built-in objects, as Figure 11-1 illustrates. As stated earlier, ActiveX Data Objects is a COM interface (specifically a dual interface COM wrapper) for OLE DB. It provides a method of manipulating data that is fast, simpler than traditional CGI data access, more powerful than the Internet Database Connector, and small in terms of memory and disk size required. The following is a list of some of the features of ADO: • Because ADO is a free-threaded object library, you can easily use it in a multiuser client/server environment such as a web application. • You can create objects in the ADO hierarchy independently. Unlike more familiar data access methods, such as Data Access Objects (DAO) and Remote Data Objects (RDO), in which you have to traverse the hierarchy to instantiate objects in the tree, ADO allows you to create objects independently of each other. You can create a standalone Recordset, for instance, whereas with DAO or RDO, you have to create other objects before you can instantiate a Recordset object. This allows you to improve your applications’ performance by instantiating only those objects that you need. Figure 11-1: Relationship among ActiveX Data Objects, OLE DB, and the underlying data stores Active Server Page ActiveX Data Objects OLE DB Layer Data Providers SQL Relational Database Non-relational data store (e.g., email store) ODBC data provider (MSDASQL) 3rd party data provider Instantiating Active Data Objects 161 ActiveX Data Objects 1.5 Instantiating Active Data Objects ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. • Using ADO, you can cache data locally and then update the underlying database in a batch fashion. This significantly decreases the overhead of going back and forth to the database as often as you have to with DAO or RDO. • You can use several different cursor types. In fact, ADO allows you to use custom cursor types on a provider-by-provider basis. For example, if a given data provider (say Oracle) allows you to use a specific cursor type that is not allowed by other ODBC databases, you can still use this cursor type from within your ADO application. • You can limit the number of rows returned to you from a query or table. This is very important in web applications, when the amount of data returned to the client has a direct impact on the speed with which it is received by that client. • You can return multiple recordsets using a single query. Once instantiated, you can then iterate through the recordsets just as you would iterate through fields in a single recordset. This is also very important for performance optimization and speed considerations. Although the ActiveX Data Objects provide all of the preceding functionality, it is imperative that you know the underlying data provider’s ability to meet these functionality requirements. While ActiveX Data Objects is a very powerful set of objects that allow you to create powerful applications, full coverage of its features would require a book in itself. For this reason, this chapter lists the various properties and methods of ADO objects and details only those that will allow you the most common functionality. For more details on the topics I have selected as being “advanced,” I again refer you to Jason T. Roff’s ADO: The Definitive Guide. Accessory Files/Required DLL Files msado15.dll This is version 1.5 of the dynamic link library for the ADO COM objects. You must install this on the web server (using the latest executable setup file from Microsoft) before you can instantiate or use any of the ADO objects. adovbs.inc This file contains VBScript declarations for all the constants used by the Active Data Objects library. You can include this file in your script using the #include directive and refer to any of these ADO constants. (There are other includes for use with non-VB languages: adoint.h and adoid.h for C/C++ programming, and adojavas.inc for Java programming.) Instantiating Active Data Objects To create an object variable containing an instance of an Active Data object, use the CreateObject method of the Server object. The syntax for the CreateObject method is: Set objMyObject = Server.CreateObject(strProgId) 162 Chapter 11 – ActiveX Data Objects 1.5 Instantiating Active Data Objects ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. where the parameters are as follows: objMyObject The name of the object variable created using the CreateObject method of the Server object. strProgId The programmatic identifier (ProgId) of the component you are trying to instantiate. Table 11-1 lists the available Active Data Objects and their corresponding ProgIds. Example <% ' This code uses the Server object's CreateObject ' method to instantiate an ADO Connection object and ' a Recordset object. For more detail about the Open ' method and the ActiveConnection property in the ' example, see later in this chapter. Dim adoCon Dim adoRec Set adoCon = Server.CreateObject("ADODB.Connection") Set adoRec = Server.CreateObject("ADODB.Recordset") ' Open the database connection to my database. adoCon.Open "MyDatabase" ' Set the Connection object to which the Recordset ' object is attached to adoCon. adoRec.ActiveConnection = adoCon %> For more details on the use of the CreateObject method, see its entry in Chapter 8, Server Object. Table 11-1: Active Data Objects Active Data Object ProgId Command ADODB.Command Connection ADODB.Connection Error ADODB.Error Field ADODB.Field Parameter ADODB.Parameter Property ADODB.Property Recordset ADODB.Recordset Object Model 163 ActiveX Data Objects 1.5 Object Model ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. Comments/Troubleshooting There are several small “gotchas” that I’ve learned the hard way when using ADO with ASP. I detail these in this chapter when discussing the particular properties or methods that caused the problems. The only comment I have on ADO is this: When you begin to write your ASP database application, take some time to delve deeper into ADO (perhaps with Roff’s book). There are several more advanced topics in ADO that I do not cover here. Once you find out how to use ADO to perform the functions you want, take time to look at your specific data provider and at what parts of ADO are supported. Does it support all the cursor types that you need? Does ADO support all the functionality you need? Are there properties of ADO that you cannot use because your data provider does not provide them, or, more likely, does your data provider support features that ADO does not support? The answers to these questions and the research that goes into finding the answers can save you a great deal of time during development. This may seem self-evident, but it is extremely important—especially when deciding whether to use ADO. ADO is young. Although Microsoft has poised OLE DB and its automation wrapper, ADO, to take the data access spotlight, it has only recently released ADO. As a result, ADO still has some maturing to do. Finally, I need to again point out that ADO encompasses a very large amount of knowledge. My first outline of this chapter (before my editors saved me) would have resulted in an even more enormous chapter. One final note: Microsoft has recently released for public download an unsupported HTML Table component that will allow you to display the contents of an ADO recordset in an HTML table simply and easily. This component was just released as this book was nearing its last stages of development, so it is not covered here. Download it from http://www.microsoft.com/windows/downloads/ default.asp and experiment on your own. Object Model Figure 11-2 shows a diagram of the ADO object hierarchy. This section briefly describes each of the seven objects that make up ADO. For each object, I list and very briefly describe all of the properties, collections, and methods (ADO objects do not respond to any events). Items marked with an asterisk in the following tables are documented in detail in the Properties Reference, Collections Reference, and Methods Reference later in this chapter. As stated earlier, this is meant only as an overview. However, for several of the more commonly used properties, collections, and methods, I have added some more in-depth coverage in this chapter’s Properties Reference, Collections Reference, and Methods Reference. Command The Command object allows you to manipulate database commands. Although you can execute a command string on a Connection object or as part of opening a Recordset object, the Command object allows you more flexibility. Chief among its 164 Chapter 11 – ActiveX Data Objects 1.5 Object Model ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. extended functionalities is the ability to add, remove, and define query parameters using the Command object’s Parameters collection. You can define the text of a database command, such as a SQL statement, using the CommandText property of the Command object. Table 11-2 lists the Command object’s properties, while Table 11-3 lists its collection objects and Table 11-4 shows its methods. Figure 11-2: The ADO object model Table 11-2: Command Object Properties Property Description ActiveConnection* The name of the Connection object to which the Command object belongs. CommandText* A string containing the text of the command you wish to execute against the database. It can be a query, a SQL statement, the name of a stored procedure, or some other database manipulation command. CommandTimeout* A Long representing the number of seconds ADO should wait for the results of a Command object’s execution before raising an error. The default value is 30 seconds. CommandType* The type of command that is executed using the Command object. Name* A string representing the name of the Command object. Prepared A Boolean value that, if True, indicates the underlying data provider is instructed to store a compiled version of the command before executing it. State An integer value indicating whether the Command object is open (adStateOpen from ADOVBS.INC) or closed (adStateClosed). Connection Object Recordset Object Fields Collection Command Object Parameters Collection Errors Collection Object Model 165 ActiveX Data Objects 1.5 Object Model ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. Connection Object The Connection object represents a single connection to the underlying data provider. As such, the Connection object maintains information about that data provider. In the context of a web application, it represents one connection from the web server to a database server. As with the other ActiveX Data Objects, your ability to use any of the Connection object’s methods or properties is directly dependent on the underlying data provider’s support for the feature. Tables 11-5, 11-6, and 11-7 list the Connection object’s properties, collection objects, and methods, respectively. Table 11-3: Command Object Collections Collection Description Parameters All the instantiated Parameter objects which, in turn, contain the parameters for the current Command object. Parameter objects are added to the Parameters collection using the Command object’s CreateParameter method. Properties The data provider-specific properties for the Command object. If the data provider does not support any custom properties, this collection is empty. Table 11-4: Command Object Methods Method Description CreateParameter Creates a new Parameter object for the Command object’s Parameters collection. Execute* Executes the command contained in the Command object’s CommandText property. Table 11-5: Connection Object Properties Property Description Attributes The value of the Attributes property of the Connection object represents the characteristics for the object. Use the Attributes property to set whether the Connection object performs retaining commits and retaining aborts. This Long value is read/write. CommandTimeout* The number of seconds the Connection should wait for the result of a call to the Execute method before raising an error. The default value is 30 seconds. ConnectionString* A string containing the information for the current connection. This string contains the definition of the Provider, Data Source, User ID, Password, File Name (for a provider specific file), Remote Provider, and Remote Server. ConnectionTimeout* The number in seconds to wait while attempting to make a connection using the ConnectionString before raising an error. The default value is 15 seconds. 166 Chapter 11 – ActiveX Data Objects 1.5 Object Model ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. CursorLocation Indicates where the cursor for the current connection should be created: on the client (adUseClient from ADOVBS.INC) or on the server (adUseServer, the default). DefaultDatabase The default database. If no database is explicitly stated in the execution string, this database is used. If only one database is being used, this is the default database. IsolationLevel The isolation level of the connection, which determines what happens to the underlying records of a database when a transaction is committed or aborted. Mode The level of permissions for the connection itself allowed by the provider. For example, you can use the Mode property to instruct the provider not to accept any other connections until after your connection is closed. Provider The name of the data provider used for the connection. The default for this string value is MSADSQL (Microsoft OLE DB Provider for ODBC). State An integer value representing whether the Connection object is open (adStateOpen from ADOVBS.INC) or closed (adStateClosed). Version The current version of ADO. Table 11-6: Connection Object Collections Collection Description Errors* All the current Error objects generated by errors from the last execution on the data provider. If there have been no errors, this collection is empty. Properties The data provider-specific properties for the Connection object. If the data provider does not support any custom properties, this collection is empty. Table 11-7: Connection Object Methods Method Description BeginTrans Begins a transaction in the underlying data provider. None of the changes made during the transaction are recorded until you explicitly commit the transaction. Close* Closes the current connection. You must close a Connection object’s connection to one data provider before opening a connection to another data provider. CommitTrans Commits a transaction in the underlying data provider. None of the changes made during the transaction are recorded until you explicitly commit the transaction. Execute* Executes a query, stored procedure, or other SQL statement sent as a parameter to this method. Open* Explicitly opens a connection to a data provider. Table 11-5: Connection Object Properties (continued) Property Description Object Model 167 ActiveX Data Objects 1.5 Object Model ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. Error Object An Error object can contain the details of a data provider error. These provider errors can result from incorrect use of ADO syntax or from lack of support for a particular property or method by the underlying data provider. It is important to realize that the Error object represents the details of an error from the provider, and not from ADO. ADO errors are caught by the web server at execution time as runtime errors. Provider errors are specific to a particular Connection object. When an error occurs with the data provider, one or more errors are raised by that provider and added to the Connection object’s Errors collection, which is cleared each time a new operation causes an error to be returned from the data provider. From the Error object, you can retrieve the name, number, and description of each error caused by the invalid operation. In addition, you can retrieve Help information and information about the state of the data provider from the Error object. Table 11-8 lists the Error object’s properties; it has no collection objects or methods. OpenSchema Obtains information on the database structure from the data provider. RollbackTrans Aborts a transaction in the underlying data provider. All of the changes that have taken place since the beginning of the transaction will be committed, and all previously made data changes will revert to their previous values. Table 11-8: Error Object Properties Property Description Description* The descriptive string associated with a given error. This descriptive string can be set by ADO or by the data provider. HelpContext The value of a Help file’s context ID, if the accompanying Help- File property indicates that there is a Windows help file associated with an Error object. HelpFile A string that evaluates to the path and filename of a Windows Help file if one exists for the Error object. NativeError The error code raised by the native data provider. This is a Long value. Number* A Long that represents the error number for the Error object. If no error has occurred, the Number property evaluates to 0. Source* A string that represents the name of the object or application that caused the ADO error. SQLState A five-character error code that the provider returns when an operation involving the processing of a SQL statement raises an error. The values of these error codes are documented in the current ANSI SQL standard. Table 11-7: Connection Object Methods (continued) Method Description 168 Chapter 11 – ActiveX Data Objects 1.5 Object Model ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. Field Object Each recordset you create is made up of a collection of Field objects. A Field object represents the data from a specific column in the query or table called from the data provider by the ADO application. All the data in a given field in the recordset has the same data type. The Value property of the Field object represents the actual field value for that field in the current record. The Field object allows you to view or change the data in a field of a record in your recordset. Tables 11-9, 11-10, and 11-11 list its properties, collections, and methods, respectively. Table 11-9: Field Object Properties Property Description ActualSize A Long that represents the size of the field’s value in number of characters. Some data providers allow the user to set this property to reserve space for BLOB data. However, most often this is a read-only property. Attributes Allows you to retrieve several different characteristics of the Field object, such as whether the data for a field is retrieved with the rest of the record or only when you specifically use the field, whether you can change the value of the field, etc. This is a read-only property. DefinedSize The size of the Field object. This is different from the Actual- Size property. The value of the ActualSize property could indicate that the length of the value in a Field object is one character, but the value of the DefinedSize property could be larger. You can use DefinedSize to determine if a value you want to input into a new record’s field is larger than the size of the field. Name* The field’s name from the database table or query. It is readonly. NumericScale The number of decimal places to which numeric values will be resolved. The data type of this property is Byte. OriginalValue The actual value of the field before any changes were made. This property value allows you to programmatically revert the field’s value. Precision The number of significant digits to which numeric values will be resolved. The data type of this property is Byte. Type An integer that represents the data type of the field’s contents. For the Field object, this is a read-only property. Table 11-10: Field Object Collections Collection Description Properties Contains the data provider-specific properties for the Field object. If the data provider does not support any custom properties, this collection is empty. Object Model 169 ActiveX Data Objects 1.5 Object Model ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. Parameter Object A Parameter object holds the values of specific parameters for a parameterized Command object. In other words, if a given SQL statement or other command takes a given set of parameters that change each time you execute the command, a Parameter object can be used to hold those parameters’ values. Each instantiated Command object has a Parameters collection to which you can add parameters. In addition to holding parameters of a straight SQL statement, a Parameter object can also represent the in/out or return values of a stored procedure. Table 11-12 lists the Parameter object’s properties, while Tables 11-13 and 11-14 show that it supports a single collection and a single method, respectively. Table 11-11: Field Object Methods Method Description AppendChunk Appends a large amount of text or a Binary object to a Field object. GetChunk Retrieves a large amount of text or a Binary object from a Field object. Table 11-12: Parameter Object Properties Property Description Attributes Sets or determines whether a given parameter will accept various data, such as signed values, null values, or long values. Direction Reflects whether the parameter represents an input parameter, an output parameter, or both. Name* The name of the parameter, if it has one. NumericScale The number of decimal places to which numeric values will be resolved. The data type of this property is Byte. Precision The number of significant digits to which numeric values will be resolved. The data type of this property is Byte. Size A Long representing the maximum number of bytes or characters valid for the Parameter object. Type An integer that represents the data type of the parameter’s contents. For the Parameter object, this is a read/write property. Value The actual value of the contents of the parameter. Table 11-13: Parameter Object Collection Collection Description Properties Contains the data provider-specific properties for the Field object. If the data provider does not support any custom properties, this collection is empty. 170 Chapter 11 – ActiveX Data Objects 1.5 Object Model ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. Property Object A Property object represents a custom or unique property that is specific to ADO objects instantiated using a specific data provider. For example, if a Recordset object were instantiated using records from an Oracle database, that recordset may have special properties not supported by a “typical” ADO. You would retrieve/set the values for these properties using an ADO Property object. This advanced feature of ADO allows you to take full command of your underlying data provider. Each Command, Connection, and Recordset object you instantiate maintains its own Properties collection. This way, you have access to custom properties of your data provider for all three object types. Table 11-15 lists the Property object’s properties. Recordset Object A Recordset object represents the records returned from a query (or table) and a cursor into those records. When instantiating a Recordset object, you can automatically create a connection to the underlying data provider on opening the recordset. However, if you use an already-open Connection object for your recordset, you can significantly reduce your memory consumption overhead, since each Connection object can maintain multiple recordsets. However, if you open a recordset without using an already open Connection object, that connection can support only that single recordset. You can read more about this feature in the section on the Open method of the Recordset object near the end of this chapter. Table 11-14: Parameter Object Method Method Description AppendChunk Appends a large amount of text or a Binary object to a Parameter object. Table 11-15: Property Object Properties Property Description Attributes Allows you to determine whether a given property is supported, required, optional, and whether Property is read-only or read/write. Name* The underlying name assigned by the data provider to a given property that’s being manipulated through the use of a Property object. Type An integer that represents the data type of the property’s contents. For the Property object, this is a read-only property. Value The actual value of the property. Object Model 171 ActiveX Data Objects 1.5 Object Model ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. Tables 11-16, 11-17, and 11-18 list the Recordset object’s properties, collections, and methods, respectively. Table 11-16: Recordset Object Properties Property Description AbsolutePage Allows you to determine the exact page of records in which the current record resides. Each recordset is broken up by the data provider and ADO into pages of PageSize number of records, with the last page possibly containing fewer records. This is a read-only value of type Long. AbsolutePosition* The ordinal number of the current record in the recordset. This is a read/write value of type Long. ActiveConnection* The currently open Connection object to which the recordset is affiliated. BOF* Indicates whether the current record pointer is pointing to the beginning of the recordset (i.e., the beginning of file), which is one position earlier in the recordset than the first record. If you use the MovePrevious method to move one position before the first record in the recordset, the BOF property will evaluate to True. This is a Boolean read-only value. Bookmark Allows you to retrieve a unique identification number for the current record in the recordset. If you set this property to a valid bookmark for another record, the current record pointer will be moved to the record identified by the value you set. CacheSize The number of records cached locally in memory. The default of this Long value is 1. If you change this value in code, be aware that the value of the CacheSize property must be greater than 1, and that the value you set has a direct relationship on performance. Forcing the server to cache more than one record locally increases memory consumption per user and decreases performance. CursorLocation Indicates to the web server where the cursor for the current recordset should be created: on the client (adUseClient from ADOVBS.INC) or on the server (adUseServer, the default). CursorType* The type of cursor ADO creates to the underlying data provider. This is an integer whose value is read-only if the Recordset object is already opened, but read/write if it is closed. The default value for this property is adOpenForwardOnly. EditMode The current editing state for the current record. The value of this property indicates whether there is an edit in progress, whether a record has been edited but not saved, or whether a new record is to be added to the recordset. 172 Chapter 11 – ActiveX Data Objects 1.5 Object Model ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. EOF* Indicates whether the current record pointer is pointing to the end of the recordset (i.e., end of file), which is one position after the last record in the recordset. If you use the Recordset object’s MoveNext method to move one position after the last record, the EOF property will evaluate to True. This is a Boolean read-only value. Filter* Allows you to selectively filter out records from being visible in a recordset. LockType Reflects the current locking scheme placed on the records during editing. For example, Read-Only, Pessimistic, Optimistic, or BatchOptimistic. MarshalOptions Sets or retrieves a setting that determines how records are marshaled between the client and server. Marshaling involves packaging and sending groups of records from the client to the server. This property determines whether only those records that have been modified or all records are marshaled back to the server. MaxRecords* Sets or retrieves the maximum number of records returned in a recordset by a specific query. This is a Long value with a default of 0, meaning that there is no maximum. PageCount Determines how many pages of records were returned by the data provider into a specific Recordset object. If the data provider does not support this property or if the page count is for some other reason undeterminable, the value of this integer is –1. PageSize The total number of records that make up one page of records. This Long value is –1 if the data provider does not support the PageSize property or if the page size is undeterminable. RecordCount* For Recordset objects that support approximate positioning or bookmarks, the RecordCount property represents the exact number of records returned into the Recordset object. If this property is unsupported by the underlying data provider or is for some other reason undeterminable, its value is –1. Source* The source string from which the records were returned from the data provider. The value of the property could be a SQL string, a stored procedure, the name of a Command object, or a table name. State An integer value representing whether the Recordset object is open (adStateOpen from ADOVBS.INC) or closed (adStateClosed). Status The status of the current record in relation to a batch update or other bulk manipulation of the data. Table 11-16: Recordset Object Properties (continued) Property Description Object Model 173 ActiveX Data Objects 1.5 Object Model ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. Table 11-17: Recordset Object Collections Collection Description Fields Contains each Field object, corresponding to each column of data in the recordset. Properties Contains the data provider-specific properties for the Recordset object. If the data provider does not support any custom properties, this collection is empty. Table 11-18: Recordset Object Methods Method Description AddNew* Adds a new record to the recordset and to the underlying data if the recordset is updateable. CancelBatch Cancels all pending updates if a recordset is in batch-update mode. CancelUpdate Cancels updates to the current record. Clone* Creates a duplicate of the current recordset. Close* Closes the current recordset. Delete* Deletes the current record or a group of records from the recordset and the underlying data (if the cursor type of the recordset supports updating). GetRows Retrieves multiple records from a recordset into an array. Move* Moves the current record pointer a certain number of positions forward or backward from the current record. To use this method, the recordset must support both forward and backward movement. MoveFirst* Moves the record pointer to the first record in the recordset. MoveLast* Moves the record pointer to the last record of the recordset. MoveNext* Moves the record pointer forward one position. MovePrevious* Moves the record pointer back one position. NextRecordset* You can create a Recordset object using multiple commands. The NextRecordset method allows you to navigate from one command’s resulting recordset to another command’s resulting recordset. Open* Opens a recordset. Requery* Repopulates the current recordset by rerunning the command that generated it. Resync* Refreshes the data in the current recordset without rerunning the query. Supports* Determines whether a specific data provider supports a given functionality. Update* Saves changes to the current record into the database. UpdateBatch Saves all changes in the current batch to the database. 174 Chapter 11 – ActiveX Data Objects 1.5 Properties Reference ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. Properties Reference AbsolutePosition (Recordset Object) rsObj.AbsolutePosition (= intRecordPosition) Returns or sets the current record based on its ordinal position in the recordset. This is a read/write value of type Long. Parameters rsObj A reference to a Recordset object intRecordPosition The position of the current record or the new position to which you wish to move the record pointer Example The following example demonstrates the use of the AbsolutePosition property. The use of AbsolutePosition is in bold to distinguish it in this example. The other parts of the script will be used to demonstrate other parts of ADO. Also, in this example, you will notice that I am careful not to specify my data provider. The reason is that only some data providers support the AbsolutePosition property; SQL Server, for example, does not. <%@ LANGUAGE="VBSCRIPT" %> <%response.buffer = true%> <HTML> <HEAD> <TITLE>ADO Examples</TITLE> </HEAD> <BODY> <% ' Include ADOVBS.INC so we can use the ADO constants. %> <!-- #include virtual = "/MySSIncludes/adovbs.inc" --> <% ' Instantiate an ADO Connection object. Set objDBConn = Server.CreateObject("ADODB.Connection") ' Construct the connection string for the Connection ' object. strConn = _ "driver={MyDBType};;uid=sa;pwd=;database=SalesDB" ' Using the connection string, open the connection. objDBConn.Open strConn ' Instantiate an ADO Recordset object. Set rsHighSales = _ Server.CreateObject("ADODB.Recordset") AbsolutePosition (Recordset Object) 175 ActiveX Data Objects 1.5 AbsolutePosition (Recordset Object) ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. ' Set the CursorType property of the recordset, so we ' can navigate within the recordset. rsHighSales.CursorType = adOpenDynamic ' Set our CursorLocation to locate the cursor on the ' client side so we can use the AbsolutePosition ' property. rsHighSales.CursorLocation = adUseClient ' Construct the SQL to be used to open the recordset. strSQL = _ "SELECT Buyer, Price FROM Sales WHERE Price > 70000" ' Open the recordset. rsHighSales.Open strSQL, objDBConn ' Move to current record pointer to the third record ' in the recordset. rsHighSales.AbsolutePosition = 3 ' Display the Buyer and Price field values for the ' third record in the recordset. %> Third Buyer: <%=rsHighSales("Buyer")%><BR> Third Price: <%=rsHighSales("Price")%><BR> <% ' Release the memory consumed by objects. Set rsHighSales = Nothing Set objDBConn = Nothing %> </BODY> </HTML> Notes You can use AbsolutePosition to retrieve or set the position of the current record pointer within the recordset. This number is one-based, meaning that if you wish to set the current record pointer to point to the first record in the recordset, you would set its value to 1. As with other properties of the Recordset object, the validity of the AbsolutePosition property depends on whether the underlying data provider supports the property. Also, you can only use the AbsolutePosition property of the Recordset object if the cursor type for the recordset supports backward movement in the cursor. If you attempt to retrieve the position of the current record pointer, you may receive one of the following constant values, depending on the state of the current record pointer: adPosUnknown Either the recordset is empty, the current position is unknown, or the underlying data provider does not support this property. 176 Chapter 11 – ActiveX Data Objects 1.5 ActiveConnection (Command, Recordset Object) ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. adPosBOF The current record is one before the first record in the recordset. adPosEOF The current record is one after the last record in the recordset. Note that when you set a value for the AbsolutePosition property—even if the new current record is already in the cache—the cache is reloaded. The number of records loaded into the cache is determined by the CacheSize property. Finally, if you want to uniquely identify a given record, use the Bookmark property, rather than the AbsolutePosition value, because this value can change. ActiveConnection (Command, Recordset Object) Obj.ActiveConnection (= strConnectionName) Indicates an open Connection object to which a Recordset or Command object belongs. Parameters Obj A reference to a Command or Recordset object strConnectionName The name of a valid, open Connection object Example This example demonstrates how you set the ActiveConnection property of a Recordset object. To set the ActiveConnection property of a Command object, use exactly the same technique. <%@ LANGUAGE="VBSCRIPT" %> <% Response.Buffer = True %> <HTML> <HEAD> <TITLE>ADO Examples</TITLE> </HEAD> <BODY> <% ' Include ADOVBS.INC so we can use the ADO constants. %> <!-- #include virtual = "/MySSIncludes/adovbs.inc" --> <% ' Instantiate an ADO Connection object. Set objDBConn = Server.CreateObject("ADODB.Connection") ' Construct the connection string for the Connection ' object. strConn = _ "driver={SQL Server};;uid=sa;pwd=;database=SalesDB" ' Using the connection string, open the connection. objDBConn.Open strConn ActiveConnection (Command, Recordset Object) 177 ActiveX Data Objects 1.5 ActiveConnection (Command, Recordset Object) ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. ' Instantiate an ADO Recordset object. Set rsHighSales = _ Server.CreateObject("ADODB.Recordset") ' Set the ActiveConnection property of the recordset. rsHighSales.ActiveConnection = objDBConn ' Construct the SQL to be used to open the recordset. strSQL = _ "SELECT Buyer, Price FROM Sales WHERE Price > 70000" ' Open the recordset. Note the lack of a Connection ' object specification. rsHighSales.Open strSQL %> First Buyer: <%=rsHighSales("Buyer")%><BR> First Price: <%=rsHighSales("Price")%><BR> <% ' Release the memory consumed by objects. Set rsHighSales = Nothing Set objDBConn = Nothing %> </BODY> </HTML> Notes You can set or retrieve the name of any valid (open) Connection object by using the ActiveConnection property. If the Connection object is not yet open when you attempt to set the property, an error occurs. If you attempt to call the Execute method of a Command object or the Open method of a Recordset object without first setting the ActiveConnection property to the name of a valid, open Connection object, ADO will raise a runtime error. The only exception to this is if you use the ActiveConnection argument of the Recordset object’s Open method; in this case, the ActiveConnection property will be set for you to the name of the Connection object specified in the Open method call. If you set the ActiveConnection property to Nothing, you will disconnect the Command or Recordset object from the open Connection object. If you do this with a Recordset object that is open, an error will occur. Also, if a Command object has parameters whose values are provided by the data provider, and you set the ActiveConnection property of this Command object to Nothing or to another Connection object, these values will be cleared. If you set the values of the Parameter objects, resetting the ActiveConnection property has no effect on your parameters’ values. 178 Chapter 11 – ActiveX Data Objects 1.5 BOF (Recordset Object) ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. BOF (Recordset Object) rsObj.BOF If the value of the BOF property of a Recordset object is True, the current record pointer is positioned one record before the first record in the recordset. This is a read-only property. You can use the BOF property in conjunction with the EOF property to ensure that your recordset contains records and that you have not navigated beyond the boundaries of the recordset. Parameters rsObj A reference to a Recordset object Example The following example demonstrates the use of BOF to determine whether the opened recordset contains any records. Note that EOF is also True if there are no records in the recordset. We could just as easily have used the EOF property in this case as BOF. <%@ LANGUAGE="VBSCRIPT" %> <% Response.Buffer = True %> <HTML> <HEAD> <TITLE>ADO Examples</TITLE> </HEAD> <BODY> <% ' Include ADOVBS.INC so we can use the ADO constants. %> <!-- #include virtual = "/MySSIncludes/adovbs.inc" --> <% ' Instantiate an ADO Connection object. Set objDBConn = Server.CreateObject("ADODB.Connection") ' Construct the connection string for the Connection ' object. strConn = _ "driver={SQL Server};;uid=sa;pwd=;database=SalesDB" ' Using the connection string, open the connection. objDBConn.Open strConn ' Instantiate an ADO Recordset object. Set rsHighSales = _ Server.CreateObject("ADODB.Recordset") ' Set the ActiveConnection property of the recordset. rsHighSales.ActiveConnection = objDBConn ' Construct the SQL to be used to open the recordset. strSQL = _ "SELECT Buyer, Price FROM Sales WHERE Price > 70000" CommandText (Command Object) 179 ActiveX Data Objects 1.5 CommandText (Command Object) ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. ' Open the recordset. Note the lack of a Connection ' object specification. rsHighSales.Open strSQL ' Use the BOF property to determine whether there are ' records in the recordset. If Not rsHighSales.BOF Then ' There are records. Use the EOF property to loop ' through all the records in the recordset and ' display them to the screen. Do While Not rsHighSales.EOF %> Buyer: <%=rsHighSales("Buyer")%><BR> Price: <%=rsHighSales("Price")%><BR> <% rsHighSales.MoveNext Loop Else ' There are no records. Tell the user. %> There are no high sales. <% End If ' Release the memory consumed by objects. Set rsHighSales = Nothing Set objDBConn = Nothing %> </BODY> </HTML> Notes The BOF property’s value is True if there are no records in the recordset or if you have navigated to the position before the first record in the recordset. If there are no records in the recordset, the value of both the BOF and EOF properties are True. This is the only occasion in which this is true. Obviously, a True value of the BOF property indicates that some navigational methods (in particular, Move- Previous and Move using a negative argument) of the Recordset object are not allowed. CommandText (Command Object) objCmd.CommandText (= strCommandText) A string value that represents the actual command you wish to run against the database. The default value for this property is an empty string (“”). This command can be a SQL statement or the name of a stored procedure. Parameters objCmd A reference to a Command object 180 Chapter 11 – ActiveX Data Objects 1.5 CommandText (Command Object) ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. strCommandText A string containing the command you wish to run against the database Example This example demonstrates how to use the CommandText property to invoke a stored procedure with two parameters. <%@ LANGUAGE="VBSCRIPT" %> <% Response.Buffer = True %> <HTML> <HEAD> <TITLE>ADO Examples</TITLE> </HEAD> <BODY> <% ' Include ADOVBS.INC so we can use the ADO constants. %> <!-- #include virtual = "/bc_SSIncludes/adovbs.inc" --> <% ' Instantiate an ADO Connection object. Set objDBConn = Server.CreateObject("ADODB.Connection") ' Construct the connection string for the Connection ' object. strConn = _ "driver={MyDBType};;uid=sa;pwd=;database=SalesDB" ' Using the connection string, open the connection. objDBConn.Open strConn ' Create a stored procedure Command object, Set objSPCmd = Server.CreateObject("ADODB.Command") ' Set active connection equal to current Connection ' object. Set objSPCmd.ActiveConnection = objDBConn ' Set Command object type to stored procedure. objSPCmd.CommandType = adCmdStoredProc ' Set the parameter values. lngHighPrice = 70000 datFirstDate ='03/02/98' ' Set stored procedure command text. The parameters ' indicate the minimum price that must be paid to ' qualify a sale as a "high sale" and the date after ' which we want to collect sales into our recordset. strCommandString = "GetHighSales (" & lngHighPrice & _ "," & datFirstDate & ")" objSPCmd.CommandText = strCommandString ' Open the recordset using the results from the Command object. Set rsHighSales = objSPCmd.Execute CommandTimeout (Command, Connection Object) 181 ActiveX Data Objects 1.5 CommandTimeout (Command, Connection Object) ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. %> First Buyer: <%=rsHighSales("Buyer")%><BR> First Price: <%=rsHighSales("Price")%><BR> <% ' Release the memory consumed by objects. Set rsHighSales = Nothing Set objSPCmd = Nothing Set objDBConn = Nothing %> </BODY> </HTML> Notes If you use a SQL command for the CommandText property of a Command object, you must ensure that the SQL syntax is that expected by the underlying data provider. ADO will not translate from one “dialect” of SQL to another. Depending on the type of command (set using the CommandType property), ADO may alter the actual string sent to the data provider. For example, suppose you set the CommandText of a stored procedure-type Command object to the following: objSPCmd.CommandText = "GetHighSales (70000)" ADO will actually send the following string to the data provider as the command: { call GetHighSales (70000) } Notice that the braces and the call keyword are added. CommandTimeout (Command, Connection Object) Obj.CommandTimeout (= lngNumSeconds) Sets the maximum amount of time (in seconds) that ADO will wait for the results of a command to execute before raising an error. The default for this Long value is 30 seconds. Parameters Obj A reference to a Command or Connection object lngNumSeconds The number of seconds ADO will wait for the results of a command before raising an error Example This example demonstrates how to use the CommandTimeout property to increase the amount of time ADO will wait for the results of a stored procedure call before raising an error. <%@ LANGUAGE="VBSCRIPT" %> <% Response.Buffer = True %> 182 Chapter 11 – ActiveX Data Objects 1.5 CommandTimeout (Command, Connection Object) ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. <HTML> <HEAD> <TITLE>ADO Examples</TITLE> </HEAD> <BODY> <% ' Include ADOVBS.INC so we can use the ADO constants. %> <!-- #include virtual = "/bc_SSIncludes/adovbs.inc" --> <% ' Instantiate an ADO Connection object. Set objDBConn = Server.CreateObject("ADODB.Connection") ' Construct the connection string for the Connection ' object. strConn = _ "driver={MyDBType};;uid=sa;pwd=;database=SalesDB" ' Using the connection string, open the connection. objDBConn.Open strConn ' Create stored procedure command object. Set objSPCmd = Server.CreateObject("ADODB.Command") ' Set the active connection equal to the current ' Connection object. Set objSPCmd.ActiveConnection = objDBConn ' Set the Command object type to stored procedure. objSPCmd.CommandType = adCmdStoredProc ' Set the parameter values. lngHighPrice = 70000 datFirstDate ='03/02/98' ' Set stored procedure command text. The parameters ' indicate the minimum price that must be paid to ' qualify a sale as a "high sale" and the date after ' which we want to collect sales into our recordset. strCommandString = "GetHighSales (" & lngHighPrice & _ "," & datFirstDate & ")" objSPCmd.CommandText = strCommandString ' Set the Command object's CommandTimeout property so ' that ADO will wait 60 seconds for the results of the ' comand before raising an error. objDBCmd.CommandTimeout = 60 ' Open the recordset using the results from the Command ' object. Set rsHighSales = objDBCmd.Execute %> CommandType (Command Object) 183 ActiveX Data Objects 1.5 CommandType (Command Object) ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. First Buyer: <%=rsHighSales("Buyer")%><BR> First Price: <%=rsHighSales("Price")%><BR> <% ' Release the memory consumed by objects. Set rsHighSales = Nothing Set objDBCmd = Nothing Set objDBConn = Nothing %> </BODY> </HTML> Notes If you create and open a Connection object and set its CommandTimeout property and then use its name to set the ActiveConnection property of a previously instantiated Command object, the Command object does not inherit the Command- Timeout property value of the Connection object. If you set this property’s value to 0, the command will wait indefinitely for the results to be returned. It is imperative to remember the current setting of the Server object’s ScriptTimeout property. (The default of the ScriptTimeout property of the Server object is 90 seconds.) For example, suppose the ScriptTimeout is set to 30 seconds and the CommandTimeout for the Command object on an Active Server Page is set to 45 seconds. You may not be able to view the outcome of the command’s execution—regardless of whether the command is executed successfully by the data provider. CommandType (Command Object) objCmd.CommandType (= intCommandType) Sets or determines the type of command being executed using the Command object. The different types of command include text, stored procedure, and table. The default is Unknown. If you attempt to call the Execute method of a Command object without setting the CommandType property’s value, an error will occur for any type of command other than straight text. Parameters objCmd A reference to a Command object. intCommandType The type of command. It can be represented by any of the following constants: adCmdText The command is a text command, such as a simple SQL statement; CommandText is evaluated as a textual definition of a command. 184 Chapter 11 – ActiveX Data Objects 1.5 CommandType (Command Object) ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. adCmdTable The Command object represents a table; CommandText is evaluated as the name of a table. adCmdStoredProc The Command object represents a stored procedure; CommandText is evaluated as the name of a stored procedure in the underlying data provider. adCmdUnknown The Command object type is unknown; this is the default value. Example This example demonstrates how to use the CommandType property of the Command object to instruct ADO to treat the CommandText property’s value as the name of a stored procedure. <%@ LANGUAGE="VBSCRIPT" %> <% Response.Buffer = True %> <HTML> <HEAD> <TITLE>ADO Examples</TITLE> </HEAD> <BODY> <% ' Include ADOVBS.INC so we can use the ADO constants. %> <!-- #include virtual = "/bc_SSIncludes/adovbs.inc" --> <% ' Instantiate an ADO Connection object. Set objDBConn = Server.CreateObject("ADODB.Connection") ' Construct the connection string for the Connection ' object. strConn = "driver={MyDBType};;uid=sa;pwd=;database=SalesDB" ' Using the connection string, open the connection. objDBConn.Open strConn ' Create a stored procedure Command object. Set objSPCmd = Server.CreateObject("ADODB.Command") ' Set the active connection equal to the current ' Connection object. Set objSPCmd.ActiveConnection = objDBConn ' Set the Command object type to stored procedure. objSPCmd.CommandType = adCmdStoredProc ' Set the parameter values. lngHighPrice = 70000 datFirstDate ='03/02/98' ' Set the stored procedure command text. The parameters ConnectionString (Connection Object) 185 ActiveX Data Objects 1.5 ConnectionString (Connection Object) ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. ' indicate the minimum price that must be paid to ' qualify a sale as a "high sale" and the date after ' which we want to collect sales into our recordset. strCommandString = "GetHighSales (" & lngHighPrice & _ "," & datFirstDate & ")" objSPCmd.CommandText = strCommandString ' Set the Command object's CommandTimeout property so ' that ADO will wait 60 seconds for the results of the ' command before raising an error. objDBCmd.CommandTimeout = 60 ' Open the recordset using the results from the Command ' object. Set rsHighSales = objDBCmd.Execute %> First Buyer: <%=rsHighSales("Buyer")%><BR> First Price: <%=rsHighSales("Price")%><BR> <% ' Release the memory consumed by objects Set rsHighSales = Nothing Set objDBCmd = Nothing Set objDBConn = Nothing %> </BODY> </HTML> Notes Setting the value of the CommandType property optimizes the command’s performance. You do not have to set the CommandType property, however. If you do not know at design time the type of command that will be used in the Command- Text property, you can leave the value for this property at its default of adCmdUnknown. In this case, however, you will experience decreased performance because ADO is forced to take the value of the CommandText property and query the underlying data provider to determine how to execute the command. If you set the CommandType property incorrectly (to something other than adCmdUnknown) and attempt to call the object’s Execute method, ADO will raise a runtime error. ConnectionString (Connection Object) objConn.ConnectionString (= strConnectionString) ConnectionString specifies or retrieves the information used to establish an open connection to an underlying data provider. 186 Chapter 11 – ActiveX Data Objects 1.5 ConnectionString (Connection Object) ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. Parameters strConnectionString A string value made up of the following elements (in order) broken up by semicolons. If you do not provide any of the elements, you must still include its semicolon unless you also do not provide any of the elements after the omitted element. Provider= The name of the underlying OLE DB data provider for the connection. Data Source= The name of a data source for the underlying data provider. For example, for SQL Server or Access, this represents a registered ODBC data source name. User ID= The username to use when establishing the connection. Password= The password to use when establishing the connection. File Name= The name of a data provider-specific file. This could, for example, represent a text file containing preset connection information. Using a File Name element in your ConnectionString loads the provider into memory. For this reason, you cannot have both a Provider and a File Name element in your ConnectionString property value. Remote Provider= (For use with Remote Data Services only.) The name of the data provider to use on the server when opening a client-side connection. Remote Server= (For use with Remote Data Services only.) The path name of the remote server to use when opening a client-side connection. Example <%@ LANGUAGE="VBSCRIPT" %> <% Response.Buffer = True %> <HTML> <HEAD> <TITLE>ADO Examples</TITLE> </HEAD> <BODY> <% ' Include ADOVBS.INC so we can use the ADO constants. %> <!-- #include virtual = "/bc_SSIncludes/adovbs.inc" --> <% ' Instantiate an ADO Connection object. Set objDBConn = Server.CreateObject("ADODB.Connection") ' Build the connection string for the Connection object. strConn = _ ConnectionTimeout (Connection Object) 187 ActiveX Data Objects 1.5 ConnectionTimeout (Connection Object) ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. "Provider={SQL Server};Data Source=SalesDB;User ID=sa;Password=;” ' Using the ConnectionTimeout property increases the ' amount of time ADO will wait while establishing a ' connection to the data provider before it raises ' an error. objDBConn.ConnectionTimeout = 60 ' Using the connection string, open the connection. objDBConn.Open strConn ...[additional code] Notes ADO recognizes only the first seven elements of a ConnectionString property value. However, you can provide as many as you like. If you provide more than seven, the extra elements are passed directly through to the data provider without any intervening actions being taken by ADO. The underlying data provider may alter the contents of the ConnectionString property value when the connection is established. If you use the ConnectionString parameter of the Connection object’s Open method and also set a value for the ConnectionString property before calling the Open method, the value passed to the Open method is the value that the ConnectionString property eventually receives. ConnectionTimeout (Connection Object) objConn.ConnectionTimeout (= lngNumSeconds) Sets or retrieves the number of seconds ADO will wait while attempting to establish a connection before raising an error. The default value for this property is 15 seconds. Parameters lngNumSeconds A Long that represents the number of seconds ADO will wait while attempting to establish a connection to the underlying data provider. Example For an example of the ConnectionTimeout property, see the example for the ConnectionString property. Notes You can instruct ADO to wait indefinitely for the connection to the underlying data provider to be established by setting the value of the ConnectionTimeout property to 0. Note, however, that it is imperative to remember the current setting of the Server object’s ScriptTimeout property. (The default for the ScriptTimeout property is 90 seconds.) For example, suppose the ScriptTimeout is set to 30 seconds and the 188 Chapter 11 – ActiveX Data Objects 1.5 CursorType (Recordset Object) ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. ConnectionTimeout for the Connection object on an Active Server Page is set to 45 seconds. You may not be able to see the result of attempting to establish a connection to the underlying data provider regardless of success or failure. CursorType (Recordset Object) rsObj.CursorType (= intCursorType) The CursorType property of the Recordset object allows you to specify or retrieve the type of cursor used to create the recordset. Parameters intCursorType An integer value representing the type of cursor to use for the Recordset object. It can be any of the following constants: adOpenForwardOnly This is the default. A forward-only cursor, as its name implies, only allows movement forward from the current record. Otherwise, this cursor type is identical to the static cursor. There is one exception to this, however: some data providers will allow you to call the MoveFirst method to move the current record pointer back to the first record in the database. This is the fastest cursor type. adOpenKeyset In a keyset cursor, you cannot see new records added by other users, and you cannot access records that have been deleted by other users. You can, however, see the changes to records in your recordset made by other users. All types of movement are possible in a keyset-cursor recordset. adOpenDynamic Dynamic cursors are the most flexible (and slowest) of the four types. In a dynamic cursor, additions, changes, and deletions are all visible in your recordset. All types of movement are possible in a dynamic-cursor recordset. adOpenStatic Static cursors provide a static snapshot of the records in your recordset. This is useful for generating reports, but the records in the recordset are not updateable. Additions, changes, and deletions made by other users are not visible in your recordset. Example <%@ LANGUAGE="VBSCRIPT" %> <%response.buffer = true%> <HTML> <HEAD> <TITLE>ADO Examples</TITLE> </HEAD> <BODY> <% CursorType (Recordset Object) 189 ActiveX Data Objects 1.5 CursorType (Recordset Object) ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. ' Include ADOVBS.INC so we can use the ADO constants. %> <!-- #include virtual = "/MySSIncludes/adovbs.inc" --> <% ' Instantiate an ADO Connection object. Set objDBConn = Server.CreateObject("ADODB.Connection") ' Construct the connection string for the Connection ' object. strConn = _ "driver={MyDBType};;uid=sa;pwd=;database=SalesDB" ' Using the connection string, open the connection. objDBConn.Open strConn ' Instantiate an ADO Recordset object. Set rsHighSales = _ Server.CreateObject("ADODB.Recordset") ' Set the CursorType property of the recordset, so we ' can navigate within the recordset. rsHighSales.CursorType = adOpenDynamic ' Set our CursorLocation to locate the cursor on the ' client side so we can use the AbsolutePosition property. rsHighSales.CursorLocation = adUseClient ' Construct the SQL to be used to open the recordset. strSQL = _ "SELECT Buyer, Price FROM Sales WHERE Price > 70000" ' Open the recordset. rsHighSales.Open strSQL, objDBConn ' Move the current record pointer to the third record. ' in the recordset. rsHighSales.AbsolutePosition = 3 ' Display the Buyer and Price field values for the ' third record in the recordset. %> Third Buyer: <%=rsHighSales("Buyer")%><BR> Third Price: <%=rsHighSales("Price")%><BR> <% ' Release the memory consumed by objects. Set rsHighSales = Nothing Set objDBConn = Nothing %> </BODY> </HTML> 190 Chapter 11 – ActiveX Data Objects 1.5 Description (Error Object) ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. Notes The CursorType property of the Recordset object is read-only if the recordset is already open but read/write otherwise. If you attempt to set the CursorType property to a value not supported by your underlying data provider, the data provider may return a cursor of a different type than you set. However, if this happens, the CursorType property value reflects this change. Then, once the recordset is closed, the CursorType property value reverts to the value you set. You can use the Supports method of the Recordset object to determine which cursors are supported by a given data provider, according to Table 11-19. What happens if one of the tests on the right fail for your selected cursor type? Suppose you attempt to set the CursorType to one of the cursor type constants in the right column, but one or more of the Supports method calls in the left column returns False. The result is unpredictable, but most often the underlying data provider will simply change the cursor type when you attempt to open the recordset. Description (Error Object) objError.Description A read-only string that provides textual information describing the error that the underlying data provider raised in response to incorrect syntax or lack of support. Description is a property of each Error object in the Connection object’s Errors collection. It is not the same as the Description property of the ASP Err object. Parameters None Example The following example demonstrates the use of the Description property of the Error object. Notice that for this example to work properly, the Response object’s Buffer property must be set to True because we use the Response’s object’s Clear and End methods. <%@ LANGUAGE="VBSCRIPT" %> Table 11-19: Determining if a Data Provider Supports a Cursor Type If Supported, Method Returns True with These Parameters Cursor Type Supported None adOpenForwardOnly adBookmark, adHoldRecords, adMovePrevious, adResync adOpenKeyset adMovePrevious adOpenDynamic adBookmark, adHoldRecords, adMovePrevious, adResync adOpenStatic Description (Error Object) 191 ActiveX Data Objects 1.5 Description (Error Object) ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. <%Response.Buffer = True%> <HTML> <HEAD> <TITLE>ADO Examples</TITLE> </HEAD> <BODY> <% ' Include ADOVBS.INC so we can use the ADO constants. %> <!-- #include virtual = "/MySSIncludes/adovbs.inc" --> <% ' Instantiate an ADO Connection object. Set objDBConn = Server.CreateObject("ADODB.Connection") ' Construct the connection string for the Connection ' object. strConn = _ "driver={MyDBType};;uid=sa;pwd=;database=SalesDB" ' Using the connection string, open the connection. objDBConn.Open strConn ' Check if attempting to open a connection to the ' provider resulted in ADO adding Error objects to the ' Connection's Errors collection. If objDBConn.Errors.Count > 0 Then ' An error occurred and ADO added an Error object to ' the Connection's Errors collection. Clear the ' Response buffer and alert the user of the error. Response.Clear Response.Write _ "One or more errors have occurred.<BR>" For intCounter = 0 to objDBConn.Errors.Count Response.Write "The " & intCounter & " error's " Response.Write "error number is " & _ objDBConn.Errors(intCounter).Number & ".<BR>" Response.Write "The description for this " Response.Write "error is <BR>" & _ objDBConn.Errors(intCounter).Description & ".<BR>" Next Response.End End If ...[additional code] Notes Each time an error occurs in the data provider, ADO adds an Error object to the Errors collection of the Connection object corresponding to that data provider. The provider is responsible for generating and sending the actual error text to ADO, but ADO can modify it before setting the description that it adds to the Connection object’s Errors collection. 192 Chapter 11 – ActiveX Data Objects 1.5 EOF (Recordset Object) ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. EOF (Recordset Object) rsObj.EOF If the value of a Recordset object’s EOF property is True, the current record pointer is positioned one record after the last record in the recordset. This is a read-only property. You can use the EOF property in conjunction with the BOF property to ensure that your recordset contains records and that you have not navigated beyond the boundaries of the recordset. Note that the value of EOF is also True if there are no records in the recordset. Parameters None Example The following example demonstrates the use of EOF to iterate through a set of records. Assuming that there are records in the recordset, we know that EOF will be true once we have iterated through all the records and the record pointer is pointing at the position after the last record in the recordset. <%@ LANGUAGE="VBSCRIPT" %> <% Response.Buffer = True %> <HTML> <HEAD> <TITLE>ADO Examples</TITLE> </HEAD> <BODY> <% ' Include ADOVBS.INC so we can use the ADO constants. %> <!-- #include virtual = "/MySSIncludes/adovbs.inc" --> <% ' Instantiate an ADO Connection object. Set objDBConn = Server.CreateObject("ADODB.Connection") ' Construct the connection string for the Connection ' object. strConn = _ "driver={SQL Server};;uid=sa;pwd=;database=SalesDB" ' Using the connection string, open the connection. objDBConn.Open strConn ' Instantiate an ADO Recordset object. Set rsHighSales = _ Server.CreateObject("ADODB.Recordset") ' Set the ActiveConnection property of the recordset. rsHighSales.ActiveConnection = objDBConn ' Construct the SQL to be used to open the recordset. strSQL = _ "SELECT Buyer, Price FROM Sales WHERE Price > 70000" Filter (Recordset Object) 193 ActiveX Data Objects 1.5 Filter (Recordset Object) ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. ' Open the recordset. Note the lack of a connection ' object specification. rsHighSales.Open strSQL ' Use the BOF property to determine whether there ' are records in the recordset. If Not rsHighSales.BOF Then ' There are records. Use the EOF property to loop ' through all the records in the recordset and ' display them to the screen. Do While Not rsHighSales.EOF %> Buyer: <%=rsHighSales("Buyer")%><BR> Price: <%=rsHighSales("Price")%><BR> <% rsHighSales.MoveNext Loop Else ' There are no records. Tell the user. %> There are no high sales. <% End If ' Release the memory consumed by objects. Set rsHighSales = Nothing Set objDBConn = Nothing %> </BODY> </HTML> Notes The EOF property’s value is True in the following instances: • There are no records in the recordset. In this case, the value of both the BOF and EOF properties is True. This is the only occasion on which this is true. • You have navigated to the position after the last record in the recordset. Obviously, the value of the EOF property indicates that some navigational methods of the Recordset object (MoveNext and Move using a positive argument) are not allowed. Filter (Recordset Object) rsObj.Filter (= vntFilterCriteria) The Filter property of the Recordset object allows you to view a subset of records contained in the recordset. This subset could match a more exact set of criteria than the criteria used to create the original recordset. When you are finished using the subset of records, you can restore the view of the recordset back to its original state of displaying all the records. Using a filter does not remove records from 194 Chapter 11 – ActiveX Data Objects 1.5 Filter (Recordset Object) ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. the recordset but only makes them unviewable. For this reason, restoring the recordset to its original state does not require requerying the database. Parameters vntFilterCriteria Controls what records will appear in the filtered view of your recordset. This variant value can contain any one of the following filtering types: Criteria text string Criteria strings are basically equivalent to SQL WHERE clauses without the WHERE keyword. For example, suppose your recordset (adoRec) were constructed using the following SQL statement: SELECT SalesPrice, Cost, Buyer FROM Sales You could then apply a filter to this recordset to show only those sales with prices of more than $1000 by using the following line of code: adoRec.Filter = "SalesPrice > 1000" Bookmark array You can set the Filter property to the name of an array of bookmarks that point to records in the recordset. These bookmarks are retrieved using the Bookmark property of the Recordset object for a specific record. ADO filter constant These ADO filter constants provide special filtering criteria not easily obtained otherwise: The adFilterNone constant restores the recordset view to allow viewing of all the records in the recordset. The adFilterPendingRecords constant retrieves only those records that have been changed but not yet updated on the server. The adFilterAffectedRecords constant retrieves only those records affected by the Recordset object’s Delete, Resync, UpdateBatch, or CancelBatch methods. The adFilterFetchedRecords constant retrieves all the records in the current cache—i.e., all those records retrieved from the last command on the database. Example The following example demonstrates the use of the Filter property. The important sections are in bold. Assume that the first recordset (before applying the Filter property) consists of the following records, in the following order: <%@ LANGUAGE="VBSCRIPT" %> <%response.buffer = true%> Buyer Price Chris 70000 Toby 80000 Simon 90345 Dave 100000 Mark 78658 Josh 89000 Filter (Recordset Object) 195 ActiveX Data Objects 1.5 Filter (Recordset Object) ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. <HTML> <HEAD> <TITLE>ADO Examples</TITLE> </HEAD> <BODY> <% ' Include ADOVBS.INC so we can use the ADO constants. %> <!-- #include virtual = "/MySSIncludes/adovbs.inc" --> <% ' Instantiate an ADO Connection object. Set objDBConn = Server.CreateObject("ADODB.Connection") ' Construct the connection string for the Connection ' object. strConn = _ "driver={MyDBType};;uid=sa;pwd=;database=SalesDB" ' Using the connection string, open the connection. objDBConn.Open strConn ' Instantiate an ADO Recordset object. Set rsHighSales = _ Server.CreateObject("ADODB.Recordset") ' Construct the SQL to be used to open the recordset. strSQL = _ "SELECT Buyer, Price FROM Sales WHERE Price > 70000" ' Open the recordset. rsHighSales.Open strSQL, objDBConn ' Display the Buyer and Price field values for the ' current (first) record in the new recordset BEFORE ' applying the filter. The first buyer will be Chris ' and the first price will be 70,000. There are also ' six viewable records at this point. %> Current (first) Buyer: <%=rsHighSales("Buyer")%><BR> Current (first) Price: <%=rsHighSales("Price")%><BR> <% ' Now apply a criteria string to the Filter property to ' filter out some of the records. rsHighSales.Filter = "Price > 80000" ' Again, display the Buyer and Price field values for ' the current (first) record in the new recordset. The ' first buyer will NOW be Simon, and the first price ' will be 90,345. Now only three records are viewable. %> 196 Chapter 11 – ActiveX Data Objects 1.5 MaxRecords (Recordset Object) ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. Current (first) Buyer: <%=rsHighSales("Buyer")%><BR> Current (first) Price: <%=rsHighSales("Price")%><BR> <% ' Release the memory consumed by objects Set rsHighSales = Nothing Set objDBConn = Nothing %> </BODY> </HTML> Notes The Filter property allows you to easily filter out the records that you don’t need from a large recordset without having to requery the database. Once you have finished with the records that appear in the filtered view of the recordset, you can restore the view all of the records without requerying the database. Once you have created your filtered recordset, the set of filtered records becomes the current cursor. This is a very convenient way to narrow a set of records without creating a new query and executing it against the database. However, it is important to recognize that although the Filter property is convenient, it will never be faster than simply honing the query that you send to the data provider. If records in the underlying database have been affected since you populated your recordset (e.g., if a record has been deleted from the underlying table), information will be added to the Errors collection. However, this will result only in warnings unless every record in the filtered recordset results in an error. When you set the Filter property, the current record pointer moves to the first record in the subset of records that meet the requirements in the Filter string. If you reset the recordset, the current record pointer goes back to the first record in the recordset that meets the criteria in the original command that makes up the recordset. In addition to being able to reset the recordset using the ADO adFilterNone constant, you also can achieve the same result by setting the Filter property value to an empty string. MaxRecords (Recordset Object) rsObj.MaxRecords (= lngNumRecords) Specifies the maximum number of records returned from a command. If set to zero (0), this property indicates that the data provider should return all records that meet the criteria in the command. This is the default. Parameters lngNumRecords A Long value that represents the maximum number of records you want returned from your command against the database MaxRecords (Recordset Object) 197 ActiveX Data Objects 1.5 MaxRecords (Recordset Object) ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. Example The following example sets the MaxRecords property so that it returns only four records. <%@ LANGUAGE="VBSCRIPT" %> <% Response.Buffer = True %> <HTML> <HEAD> <TITLE>ADO Examples</TITLE> </HEAD> <BODY> <% ' Include ADOVBS.INC so we can use the ADO constants. %> <!-- #include virtual = "/MySSIncludes/adovbs.inc" --> <% ' Instantiate an ADO Connection object. Set objDBConn = Server.CreateObject("ADODB.Connection") ' Construct the connection string for the Connection ' object. strConn = _ "driver={SQL Server};;uid=sa;pwd=;database=SalesDB" ' Using the connection string, open the connection. objDBConn.Open strConn ' Instantiate an ADO Recordset object. Set rsHighSales = _ Server.CreateObject("ADODB.Recordset") ' Set the ActiveConnection property of the recordset. rsHighSales.ActiveConnection = objDBConn ' Construct the SQL to be used to open the recordset. strSQL = _ "SELECT Buyer, Price FROM Sales WHERE Price > 70000" ' Set the maximum number of records the data provider ' can return into your recordset to four records. rsHighSales.MaxRecords = 4 ' Open the recordset. Note the lack of a Connection ' object specification. rsHighSales.Open strSQL ' Use the BOF property to determine whether there ' are records in the recordset. If Not rsHighSales.BOF Then %> Buyer: <%=rsHighSales("Buyer")%><BR> Price: <%=rsHighSales("Price")%><BR> <% 198 Chapter 11 – ActiveX Data Objects 1.5 Name (Command, Field, Parameter, Property Object) ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. Else ' There are no records. Tell the user. %> There are no high sales. <% End If ' Release the memory consumed by objects. Set rsHighSales = Nothing Set objDBConn = Nothing %> </BODY> </HTML> Notes The MaxRecords property is read/write if the Recordset object is closed but readonly if it is open. This is functionally equivalent to the SET ROWS command in ANSI SQL. Name (Command, Field, Parameter, Property Object) Obj.Name (= strObjName) Each Command, Field, Parameter, and Property object has a Name property that is a string value that identifies that object. The value for the Name property does not have to be unique within a collection. Note, however, that if two objects in a collection have the same name, you must use its ordinal position rather than just its name to ensure you retrieve the correct one. For example, suppose you have a recordset with two field objects both with the name “SalesPerson.” The first Sales- Person field is the first in the collection and the second is the fifth. The following line of code will always retrieve the value in the first column only: strEmployee = rsSales("SalesPerson") To retrieve the value of the second SalesPerson field, you must use its ordinal reference: strSecondEmployee = rsSales.Fields(5).Value Parameters strObjName A string value that represents the name of the object Example The following example demonstrates the use of the Name property to retrieve the names of the first and second Field objects in the Fields collection of the rsHighSales Recordset object. <%@ LANGUAGE="VBSCRIPT" %> <% Response.Buffer = True %> <HTML> <HEAD> <TITLE>ADO Examples</TITLE> Name (Command, Field, Parameter, Property Object) 199 ActiveX Data Objects 1.5 Name (Command, Field, Parameter, Property Object) ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. </HEAD> <BODY> <% ' Include ADOVBS.INC so we can use the ADO constants. %> <!-- #include virtual = "/MySSIncludes/adovbs.inc" --> <% ' Instantiate an ADO Connection object. Set objDBConn = Server.CreateObject("ADODB.Connection") ' Construct the connection string for the Connection object. strConn = _ "driver={SQL Server};;uid=sa;pwd=;database=SalesDB" ' Using the connection string, open the connection. objDBConn.Open strConn ' Instantiate an ADO Recordset object. Set rsHighSales = _ Server.CreateObject("ADODB.Recordset") ' Set the ActiveConnection property of the recordset. rsHighSales.ActiveConnection = objDBConn ' Construct the SQL to be used to open the recordset. strSQL = _ "SELECT Buyer, Price FROM Sales WHERE Price > 70000" ' Open the recordset. Note the lack of a Connection ' object specification. rsHighSales.Open strSQL ' Use the BOF property to determine whether there ' are records in the recordset. If Not rsHighSales.BOF Then ' There are records. Use the EOF property to loop ' through all the records in the recordset and ' display them to the screen. Do While Not rsHighSales.EOF %> <%=rsHighSales.Fields(0).Name%>: <%=rsHighSales("Buyer")%><BR> <%=rsHighSales.Fields(1).Name %>: <%=rsHighSales("Price")%><BR> <% rsHighSales.MoveNext Loop Else ' There are no records. Tell the user. %> There are no high sales. <% End If ' Release the memory consumed by objects. 200 Chapter 11 – ActiveX Data Objects 1.5 Number (Error Object) ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. Set rsHighSales = Nothing Set objDBConn = Nothing %> </BODY> </HTML> Notes You can retrieve or set the name of a Command, Field, Parameter, or Property object. However, there are some exceptions. First, if a Parameter object has already been added to a Command object’s Parameters collection, you cannot set its Name property. Also, if a Field object is part of the Fields collection of an open Recordset object, you cannot set its name. Number (Error Object) objError.Number A read-only string that provides the error code number that the underlying data provider raised in response to incorrect syntax or lack of support. This Number property is a property of each Error object in the Connection object’s Errors collection. It is not the same as the Number property of the ASP Err object. Parameters None Example The following example demonstrates the use of the Number property of the Error object. Notice that for this example to work properly, the Buffer property of the Response object must be set to True because we use the Response object’s Clear, and End methods. <%@ LANGUAGE="VBSCRIPT" %> <%Response.Buffer = True%> <HTML> <HEAD> <TITLE>ADO Examples</TITLE> </HEAD> <BODY> <% ' Include ADOVBS.INC so we can use the ADO constants. %> <!-- #include virtual = "/MySSIncludes/adovbs.inc" --> <% ' Instantiate an ADO Connection object. Set objDBConn = Server.CreateObject("ADODB.Connection") ' Construct the connection string for the Connection object. strConn = _ "driver={MyDBType};;uid=sa;pwd=;database=SalesDB" ' Using the connection string, open the connection. RecordCount (Recordset Object) 201 ActiveX Data Objects 1.5 RecordCount (Recordset Object) ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. objDBConn.Open strConn ' Check to see if attempting to open a connection to ' the provider resulted in ADO adding Error objects to ' the Connection's Errors collection. If objDBConn.Errors.Count > 0 Then ' An error occurred and ADO added an Error object to ' the Connection's Errors collection. Clear the ' Response buffer and alert the user of the error. Response.Clear Response.Write _ "One or more errors has occurred.<BR>" For intCounter = 0 to objDBConn.Errors.Count Response.Write "The " & intCounter & " error's " Response.Write "error number is " & _ ojDBConn.Errors(intCounter).Number & ".<BR>" Response.Write "The description for this " Response.Write "error is <BR>" & _ ojDBConn.Errors(intCounter).Description & ".<BR>" Next Response.End End If ...[additional code] Notes Each time an error occurs in the data provider, ADO adds an Error object to the Errors collection of the Connection object corresponding to that data provider. The provider is responsible for generating and sending the actual error text to ADO. The value of the Number property is unique for each error. RecordCount (Recordset Object) rsObj.RecordCount Provides you with the current number of records in the Recordset object (or the number of records in the Recordset object that meet the criteria in the Filter property, if one is supplied). If ADO cannot ascertain the total number of records, the value of this property is –1. The Recordset object must be open before you can retrieve a value for this property. Also, the Recordset object must be of a cursor type that supports movement (forward and backward) or it must be fully populated before the value for the RecordCount property is accurate. Parameters None Example <%@ LANGUAGE="VBSCRIPT" %> <% Response.Buffer = True %> <HTML> <HEAD> <TITLE>ADO Examples</TITLE> </HEAD> 202 Chapter 11 – ActiveX Data Objects 1.5 RecordCount (Recordset Object) ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. <BODY> <% ' Include ADOVBS.INC so we can use the ADO constants. %> <!-- #include virtual = "/MySSIncludes/adovbs.inc" --> <% ' Instantiate an ADO Connection object. Set objDBConn = Server.CreateObject("ADODB.Connection") ' Construct the connection string for the Connection ' object. strConn = _ "driver={SQL Server};;uid=sa;pwd=;database=SalesDB" ' Using the connection string, open the connection. objDBConn.Open strConn ' Instantiate an ADO Recordset object. Set rsHighSales = _ Server.CreateObject("ADODB.Recordset") ' Set the ActiveConnection property of the recordset. rsHighSales.ActiveConnection = objDBConn ' Set the recordset's cursor type to adOpenStatic so ' that the recordset supports the RecordCount property. rsHighSales.CursorType = adOpenStatic ' Construct the SQL to be used to open the recordset. strSQL = _ "SELECT Buyer, Price FROM Sales WHERE Price > 70000" ' Open the recordset. Note the lack of a Connection ' object specification. rsHighSales.Open strSQL ' Use the BOF property to determine whether there ' are records in the recordset. If Not rsHighSales.BOF Then ' There are records. Use the EOF property to loop ' through all the records in the recordset and ' display them to the screen. ' If the record count can be determined, display it ' to the user. Otherwise, let him know that the ' count cannot be determined. If Not (rsHighSales.RecordCount = -1) Then %> There are <%=rsHighSales.RecordCount%> records. <% Else %> ADO cannot determine the number of records in your recordset. Source (Error Object) 203 ActiveX Data Objects 1.5 Source (Error Object) ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. <% End If Else ' There are no records. Tell the user. %> There are no high sales. <% End If ' Release the memory consumed by objects. Set rsHighSales = Nothing Set objDBConn = Nothing %> </BODY> </HTML> Notes You can determine whether your recordset supports the RecordCount property by using the Recordset object’s Supports method with the adApproxPosition or AdBookmark parameters, as demonstrated in the following code: blnApproxPos = rsExample.Supports(adApproxPosition) blnBookmark = rsExample.Supports(adBookmark) These calls to the Supports method allow you to determine if the Recordset object supports approximate positioning or bookmarking, respectively. If the value of blnApproxPos or blnBookmark is True, then RecordCount immediately reflects the actual number of records in the recordset. If the recordset does not support approximate positioning, an attempt to retrieve the value of the RecordCount property will represent a possible drain on resources, since your code will be forced to traverse the recordset and populate it before RecordCount represents a valid count of rows in the recordset. Source (Error Object) objError.Source A string value that represents the name of the application or object that caused ADO or the underlying data provider to add an Error object to the Errors collection of the Connection object. Parameters None Example <%@ LANGUAGE="VBSCRIPT" %> <%Response.Buffer = True%> <HTML> <HEAD> <TITLE>ADO Examples</TITLE> </HEAD> 204 Chapter 11 – ActiveX Data Objects 1.5 Source (Error Object) ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. <BODY> <% ' Include ADOVBS.INC so we can use the ADO constants. %> <!-- #include virtual = "/MySSIncludes/adovbs.inc" --> <% ' Instantiate an ADO Connection object. Set objDBConn = Server.CreateObject("ADODB.Connection") ' Construct the connection string for the Connection ' object. strConn = _ "driver={MyDBType};;uid=sa;pwd=;database=SalesDB" ' Using the connection string, open the connection. objDBConn.Open strConn ' Check to see if attempting to open a connection to ' the provider resulted in ADO adding Error objects to ' the Connection's Errors collection. If objDBConn.Errors.Count > 0 Then ' An error occurred and ADO added an Error object to ' the Connection's Errors collection. Clear the ' Response buffer and alert the user of the error. Response.Clear Response.Write _ "One or more errors have occurred.<BR>" For intCounter = 0 to objDBConn.Errors.Count Response.Write "The " & intCounter & " error's " Response.Write "error number is " & _ ojDBConn.Errors(intCounter).Number & ".<BR>" Response.Write "The description for this " Response.Write "error is <BR>" & _ ojDBConn.Errors(intCounter).Description & _ ".<BR>" Response.Write "The object or application that " Response.Write "caused this error to be raised " Response.Write " is " & _ ojDBConn.Errors(intCounter).Source & ".<BR>" Next Response.End End If ...[additional code] Notes The Error object’s Source property allows you to programmatically determine which object or application caused the data provider to raise an error. The value of this string property can be an application name, a class name, or a ProgID for a class. For errors in ADODB, the value of this property will be the following: ADODB.strObjName where strObjName represents the name of the instantiated ADODB object that caused the error. This is a read-only property. Source (Recordset Object) 205 ActiveX Data Objects 1.5 Source (Recordset Object) ASP in a Nutshell: A Desktop Quick Reference, eMatter Edition Copyright © 2000 O’Reilly & Associates, Inc. All rights reserved. Source (Recordset Object) rsObj.Source (= strSource) A string value that represents the source for the records in the recordset. This can be the name of a stored procedure or a Command object, a table name, or a SQL statement. Parameters strSource A string value that can hold the name of a stored procedure or a Command object, the name of a table in the database, or a simple SQL statement Example In this example, we set the Source property to a simple SQL statement. <%@ LANGUAGE="VBSCRIPT" %&