Read Value from Registory

Here we can get the loaction where QTP installed

Set objFSO =CreateObject(“Scripting.FileSystemObject”)

Set WshShell = CreateObject(“WScript.Shell”)
sKey =”HKLM\SOFTWARE\Mercury Interactive\QuickTest Professional\Configuration_UI\Packages\WebFormsPackage\Settings\ConfigurationFile”
sQTPpath= WshShell.RegRead(sKey)

msgbox sQTPpath

set WshShell =nothing

set objFSO=nothing

Working with files(FileSystemObject)

There are two major categories of file manipulation:

• Creating, adding, or removing data, and reading files
• Moving, copying, and deleting files

Creating Files

There are three ways to create an empty text file (sometimes referred to as a “text stream”).

The first way is to use the CreateTextFile method. The following example demonstrates how to create a text file using the CreateTextFileMethod method.

[VBScript]
Dim fso, f1
Set fso = CreateObject("Scripting.FileSystemObject")
Set f1 = fso.CreateTextFile("c:\testfile.txt", True)
[JScript]
var fso, f1;
fso = new ActiveXObject("Scripting.FileSystemObject");
f1 = fso.CreateTextFile("c:\\testfile.txt", trThe second way to create a text file is to use the OpenTextFile method of the FileSystemObject object with the ForWriting flag set.

[VBScript
Dim fso, ts
Const ForWriting = 2
Set fso = CreateObject("Scripting. FileSystemObject")
Set ts = fso.OpenTextFile("c:\test.txt", ForWriting, True)
[JScript]var fso, ts;var ForWriting= 2;fso = new ActiveXObject("Scripting.FileSystemObject")ts = fso.OpenTextFile("c:\\test.txt", ForWriting, true);

A third way to create a text file is to use the OpenAsTextStream method with the ForWriting flag set.

[VBScript]
Dim fso, f1, ts
Const ForWriting = 2
Set fso = CreateObject("Scripting.FileSystemObject")
fso.CreateTextFile ("c:\test1.txt")
Set f1 = fso.GetFile("c:\test1.txt")
Set ts = f1.OpenAsTextStream(ForWriting, True[JScript]var fso, f1, ts;var ForWriting = 2fso = new ActiveXObject("Scripting.FileSystemObject")fso.CreateTextFile ("c:\\test1.txt");f1 = fso.GetFile("c:\\test1.txt");ts = f1.OpenAsTextStream(ForWriting, true);

Adding Data to the File

Once the text file is created, add data to the file using the following three steps:

Open the text file.

Write the data.

Close the file.

To open an existing file, use either the OpenTextFile method of the FileSystemObject object or the OpenAsTextStream method of the File object.

To write data to the open text file, use the Write, WriteLine, or WriteBlankLines methods of the TextStream object, according to the tasks outlined in the following table.

Task	Method
Write data to an open text file without a trailing newline character.	Write
Write data to an open text file with a trailing newline character.	WriteLine
Write one or more blank lines to an open text file.	WriteBlankLines

To close an open file, use the Close method of the TextStream object.

Note The newline character contains a character or characters (depending on the operating system) to advance the cursor to the beginning of the next line (carriage return/line feed). Be aware that the end of some strings may already have such nonprinting characters.

The following example demonstrates how to open a file, use all three write methods to add data to the file, and then close the file:

[VBScript]
Sub CreateFile()
   Dim fso, tf
   Set fso = CreateObject("Scripting.FileSystemObject")
   Set tf = fso.CreateTextFile("c:\testfile.txt", True)
   ' Write a line with a newline character.
   tf.WriteLine("Testing 1, 2, 3.") 
   ' Write three newline characters to the file.        
   tf.WriteBlankLines(3) 
   ' Write a line.
   tf.Write ("This is a test.") 
   tf.Close
End Sub
[JScript]function CreateFile(){   var fso, t   fso = new ActiveXObject("Scripting.FileSystemObject");   tf = fso.CreateTextFile("c:\\testfile.txt",    // Write a line with a newline character.   tf.WriteLine("Testing 1, 2, 3.") ;   // Write three newline characters to the file.   tf.WriteBlankLines(3) ;   // Write a line   tf.Write ("This is a test.");   tf.Close()}

Reading Files

To read data from a text file, use the Read, ReadLine, or ReadAll method of the TextStream object. The following table describes which method to use for various tasks.

Task	Method
Read a specified number of characters from a file.	Read
Read an entire line (up to, but not including, the newline character).	ReadLine
Read the entire contents of a text file.	ReadAll

If you use the Read or ReadLine method and want to skip to a particular portion of data, use the Skip or SkipLine method. The resulting text of the read methods is stored in a string which can be displayed in a control, parsed by string functions (such as Left, Right, and Mid), concatenated, and so forth.

The following example demonstrates how to open a file, write to it, and then read from it:

[VBScript]
Sub ReadFiles
   Dim fso, f1, ts, s
   Const ForReading = 1
   Set fso = CreateObject("Scripting.FileSystemObject")
   Set f1 = fso.CreateTextFile("c:\testfile.txt", True)
   ' Write a line.
   Response.Write "Writing file <br>"
   f1.WriteLine "Hello World"
   f1.WriteBlankLines(1)
   f1.Close
   ' Read the contents of the file.
   Response.Write "Reading file <br>"
   Set ts = fso.OpenTextFile("c:\testfile.txt", ForReading)
   s = ts.ReadLine
   Response.Write "File contents = '" & s & "'"
   ts.Close
End Sub
[JScript]function ReadFiles(){   var fso, f1, ts, s;   var ForReading = 1;   fso = new ActiveXObject("Scripting.FileSystemObject");   f1 = fso.CreateTextFile("c:\\testfile.txt", true);   // Write a line.   Response.Write("Writing file <br>");   f1.WriteLine("Hello World");   f1.WriteBlankLines(1);   f1.Close();   // Read the contents of the file.   Response.Write("Reading file <br>");   ts = fso.OpenTextFile("c:\\testfile.txt", ForReading);   s = ts.ReadLine()   Response.Write("File contents = '" + s + "'")   ts.Close();}

Moving, Copying, and Deleting Files

The FSO object model has two methods each for moving, copying, and deleting files, as described in the following table.

Task	Method
Move a file	File.Move or FileSystemObject.MoveFile
Copy a file	File.Copy or FileSystemObject.CopyFile
Delete a file	File.Delete or FileSystemObject.DeleteFile

The following example creates a text file in the root directory of drive C, writes some information to it, moves it to a directory called \tmp, makes a copy of it in a directory called \temp, then deletes the copies from both directories.

To run the following example, create directories named \tmp and \temp in the root directory of drive C:

[VBScript]
Sub ManipFiles
   Dim fso, f1, f2, s
   Set fso = CreateObject("Scripting.FileSystemObject")
   Set f1 = fso.CreateTextFile("c:\testfile.txt", True)
   Response.Write "Writing file <br>"
   ' Write a line.
   f1.Write ("This is a test.")
   ' Close the file to writing.
   f1.Close
   Response.Write "Moving file to c:\tmp <br>"
   ' Get a handle to the file in root of C:\.
   Set f2 = fso.GetFile("c:\testfile.txt")
   ' Move the file to \tmp directory.
   f2.Move ("c:\tmp\testfile.txt")
   Response.Write "Copying file to c:\temp <br>"
   ' Copy the file to \temp.
   f2.Copy ("c:\temp\testfile.txt")
   Response.Write "Deleting files <br>"
   ' Get handles to files' current location.
   Set f2 = fso.GetFile("c:\tmp\testfile.txt")
   Set f3 = fso.GetFile("c:\temp\testfile.txt")
   ' Delete the files.
   f2.Delete
   f3.Delete
   Response.Write "All done!"
End Sub

Child Object

Getting Child Object:

We can use description object to get all the objects on the page that matches
that specific description. Suppose we have to check all the checkboxes present
on a web page. So we will first create an object description for a
class=SpellE>checkboxe and then get all the checkboxes from the page

Dim obj_ChkDesc

Set obj_ChkDesc=Description.Create
obj_ChkDesc
class=GramE>(“html tag”).value = “INPUT”
obj_ChkDesc(“type”).value = “checkbox”
Dim allCheckboxes, singleCheckBox
Set allCheckboxes = Browse(“Browser”).Page(“Page”).
class=SpellE>ChildObjects(obj_ChkDesc)

For each singleCheckBox in allCheckboxes

singleCheckBox.Set “ON”

Next

The above code will check all the check boxes present on the page. To get all
the child objects we need to specify an object description i.e. we can’t use
the string arguments that will be discussed later in the 2nd way of using the
programming description.

Possible Operation on Description Object

Consider the below code for all the solutions

Dim obj_ChkDesc

Set obj_ChkDesc=Description.Create

obj_ChkDesc
class=GramE>(“html tag”).value = “INPUT”

obj_ChkDesc(“type”).value = “checkbox”

style=’mso-special-character:line-break’>

How to use Descriptive Programming

How to use Descriptive Programming?

There are two ways in which descriptive programming can be used

1. By creating properties collection object for the description.

2. By giving the description in form of the string arguments.

1. By creating properties collection object for the description.

To use this method you need first to create an empty description

Dim obj_Desc ‘Not necessary to declare

Set obj_Desc = Description.Create

Now we have a blank description in “obj_Desc”. Each

description has 3 properties “Name”, “Value” and “Regular Expression”.

obj_Desc(“html tag”).value= “INPUT”

When you use a property name for the first time the property is added to the

collection and when you use it again the property is modified. By default each

property that is defined is a regular expression. Suppose if we have the

following description

obj_Desc(“html

tag”).value= “INPUT”

obj_Desc(“name”).value= “txt.*”

This would mean an object with html tag as INPUT and name starting with txt. Now

actually that “.*” was considered as regular expression. So, if you want the

property “name” not to be recognized as a regular expression then you need to

set the “regularexpression” property as FALSE

obj_Desc(“html tag”).value= “INPUT”

obj_Desc(“name”).value= “txt.*”

obj_Desc(“name”).regularexpression=False

This is how we create a description. Now below is the way we can use it

Browser(“Browser”).Page(“Page”).WebEdit(obj_Desc).set “Test”

When we say .WebEdit(obj_Desc)

we define one more property for our description that was not earlier defined

that is it’s a text box (because QTPs WebEdit boxes map to text boxes in a web page).

If we know that we have more than 1 element with same description on the page

then we must define “index” property for the that description

Consider the HTML code given below

<–!input type=””textbox”” name=””txt_Name””–>

<–!input type=””textbox”” name=””txt_Name””–>

Now the html code has two objects with same description. So distinguish between

these 2 objects we will use the “index” property. Here is the description for

both the object

For 1st textbox:

obj_Desc(“html tag”).value= “INPUT”

obj_Desc(“name”).value= “txt_Name”

obj_Desc(“index”).value= “0”

For 2nd textbox:

obj_Desc(“html tag”).value= “INPUT”

obj_Desc(“name”).value= “txt_Name”

obj_Desc(“index”).value= “1”

Consider the HTML Code given below:

<–!input type=””textbox”” name=””txt_Name””–>

<–!input type=””radio”” name=””txt_Name””–>

We can use the same description for both the objects and still distinguish

between both of them

obj_Desc(“html tag”).value= “INPUT”

obj_Desc(“name”).value= “txt_Name”

When I want to refer to the textbox then I will use the inside a WebEdit object and to refer to the radio button I will use

the description object with the WebRadioGroup object.

Browser(“Browser”).Page(“Page”).WebEdit(obj_Desc).set “Test” ‘Refers to the text box

Browser(“Browser”).Page(“Page”).WebRadioGroup(obj_Desc).set “Test” ‘Refers to the radio button

But if we use WebElement object for the description

then we must define the “index” property because for a webelement

the current description would return two objects.

Hierarchy of test description:

When using programmatic descriptions from a specific point within a test object

hierarchy, you must continue to use programmatic descriptions

from that point onward within the same statement. If you specify a test object

by its object repository name after other objects in the hierarchy have

been described using programmatic descriptions, QuickTest

cannot identify the object.

For example, you can use Browser(Desc1).Page(Desc1).Link(desc3),

since it uses programmatic descriptions throughout the entire test object

hierarchy.

You can also use Browser(“Index”).Page(Desc1).Link(desc3), since it

uses programmatic descriptions from a certain point in the description

(starting

from the Page object description).

However, you cannot use Browser(Desc1).Page(Desc1).Link(“Example1”),

since it uses programmatic descriptions for the Browser and Page objects but

then attempts to use an object repository name for the Link test object (QuickTest tries to locate the Link object based on its

name, but cannot

locate it in the repository because the parent objects were specified using

programmatic descriptions).

When and Why to use Descriptive programming?

When and Why to use Descriptive programming?

Below are some of the situations when Descriptive Programming can be considered
useful:

1. The objects in the application are dynamic in nature and
need special handling to identify the object. The best example would be of
clicking a link which changes according to the user of the application, Ex.
“Logout <>”.

2. When object repository is getting huge due to the no. of objects being
added. If the size of Object repository increases too much then it decreases
the performance of QTP while recognizing a object.

3. When you don’t want to use object repository at all. Well the first question
would be why not Object repository? Consider the
following scenario which would help understand why not Object repository

Scenario 1: Suppose we have a web application that has not been
developed yet.Now QTP for recording the script and
adding the objects to repository needs the application to be up, that would
mean waiting for the application to be deployed before we can start of with
making QTP scripts. But if we know the descriptions of the objects that will be
created then we can still start off with the script writing for testing

Scenario 2: Suppose an application has 3 navigation
buttons on each and every page. Let the buttons be “Cancel”, “Back” and “Next”.
Now recording action on these buttons would add 3 objects per page in the
repository. For a 10 page flow this would mean 30 objects which could have been
represented just by using 3 objects. So instead of adding these 30 objects to
the repository we can just write 3 descriptions for the object and use it on
any page.

4. Modification to a test case is needed but the Object repository for the same
is Read only or in shared mode i.e. changes may affect other scripts as well.

5. When you want to take action on similar type of object
i.e. suppose we have 20 textboxes on the page and there names are in the form
txt_1, txt_2, txt_3 and so on. Now adding all 20 the Object repository would
not be a good programming approach.

ADO Component Reference

---

Sun ONE Active Server Pages includes
ActiveX Data Objects (ADO) for connecting ASP applications to databases. ADO is a set of objects
that provide a mechanism to access information from ODBC-compliant data
sources.

This section provides ADO reference information.

In this section:

ADO
Overview

ADO
Objects

ADO Collections

ADO
Overview

---

The implementation of ADO used with Sun ONE ASP is called ADODB. ADO enables client
applications to access and manipulate data in a database server from a variety
of different vendors in the same manner. With ADO, data is updated and retrieved using a
variety of existing methods (including SQL). In the context of ASP, using ADO typically involves
writing script procedures that interact with a database and use HTML to display
information from data sources.

In ADO,
the Recordset object is the main interface to data. An example of the
minimal VBScript code to generate a recordset from an ODBC data source is as
follows:

set rstMain = CreateObject("ADODB.Recordset")

rstMain.Open "SELECT * FROM authors", _

"DATABASE=pubs;UID=sa;PWD=;DSN=Publishers"

This generates a forward-only, read-only Recordset

object useful for scanning data. A slightly more functional recordset can be
generated as follows:

set rstMain = CreateObject("ADODB.Recordset")

rstMain.Open "SELECT * FROM authors", _

"DATABASE=pubs;UID=sa;PWD=;DSN=Publishers",

adOpenKeyset, adLockOptimistic

This creates a fully scrollable and
updateable recordset.

Note

Adovbs.inc & Adojavas.inc: For applications that use VBScript (for example, Active Server Pages), you must include the Adovbs.inc file in your code in order to call ADO constants by name (use Adojavas.inc for JScript). Always refer to constants by name rather than by value since the values may change from one version to the next.

Note

Updatable Cursor support: Microsoft and Sun use the Positioned Update and Positioned SQL features of ODBC to implement the AddNew, Update, and Delete methods of the ADO Recordset Object . For some of the supplied ODBC drivers these features are not implemented at all (MySQL, PostgreSQL). For these drivers, Sun uses the implementation of updatable cursors in the ODBC Manager to supply the missing functionality. This works well for recordsets whose fields contain string or numeric data as well as a primary key, auto-increment, or timestamp fields. However, in recordsets containing binary fields or recordsets with duplicate rows, updates, inserts and deletes should be done using the Execute method of the Connection object. Connection.Execute will execute any SQL statement recognized by the database regardless of the capabilities of the ODBC driver.

Note	Linux and multiple SELECT statements: On Linux, ADO does not support stored procedures with multiple SELECT statements.

In ADO,
the object hierarchy is de-emphasized. Unlike Data Access Objects (DAO) or
Remote Data Objects (RDO), you do not have to navigate through a hierarchy to
create objects, because most ADO

objects can be independently created. This allows you to create and track only
the objects you need. This model also results in fewer ADO objects, and thus a smaller working set.

ADO
supports the following key features for building client/server and Web-based
applications:

·
Independently created objects.

·

Support for stored procedures with in/out
parameters and return values.

·
Different cursor types, including the potential
for support of back-end-specific cursors.

·
Advanced recordsetcache management.

·
Support for limits on number of returned rows
and other query goals.

ADO Objects

---

ADO provides
two objects for managing connections with data sources (Connection and Command),
two objects for managing the data returned from a data source (Field and

Recordset) and three secondary objects (Parameters, Properties,
and Errors) for managing information about ADO.

Note	ADO objects cannot be stored in application variables.

Object	Description
Command	Defines a specific command to execute against a data source.
Connection	Represents an open connection to a data source.
Error	Provides specific details about each ADO error.
Field	Represents a column of data with a common data type.
Parameter	Represents a parameter or argument associated with a Command object based on a parameterized query or stored procedure.
Property	Represents a dynamic characteristic of an ADO object that is defined by the provider. This object is not currently supported on UNIX.
Recordset	Represents the entire set of records from a database table or the results of an executed command.

In this section:

ADO
Command Object

ADO
Connection Object

ADO
Error Object

ADO
Field Object

ADO
Parameter Object

ADO
Property Object

ADO
Recordset Object

ADO Command
Object

---

The Command object defines a
specific command to execute against a data source.

In this section:

ADO
Command Object Collections

ADO
Command Object Methods

ADO
Command Object Properties

ADO
Command Object Remarks

ADO Command
Object Collections

---

Collection	Description
Parameters	Contains all the Parameter objects of a Command object.
Properties	Contains all the Property objects for a specific instance of a Command object. This collection is not currently supported on UNIX.

ADO Command
Object Methods

---

Method	Description
CreateParameter	Creates a new Parameter object with the specified properties.
Execute	Executes the query, SQL statement, or stored procedure specified in the CommandText property.

In this section:

ADO
Command Object CreateParameter Method

ADO
Command Object Execute Method

ADO Command
Object CreateParameter Method

---

Creates a new Parameter object with
the specified properties.

CreateParameter Method Syntax (ADO Command Object)

Set parameter = command.CreateParameter (

 Name, Type, Direction, Size, Value)

CreateParameter Method Parameters (ADO Command Object)

parameter

The new ADO Parameter
Object.

Name

An optional String representing the
name of the Parameter object.

Type

An optional Long value specifying
the data type of the Parameter object. See the ADO
Parameter Object Type Property for valid settings.

Direction

An optional Long value specifying
the type of Parameter object. See the ADO
Parameter Object Direction Property for valid settings.

Size

An optional Long value specifying
the maximum length for the parameter value in characters or bytes.

Value

An optional varValue specifying
the value for the Parameter object.

CreateParameter Method Return Value (ADO Command Object)

Returns a Parameter object.

CreateParameter Method Remarks (ADO Command Object)

Use the CreateParameter method to
create a new ADO
Parameter Object with the specified name, type, direction, size, and value.
Any values you pass in the arguments are written to the corresponding Parameter
properties.

This method does not automatically append
the Parameter object to the ADO Parameters
Collection of a Command object. This lets you set additional
properties whose values ADO

will validate when you append the Parameter object to the collection.

If you specify a variable-length data type
in the Type argument, you must either pass a Size argument or
set the ADO
Parameter Object Size Property of the Parameter object before
appending it to the Parameters collection; otherwise, an error occurs.

CreateParameter Method Examples (ADO Command Object)

See the ADO Collections
Append Method example.

ADO Command
Object Execute Method

---

Executes the query, SQL statement, or
stored procedure specified in the CommandText property.

Object Execute Method Syntax (ADO Command Object)

For a row-returning Command:

Set recordset = command.Execute(

 RecordsAffected, Parameters, Options )

For a non-row-returning Command:

command.Execute RecordsAffected, Parameters, Options

Object Execute Method Parameters (ADO Command Object)

RecordsAffected

An optional Long variable to which
the provider returns the number of records that the operation affected.

Parameters

An optional Variant array of parameter
values passed with an SQL statement. (Output parameters will not return correct
values when passed in this argument.)

Options

An optional Long value that
indicates how the provider should evaluate the CommandText property of
the Command object:

Constant	Description
adCmdText	The provider should evaluate CommandText as a textual definition of a command, such as a SQL statement.
adCmdTable	The provider should evaluate CommandText as a table name.
adCmdStoredProc	The provider should evaluate CommandText as a stored procedure.
adCmdUnknown	The type of command in CommandText is not known.

See the ADO
Command Object CommandType Property for a more detailed explanation of the
four constants in this list.

Object Execute Method Remarks (ADO Command Object)

Using the Execute method on a Command
object executes the query specified in the CommandText property of the
object. If the CommandText property specifies a row-returning query, any
results the execution generates are stored in a new ADO Recordset
Object. If the command is not a row-returning query, the provider returns a
closed Recordset object. Some application languages allow you to ignore
this return value if no recordset is desired.

If the query has parameters, the current
values for the Command object’s parameters are used unless you override
these with parameter values passed with the Execute call. You can
override a subset of the parameters by omitting new values for some of the
parameters when calling the Execute method. The order in which you
specify the parameters is the same order in which the method passes them. For example,
if there were four (or more) parameters and you wanted to pass new values for
only the first and fourth parameters, you would pass Array(var1,,,var4) as the Parameters
argument.

Note	Output parameters will not return correct values when passed in the Parameters argument.

Object Execute Method Return Values (ADO Command Object)

Returns a Recordset object
reference.

Object Execute Method Examples (ADO Command Object)

This VBScript example demonstrates the Execute
method when run from both a Command object and an ADO Connection
Object. It also uses the ADO
Recordset Object Requery Method to retrieve current data in a recordset,
and the ADO
Collections Clear Method to clear the contents of the ADO Errors
Collection. The ExecuteCommand and PrintOutput procedures are required for
this procedure to run.

<!-- #Include file="ADOVBS.INC" -->

<HTML><HEAD>

<TITLE>ADO 1.5 Execute Method</TITLE></HEAD>

<BODY>

<FONT FACE="MS SANS SERIF" SIZE=2>

<Center><H3>ADO Execute Method</H3><H4>Recordset Retrieved Using Connection Object</H4>

<TABLE WIDTH=600 BORDER=0>

<TD VALIGN=TOP ALIGN=LEFT COLSPAN=3><FONT SIZE=2>

<!--- Recordsets retrieved using Execute method of Connection and Command Objects-->

<%

Set OBJdbConnection = Server.CreateObject("ADODB.Connection")

OBJdbConnection.Open "AdvWorks"

SQLQuery = "SELECT * FROM Customers"

'First Recordset RSCustomerList

Set RSCustomerList = OBJdbConnection.Execute(SQLQuery)

Set OBJdbCommand = Server.CreateObject("ADODB.Command")

Set OBJdbCommand.ActiveConnection = OBJdbConnection

SQLQuery2 = "SELECT * From Products"

OBJdbCommand.CommandText = SQLQuery2

Set RsProductList = OBJdbCommand.Execute

%>

<TABLE COLSPAN=8 CELLPADDING=5 BORDER=0>

<!-- BEGIN column header row for Customer Table-->

<TR><TD ALIGN=CENTER BGCOLOR="#008080">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Company Name</FONT>

</TD>

<TD ALIGN=CENTER BGCOLOR="#008080">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Contact Name</FONT>

</TD>

<TD ALIGN=CENTER WIDTH=150 BGCOLOR="#008080">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>E-mail address</FONT>

</TD>

<TD ALIGN=CENTER BGCOLOR="#008080">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>City</FONT>

</TD>

<TD ALIGN=CENTER BGCOLOR="#008080">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>State/Province</FONT>

</TD></TR>

<!--Display ADO Data from Customer Table-->

<% Do While Not RScustomerList.EOF %>

<TR>

<TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RSCustomerList("CompanyName")%>

</FONT></TD>

<TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RScustomerList("ContactLastName") & ", " %>

<%= RScustomerList("ContactFirstName") %>

</FONT></TD>

<TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RScustomerList("ContactLastName")%>

</FONT></TD>

<TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RScustomerList("City")%>

</FONT></TD>

<TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RScustomerList("StateOrProvince")%>

</FONT></TD>

</TR>

<!-Next Row = Record Loop and add to html table-->

<%

RScustomerList.MoveNext

Loop

RScustomerList.Close

%>

</TABLE><HR>

<H4>Recordset Retrieved Using Command Object</H4>

<TABLE COLSPAN=8 CELLPADDING=5 BORDER=0>

<!-- BEGIN column header row for Product List Table-->

<TR><TD ALIGN=CENTER BGCOLOR="#800000">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Product Type</FONT>

</TD>

<TD ALIGN=CENTER BGCOLOR="#800000">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Product Name</FONT>

</TD>

<TD ALIGN=CENTER WIDTH=350 BGCOLOR="#800000">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Product Description</FONT>

</TD>

<TD ALIGN=CENTER BGCOLOR="#800000">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Unit Price</FONT>

</TD></TR>

<!-- Display ADO Data Product List-->

<% Do While Not RsProductList.EOF %>

<TR>

<TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RsProductList("ProductType")%>

</FONT></TD>

<TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RsProductList("ProductName")%>

</FONT></TD>

<TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RsProductList("ProductDescription")%>

</FONT></TD>

<TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RsProductList("UnitPrice")%>

</FONT></TD>

<!-- Next Row = Record -->

<%

RsProductList.MoveNext

Loop

'Remove objects from memory to free resources

RsProductList.Close

OBJdbConnection.Close

Set ObJdbCommand = Nothing

Set RsProductList = Nothing

Set OBJdbConnection = Nothing

%>

</TABLE></FONT></Center></BODY></HTML>

ADO Command
Object Properties

---

Property	Description
ActiveConnection	The Connection object to which the specified Command object currently belongs.
CommandText	The text of a command that you want to issue against a provider.
CommandTimeout	How long to wait while executing a command before terminating the command and issuing an error.
CommandType	The type of Command object.
Name	The name of a specific Command object. This property is not currently supported on UNIX.
Prepared	Whether or not to save a compiled version of a command before execution. This property is not currently supported on UNIX.
State	The current state of the Command object. This property is not currently supported on UNIX.

In this section:

ADO
Command Object ActiveConnection Property

ADO
Command Object CommandText Property

ADO
Command Object CommandTimeout Property

ADO
Command Object CommandType Property

ADO
Command Object Name Property

ADO
Command Object Prepared Property

ADO
Command Object State Property

ADO Command
Object ActiveConnection Property

---

Specifies to which Connection
object the specified Command object currently belongs.

ActiveConnection Property Return Values (ADO Command Object)

Sets or returns a String containing
the definition for a connection or a Connection object. Default is a Null
object reference.

ActiveConnection Property Remarks (ADO Command Object)

Use the ActiveConnection property
to determine the Connection object over which the specified Command
object will execute.

For Command objects, the ActiveConnection

property is read/write. If you attempt to call the ADO
Command Object Execute Method on a Command object before setting
this property to an open ADO Connection
Object or valid connection string, an error occurs. Setting the ActiveConnection
property to Nothing disassociates the Command object from the
current Connection and causes the provider to release any associated
resources on the data source. You can then associate the Command object
with the same or another Connection object. Some providers allow you to
change the property setting from one Connection to another, without
having to first set the property to Nothing.

If the ADO Parameters
Collection of the Command object contains parameters supplied by the
provider, the collection is cleared if you set the ActiveConnection
property to Nothing or to another Connection object. If you
manually create ADO Parameter
Object objects and use them to fill the Parameters collection
of the Command object, setting the ActiveConnection property to Nothing

or to another Connection object leaves the Parameters collection
intact.

Closing the Connection object with
which a Command object is associated sets the ActiveConnection

property to Nothing. Setting this property to a closed Connection
object generates an error.

ActiveConnection Property Example (ADO Command Object)

This Visual Basic example uses the ActiveConnection,
ADO Command Object CommandText Property, CommandTimeout, ADO
Command Object CommandType Property, ADO
Field Object ActualSize Property, and ADO
Parameter Object Direction Property properties to execute a stored
procedure:

Public Sub ActiveConnectionX()

Dim cnn1 As ADODB.Connection

Dim cmdByRoyalty As ADODB.Command

Dim prmByRoyalty As ADODB.Parameter

Dim rstByRoyalty As ADODB.Recordset

Dim rstAuthors As ADODB.Recordset

Dim intRoyalty As Integer

Dim strAuthorID As String

Dim strCnn As String

` Define a command object for a stored procedure.

Set cnn1 = New ADODB.Connection

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

cnn1.Open strCnn

Set cmdByRoyalty = New ADODB.Command

Set cmdByRoyalty.ActiveConnection = cnn1

cmdByRoyalty.CommandText = "byroyalty"

cmdByRoyalty.CommandType = adCmdStoredProc

cmdByRoyalty.CommandTimeout = 15

` Define the stored procedure's input parameter.

intRoyalty = Trim(InputBox( _

"Enter royalty:"))

Set prmByRoyalty = New ADODB.Parameter

prmByRoyalty.Type = adInteger

prmByRoyalty.Size = 3

prmByRoyalty.Direction = adParamInput

prmByRoyalty.Value = intRoyalty

cmdByRoyalty.Parameters.Append prmByRoyalty

` Create a recordset by executing the command.

Set rstByRoyalty = cmdByRoyalty.Execute()

` Open the Authors table to get author names for display.

Set rstAuthors = New ADODB.Recordset

rstAuthors.Open "authors", strCnn, , , adCmdTable

` Print current data in the recordset, adding

` author names from Authors table.

Debug.Print "Authors with " & intRoyalty & _

" percent royalty"

Do While Not rstByRoyalty.EOF

strAuthorID = rstByRoyalty!au_id

Debug.Print , rstByRoyalty!au_id & ", ";

rstAuthors.Filter = "au_id = '" & strAuthorID & "'"

Debug.Print rstAuthors!au_fname & " " & _

rstAuthors!au_lname

rstByRoyalty.MoveNext

Loop

rstByRoyalty.Close

rstAuthors.Close

cnn1.Close

End Sub

ADO Command
Object CommandText Property

---

Contains the text of a command that you
want to issue against a provider.

CommandText Property Return Values

Sets or returns a String value
containing a provider command, such as an SQL statement, a table name, or a
stored procedure call. Default is “” (zero-length string).

CommandText Property Remarks

Use the CommandText property to set
or return the text of a Command object. Usually, this will be an SQL
statement, but can also be any other type of command statement recognized by
the provider, such as a stored procedure call. An SQL statement must be of the
particular dialect or version supported by the provider’s query processor.

If the ADO
Command Object Prepared Property of the Command object is set to True
and the Command object is bound to an open connection when you set the CommandText
property, ADO prepares the query (that is, a compiled form of the query is
stored by the provider) when you call the ADO
Command Object Execute Method or ADO
Connection Object Open Method methods. The Prepared property
is not currently supported on UNIX.

Depending on the ADO
Command Object CommandType Property setting, ADO may alter the CommandText
property. You can read the CommandText property at any time to see the
actual command text that ADO
will use during execution.

CommandText Property Example

See the ActiveConnection property.

ADO Command
Object CommandTimeout Property

---

How long to wait while executing a command
before terminating the attempt and generating an error.

CommandTimeout Property Return Values (ADO Command Object)

Sets or returns a Long value that
specifies, in seconds, how long to wait for a command to execute. Default is
30.

CommandTimeout Property Remarks (ADO Command Object)

Use the CommandTimeout property on
a Command object to allow the cancellation of an ADO
Command Object Execute Method call due to delays from network traffic or
heavy server use. If the interval set in the CommandTimeout property
elapses before the command completes execution, an error occurs and ADO cancels the command.
If you set the property to zero, ADO

will wait indefinitely until the execution is complete. Make sure the provider
and data source to which you are writing code supports the CommandTimeout
functionality.

The CommandTimeout setting on a Connection
object has no effect on the CommandTimeout setting on a Command

object on the same Connection; that is, the Command object’s CommandTimeout
property does not inherit the value of the Connection object’s CommandTimeout
value.

CommandTimeout Property Examples (ADO Command Object)

See the ActiveConnection property.

ADO Command
Object CommandType Property

---

The type of a Command object.

CommandType Property Return Values (ADO Command Object)

Sets or returns one of the following CommandTypeEnum
values:

Constant	Description
adCmdText	Evaluates CommandText as a textual definition of a command.
adCmdTable	Evaluates CommandText as a table name.
adCmdStoredProc	Evaluates CommandText as a stored procedure.
adCmdUnknown	(Default) The type of command in the CommandText property is not known.

CommandType Property Remarks (ADO Command Object)

Use the CommandType property to
optimize evaluation of the ADO
Command Object CommandText Property.

If the CommandType property value
equals adCmdUnknown (the default value), you may experience diminished
performance because ADO
must make calls to the provider to determine if the CommandText property
is an SQL statement, a stored procedure, or a table name. If you know what type
of command you’re using, setting the CommandType property instructs ADO to go directly to the
relevant code. If the CommandType property does not match the type of
command in the CommandText property, an error occurs when you call the ADO
Command Object Execute Method.

CommandType Property Example (ADO Command Object)

See the ActiveConnection property.

ADO Command
Object Name Property

---

The name of an object. This property
is not currently supported on UNIX.

Name Property Return Values (ADO Command Object)

Sets or returns a String value. The
value is read/write.

Name Property Remarks (ADO Command Object)

Use the Name property to assign a
name to or retrieve the name of a Command object.

ADO Command
Object Prepared Property

---

Determines whether or not the provider
saves a compiled version of a command before execution. This property is
not currently supported on UNIX.

Prepared Property Return Values

Sets or returns a Boolean value.

Prepared Property Remarks

Use the Prepared property to have
the provider save a prepared (or compiled) version of the query specified in
the ADO
Command Object CommandText Property before a Command object’s first
execution. This may slow a command’s first execution, but once the provider
compiles a command, the provider will use the compiled version of the command
for any subsequent executions, which will result in improved performance.

If the property is False, the
provider will execute the Command object directly without creating a
compiled version.

If the provider does not support command
preparation, it may return an error as soon as this property is set to True.
If it does not return an error, it simply ignores the request to prepare the
command and sets the Prepared property to False.

Prepared Property Example

This Visual Basic example demonstrates the
Prepared property by opening two Command objects: one prepared
and one not prepared.

Public Sub PreparedX()

Dim cnn1 As ADODB.Connection

Dim cmd1 As ADODB.Command

Dim cmd2 As ADODB.Command

Dim strCnn As String

Dim strCmd As String

Dim sngStart As Single

Dim sngEnd As Single

Dim sngNotPrepared As Single

Dim sngPrepared As Single

Dim intLoop As Integer

` Open a connection.

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

Set cnn1 = New ADODB.Connection

cnn1.Open strCnn

` Create two command objects for the same

` command -- one prepared and one not prepared.

strCmd = "SELECT title, type FROM titles ORDER BY type"

Set cmd1 = New ADODB.Command

Set cmd1.ActiveConnection = cnn1

cmd1.CommandText = strCmd

Set cmd2 = New ADODB.Command

Set cmd2.ActiveConnection = cnn1

cmd2.CommandText = strCmd

cmd2.Prepared = True

` Set timer, then execute unprepared command 20 times.

sngStart = Timer

For intLoop = 1 To 20

cmd1.Execute

Next intLoop

sngEnd = Timer

sngNotPrepared = sngEnd - sngStart

` Reset the timer, then execute the prepared

` command 20 times.

sngStart = Timer

For intLoop = 1 To 20

cmd2.Execute

Next intLoop

sngEnd = Timer

sngPrepared = sngEnd - sngStart

` Display performance results.

MsgBox "Performance Results:" & vbCr & _

" Not Prepared: " & Format(sngNotPrepared, _

"##0.000") & " seconds" & vbCr & _

" Prepared: " & Format(sngPrepared, _

"##0.000") & " seconds"

cnn1.Close

End Sub

ADO Command Object
State Property

---

Describes the current state of an object.
This property is not currently supported on UNIX.

State Property Return Values (ADO Command Object)

Sets or returns a Long value that
can be one of the following constants:

Constant	Description
adStateClosed	The object is closed. Default.
adStateOpen	The object is open.

State Property Remarks (ADO Command Object)

You can use the State property to
determine the current state of a given object at any time.

ADO Command
Object Remarks

---

A Command object is used to query a
database, return records in a ADO Recordset
Object, execute bulk operations, or manipulate the structure of a database.
It is a definition of a specific command that you intend to execute against a
data source.

The collections, methods, and properties of
a Command object are used to:

·
Define the executable text of the command (for
example, an SQL statement) with the ADO
Command Object CommandText Property.

·
Define parameterized queries or stored procedure
arguments with ADO
Parameter Object objects and the ADO Parameters
Collection.

·
Execute a command and return a ADO Recordset
Object if appropriate with the ADO
Command Object Execute Method.

·
Specify the type of command with the ADO
Command Object CommandType Property prior to execution to optimize
performance.

·
Set the number of seconds a provider will wait
for a command to execute with the CommandTimeout property.

·
Associate an open connection with a Command
object by setting its property.

·

Set the ADO
Command Object Name Property to identify the Command object as a
method on the associated ADO Connection
Object.

·
Pass a Command object to the ADO
Recordset Object Source Property of an ADO Recordset
Object in order to obtain data.

To execute a query without using a Command
object, pass a query string to the ADO
Connection Object Execute Method of an ADO Connection
Object or to the ADO
Recordset Object Open Method of an ADO Recordset
Object. However, a Command object is required when you want to
retain the command text and re-execute it, or use query parameters.

To create a Command object
independently of a previously defined Connection object, set its ActiveConnection
property to a valid connection string. ADO still creates a Connection object,
but it doesn’t assign that object to an object variable. However, if you are
associating multiple Command objects with the same connection, you
should explicitly create and open a Connection object; this assigns the Connection

object to an object variable. If you do not set the Command object’s ActiveConnection
property to this object variable, ADO
creates a new Connection object for each Command object, even if
you use the same connection string.

To execute a Command, simply call
it by its ADO
Command Object Name Property on the associated Connection object.
The Command must have its ActiveConnection property set to the Connection

object. If the Command has parameters, pass values for them as arguments
to the method.

Depending on the functionality of the
provider, some Command collections, methods, or properties may generate
an error when referenced.

ADO
Connection Object

---

A Connection object represents an
open connection to a data source.

In this section:

ADO
Connection Object Collections

ADO
Connection Object Methods

ADO
Connection Object Properties

ADO
Connection Object Remarks

ADO
Connection Object Collections

---

Collection	Description
Errors	Contains all stored Error objects that pertain to an ADO operation.
Properties	All Property objects for a specific instance of a Connection object. This collection is not currently supported on UNIX.

ADO
Connection Object Methods

---

Method	Description
Close	Closes an open Connection object and any dependent objects.
Execute	Executes the specified query, SQL statement, stored procedure, or provider-specified text.
Open	Opens a connection to a data source.
OpenSchema	Obtains database schema information from the provider. This method is not currently supported on UNIX.
BeginTrans, CommitTrans, and RollbackTrans	Cancels any changes made during the current transaction and ends the transaction. It may also start a new transaction.

In this section:

ADO
Connection Object Close Method

ADO
Connection Object Execute Method

ADO
Connection Object OpenSchema Method

ADO
Connection Object Open Method

ADO
Connection Object BeginTrans, CommitTrans, and RollbackTrans Methods

ADO
Connection Object Close Method

---

Closes an open object and any dependent
objects.

Close Method Syntax (ADO Connection Object)

object.Close

Close Method Remarks (ADO Connection Object)

Use the Close method to close a Connection

object to free any associated system resources. Closing an object does not
remove it from memory; you may change its property settings and open it again
later. To completely eliminate an object from memory, set the object variable
to Nothing.

Using the Close method to close a Connection
object also closes any active Recordset objects associated with the
connection. An ADO
Command Object associated with the Connection object you are closing
will persist, but it will no longer be associated with a Connection

object; that is, its ActiveConnection property will be set to Nothing.
Also, the Command object’s ADO Parameters
Collection will be cleared of any provider-defined parameters.

You can later call the ADO
Connection Object Open Method to reestablish the connection to the same or
another data source. While the Connection object is closed, calling any
methods that require an open connection to the data source generates an error.

Closing a Connection object while
there are open ADO
Recordset Object objects on the connection rolls back any pending
changes in all of the Recordset objects. Explicitly closing a Connection
object (calling the Close method) while a transaction is in progress
generates an error. If a Connection object falls out of scope while a
transaction is in progress, ADO

automatically rolls back the transaction.

Close Method Examples (ADO Connection Object)

This VBScript example uses the Open
and Close methods on both Recordset and Connection objects
that have been opened.

<!-- #Include file="ADOVBS.INC" -->

<HTML><HEAD>

<TITLE>ADO 1.5 Open Method</TITLE>

</HEAD><BODY>

<FONT FACE="MS SANS SERIF" SIZE=2>

<Center><H3>ADO Open Method</H3>

<TABLE WIDTH=600 BORDER=0>

<TD VALIGN=TOP ALIGN=LEFT COLSPAN=3><FONT SIZE=2>

<!--- ADO Connection used to create 2 recordsets-->

<%

Set OBJdbConnection = Server.CreateObject("ADODB.Connection")

OBJdbConnection.Open "AdvWorks"

SQLQuery = "SELECT * FROM Customers"

'First Recordset RSCustomerList

Set RSCustomerList = OBJdbConnection.Execute(SQLQuery)

'Second Recordset RsProductist

Set RsProductList = Server.CreateObject("ADODB.Recordset")

RsProductList.CursorType = adOpenDynamic

RsProductList.LockType = adLockOptimistic

RsProductList.Open "Products", OBJdbConnection

%>

<TABLE COLSPAN=8 CELLPADDING=5 BORDER=0>

<!-- BEGIN column header row for Customer Table-->

<TR><TD ALIGN=CENTER BGCOLOR="#008080">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Company Name</FONT></TD>

<TD ALIGN=CENTER BGCOLOR="#008080">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Contact Name</FONT></TD>

<TD ALIGN=CENTER WIDTH=150 BGCOLOR="#008080">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>E-mail address</FONT></TD>

<TD ALIGN=CENTER BGCOLOR="#008080">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>City</FONT></TD>

<TD ALIGN=CENTER BGCOLOR="#008080">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>State/Province</FONT></TD></TR>

<!--Display ADO Data from Customer Table-->

<% Do While Not RScustomerList.EOF %>

<TR><TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RSCustomerList("CompanyName")%>

</FONT></TD>

<TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RScustomerList("ContactLastName") & ", " %>

<%= RScustomerList("ContactFirstName") %>

</FONT></TD>

<TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RScustomerList("ContactLastName")%>

</FONT></TD>

<TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RScustomerList("City")%>

</FONT></TD>

<TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RScustomerList("StateOrProvince")%>

</FONT></TD></TR>

<!-Next Row = Record Loop and add to html table-->

<%

RScustomerList.MoveNext

Loop

RScustomerList.Close

OBJdbConnection.Close

%>

</TABLE>

<HR>

<TABLE COLSPAN=8 CELLPADDING=5 BORDER=0>

<!-- BEGIN column header row for Product List Table-->

<TR><TD ALIGN=CENTER BGCOLOR="#800000">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Product Type</FONT></TD>

<TD ALIGN=CENTER BGCOLOR="#800000">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Product Name</FONT></TD>

<TD ALIGN=CENTER WIDTH=350 BGCOLOR="#800000">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Product Description</FONT></TD>

<TD ALIGN=CENTER BGCOLOR="#800000">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Unit Price</FONT></TD></TR>

<!-- Display ADO Data Product List-->

<% Do While Not RsProductList.EOF %>

<TR> <TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RsProductList("ProductType")%>

</FONT></TD>

<TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RsProductList("ProductName")%>

</FONT></TD>

<TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RsProductList("ProductDescription")%>

</FONT></TD>

<TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RsProductList("UnitPrice")%>

</FONT></TD>

<!-- Next Row = Record -->

<%

RsProductList.MoveNext

Loop

'Remove Objects from Memory Freeing

Set RsProductList = Nothing

Set OBJdbConnection = Nothing

%>

</TABLE></FONT></Center></BODY></HTML>

ADO
Connection Object Execute Method

---

Executes the specified query, SQL
statement, stored procedure, or provider-specific text.

Execute Method Syntax (ADO Connection Object)

For a non-row-returning command string:

connection.Execute CommandText, RecordsAffected, Options

For a row-returning command string:

Set recordset = connection.Execute (

 CommandText, RecordsAffected, Options)

Execute Method Parameters (ADO Connection Object)

CommandText

A String containing the SQL
statement, table name, stored procedure, or provider-specific text to execute.

RecordsAffected

An optional Long variable to which
the provider returns the number of records that the operation affected.

Options

An optional Long value that
indicates how the provider should evaluate the CommandText argument:

Constant	Description
adCmdText	The provider should evaluate CommandText as a textual definition of a command.
adCmdTable	The provider should evaluate CommandText as a table name.
adCmdStoredProc	The provider should evaluate CommandText as a stored procedure.
adCmdUnknown	The type of command in the CommandText argument is not known.

See the ADO
Command Object CommandType Property for a more detailed explanation of the
four constants in this list.

Execute Method Return Values (ADO Connection Object)

Returns an ADO Recordset
Object reference.

Execute Method Remarks (ADO Connection Object)

Using the Execute method on a Connection

object executes whatever query you pass to the method in the CommandText
argument on the specified connection. If the CommandText argument
specifies a row-returning query, any results the execution generates are stored
in a new Recordset object. If the command is not a row-returning query,
the provider returns a closed Recordset object.

The returned Recordset object is
always a read-only, forward-only cursor. If you need a Recordset object
with more functionality, first create a Recordset object with the
desired property settings, then use the Recordset object’s ADO
Recordset Object Open Method to execute the query and return the desired
cursor type.

The contents of the CommandText
argument are specific to the provider and can be standard SQL syntax or any
special command format that the provider supports.

Execute Method Examples (ADO Connection Object)

See the Command ADO
Command Object Execute Method.

ADO
Connection Object OpenSchema Method

---

Obtains database schema information from
the provider.

OpenSchema Method Syntax

Set recordset = connection.OpenSchema (QueryType,

 Criteria, SchemaID)

OpenSchema Method Parameters

QueryType

The type of schema query to run. Can be
any of the constants listed below.

Criteria

Optional array of query constraints for
each QueryType option, as listed below:

QueryType values	Criteria values
adSchemaAsserts	CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME Not currently supported on UNIX.
adSchemaCatalogs	CATALOG_NAME Not currently supported on UNIX.
asSchemaCharacterSets	CHARACTER_SET_CATALOG CHARACTER_SET_SCHEMA CHARACTER_SET_NAME Not currently supported on UNIX.
adSchemaCheckConstraints	CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME Not currently supported on UNIX.
adSchemaCollations	COLLATION_CATALOG COLLATION_SCHEMA COLLATION_NAME Not currently supported on UNIX.
adSchemaColumnDomainUsage	DOMAIN_CATALOG DOMAIN_SCHEMA DOMAIN_NAME COLUMN_NAME Not currently supported on UNIX.
adSchemaColumnPrivileges	TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME GRANTOR GRANTEE Not currently supported on UNIX.
adSchemaColumns	TABLE_CATALOG TABLE_SCHEMA TABLE_NAME Supported on UNIX.
adSchemaConstraintTableUsage	TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME Not currently supported on UNIX.
adSchemaForeignKeys	PK_TABLE_CATALOG PK_TABLE_SCHEMA PK_TABLE_NAME FK_TABLE_CATALOG FK_TABLE_SCHEMA FK_TABLE_NAME Not currently supported on UNIX.
adSchemaIndexes	TABLE_CATALOG TABLE_SCHEMA INDEX_NAME TYPE TABLE_NAME Not currently supported on UNIX.
adSchemaKeyColumnUsage	CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME Not currently supported on UNIX.
adSchemaPrimaryKeys	PK_TABLE_CATALOG PK_TABLE_SCHEMA PK_TABLE_NAME Not currently supported on UNIX.
adSchemaProcedureColumns	PROCEDURE_CATALOG PROCEDURE_SCHEMA PROCEDURE_NAME COLUMN_NAME Not currently supported on UNIX.
adSchemaProcedures	PROCEDURE_CATALOG PROCEDURE_SCHEMA PROCEDURE_NAME PARAMETER_TYPE Not currently supported on UNIX.
adSchemaProviderSpecific	see Remarks Not currently supported on UNIX.
adSchemaProviderTypes	DATA_TYPE BEST_MATCH Supported on UNIX.
adSchemaReferentialConstraints	CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME Not currently supported on UNIX.
adSchemaSchemata	CATALOG_NAME SCHEMA_NAME SCHEMA_OWNER Not currently supported on UNIX.
adSchemaSQLLanguages	<none> Not currently supported on UNIX.
adSchemaStatistics	TABLE_CATALOG TABLE_SCHEMA TABLE_NAME Not currently supported on UNIX.
adSchemaTableConstraints	CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE Not currently supported on UNIX.
adSchemaTablePrivileges	TABLE_CATALOG TABLE_SCHEMA TABLE_NAME Not currently supported on UNIX.
adSchemaTables	TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE Supported on UNIX.
adSchemaTranslations	TRANSLATION_CATALOG TRANSLATION_SCHEMA TRANSLATION_NAME Not currently supported on UNIX.
adSchemaUsagePrivileges	OBJECT_CATALOG OBJECT_SCHEMA OBJECT_NAME OBJECT_TYPE GRANTOR GRANTEE Not currently supported on UNIX.
adSchemaViewColumnUsage	VIEW_CATALOG VIEW_SCHEMA VIEW_NAME Not currently supported on UNIX.
adSchemaViewTableUsage	VIEW_CATALOG VIEW_SCHEMA VIEW_NAME Not currently supported on UNIX.
adSchemaViews	TABLE_CATALOG TABLE_SCHEMA TABLE_NAME Not currently supported on UNIX.

SchemaID

The GUID for a provider-schema schema
query is not defined by the OLE DB 1.1 specification. This parameter is
required if QueryType is set to adSchemaProviderSpecific;
otherwise, it is not used.

OpenSchema Method Return Values

Returns an ADO Recordset
Object that contains schema information.

OpenSchema Method Remarks

The OpenSchema method returns
information about the data source, such as information about the tables on the
server and the columns in the tables.

The Criteria argument is an array
of values that can be used to limit the results of a schema query. Each schema
query has a different set of parameters that it supports. The actual schemas
are defined by the OLE DB specification under the “IDBSchemaRowset”
interface. The ones supported in ADO
1.5 are listed above.

The constant adSchemaProviderSpecific
is used for the QueryType argument if the provider defines its own
non-standard schema queries outside those listed above. When this constant is
used, the SchemaID argument is required to pass the GUID of the schema
query to execute. If QueryType is set to adSchemaProviderSpecific

but SchemaID is not provided, an error will result.

Providers are not required to support all
of the OLE DB standard schema queries. Specifically, only adSchemaTables,
adSchemaColumns and adSchemaProviderTypes are required by the OLE
DB specification. However, the provider is not required to support the Criteria

constraints listed above for those schema queries.

OpenSchema Method Example

This Visual Basic example uses the OpenSchema
method to display the name and type of each table in the Pubs database.

Public Sub OpenSchemaX()

Dim cnn1 As ADODB.Connection

Dim rstSchema As ADODB.Recordset

Dim strCnn As String

Set cnn1 = New ADODB.Connection

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

cnn1.Open strCnn

Set rstSchema = cnn1.OpenSchema(adSchemaTables)

Do Until rstSchema.EOF

Debug.Print "Table name: " & _

rstSchema!TABLE_NAME & vbCr & _

"Table type: " & rstSchema!TABLE_TYPE & vbCr

rstSchema.MoveNext

Loop

rstSchema.Close

cnn1.Close

End Sub

ADO
Connection Object Open Method

---

Opens a connection to a data source.

Open Method Syntax (ADO Connection Object)

connection.Open ConnectionString, UserID, Password

Open Method Parameters (ADO Connection Object)

ConnectionString

An optional String containing
connection information. See the ConnectionString property for details on
valid settings.

UserID

An optional String containing a
user name to use when establishing the connection.

Password

An optional String containing a
password to use when establishing the connection.

Open Method Remarks (ADO Connection Object)

Using the Open method on a Connection
object establishes the physical connection to a data source. After this method
successfully completes, the connection is live and you can issue commands
against it and process results.

Use the optional ConnectionString
argument to specify a connection string containing a series of argument
= value statements separated by semicolons. The ConnectionString property
automatically inherits the value used for the ConnectionString

argument. Therefore, you can either set the ConnectionString property of
the Connection object before opening it, or use the ConnectionString
argument to set or override the current connection parameters during the Open
method call.

If you pass user and password information
both in the ConnectionString argument and in the optional UserID

and Password arguments, the results may be unpredictable; you should
only pass such information in either the ConnectionString argument or
the UserID and Password arguments.

When you have concluded your operations
over an open Connection, use the ADO
Connection Object Close Method to free any associated system resources.
Closing an object does not remove it from memory; you may change its property
settings and use the Open method to open it again later. To completely
eliminate an object from memory, set the object variable to Nothing.

Open Method Examples (ADO Connection Object)

See the ADO
Connection Object Close Method.

ADO
Connection Object BeginTrans, CommitTrans, and RollbackTrans Methods

---

The transaction methods manage transaction
processing within a Connection object.

These transaction methods are summarized
as follows:

Method	Description
BeginTrans	Begins a new transaction
CommitTrans	Saves any changes and ends the current transaction. It may also start a new transaction.
RollbackTrans	Cancels any changes made during the current transaction and ends the transaction. It may also start a new transaction.

BeginTrans, CommitTrans, and RollbackTrans Methods
Syntax

level = connection.BeginTrans()

connection.BeginTrans

connection.CommitTrans

connection.RollbackTrans

BeginTrans, CommitTrans, and RollbackTrans Methods
Remarks

Use these methods with a Connection
object when you want to save or cancel a series of changes made to the source
data as a single unit. For example, to transfer money between accounts, you
subtract an amount from one and add the same amount to the other. If either
update fails, the accounts no longer balance. Making these changes within an
open transaction ensures either all or none of the changes goes through.

Not all providers support transactions.
Check that the provider-defined property “Transaction DDL” appears in
the Connection object’s ADO Properties
Collection, indicating that the provider supports transactions. If the
provider does not support transactions, calling one of these methods will
return an error.

Once you call the BeginTrans
method, the provider will no longer instantaneously commit any changes you make
until you call CommitTrans or RollbackTrans to end the
transaction.

For providers that support nested
transactions, calling the BeginTrans method within an open transaction
starts a new, nested transaction. The return value indicates the level of
nesting: a return value of “1” indicates you have opened a top-level
transaction (that is, the transaction is not nested within another
transaction), “2” indicates that you have opened a second-level
transaction (a transaction nested within a top-level transaction), and so
forth. Calling CommitTrans or RollbackTrans affects only the most
recently opened transaction; you must close or rollback the current transaction
before you can resolve any higher-level transactions.

Calling the CommitTrans method
saves changes made within an open transaction on the connection and ends the
transaction. Calling the RollbackTrans method reverses any changes made
within an open transaction and ends the transaction. Calling either method when
there is no open transaction generates an error.

Depending on the Connection
object’s ADO
Connection Object Attributes Property, calling either the CommitTrans

or RollbackTrans methods may automatically start a new transaction. If
the Attributes property is set to adXactCommitRetaining, the
provider automatically starts a new transaction after a CommitTrans
call. If the Attributes property is set to adXactAbortRetaining,
the provider automatically starts a new transaction after a RollbackTrans

call.

BeginTrans, CommitTrans, and RollbackTrans Methods
Return Value

BeginTrans can be called as a
function that returns a Long variable indicating the nesting level of
the transaction.

BeginTrans, CommitTrans, and RollbackTrans Methods
Examples

This Visual Basic example changes the book
type of all psychology books in the Titles table of the database. After
the BeginTrans method starts a transaction that isolates all the changes
made to the Titles table, the CommitTrans method saves the
changes. Notice that you can use the RollbackTrans method to undo
changes that you saved using the ADO
Recordset Object Update Method.

Public Sub BeginTransX()

Dim cnn1 As ADODB.Connection

Dim rstTitles As ADODB.Recordset

Dim strCnn As String

Dim strTitle As String

Dim strMessage As String

` Open connection.

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

Set cnn1 = New ADODB.Connection

cnn1.Open strCnn

` Open titles table.

Set rstTitles = New ADODB.Recordset

rstTitles.CursorType = adOpenDynamic

rstTitles.LockType = adLockPessimistic

rstTitles.Open "titles", cnn1, , , adCmdTable

rstTitles.MoveFirst

cnn1.BeginTrans

` Loop through recordset and ask user if she wants

` to change the type for a specified title.

Do Until rstTitles.EOF

If Trim(rstTitles!Type) = "psychology" Then

strTitle = rstTitles!Title

strMessage = "Title: " & strTitle & vbCr & _

"Change type to self help?"

` Change the title for the specified employee.

If MsgBox(strMessage, vbYesNo) = vbYes Then

rstTitles!Type = "self_help"

rstTitles.Update

End If

End If

rstTitles.MoveNext

Loop

` Ask if the user wants to commit to all the

` changes made above.

If MsgBox("Save all changes?", vbYesNo) = vbYes Then

cnn1.CommitTrans

Else

cnn1.RollbackTrans

End If

` Print current data in recordset.

rstTitles.Requery

rstTitles.MoveFirst

Do While Not rstTitles.EOF

Debug.Print rstTitles!Title & " - " & rstTitles!Type

rstTitles.MoveNext

Loop

' Restore original data

rstTitles.MoveFirst

Do Until rstTitles.EOF

If Trim(rstTitles!Type) = "self_help" Then

rstTitles!Type = "psychology"

rstTitles.Update

End If

rstTitles.MoveNext

Loop

rstTitles.Close

cnn1.Close

End Sub

ADO
Connection Object Properties

---

Property	Description
Attributes	One or more characteristics of an object.
CommandTimeout	How long to wait while executing a command before terminating the command and issuing and error.
ConnectionString	Contains the information used to establish a connection to a data source.
ConnectionTimeout	How long to wait while establishing a connection before terminating the attempt and issuing and error.
CursorLocation	The location of the cursor engine in a recordset.
DefaultDatabase	The default database for the Connection object. This property is not currently supported on UNIX.
IsolationLevel	The level of isolation for the Connection object.
Mode	The available permissions for modifying data in a Connection object.
Provider	The name of a provider for a Connection object. This property is not available on UNIX.
State	Describes the current state of the Connection object.
Version	The ADO version number.

In this section:

ADO
Connection Object Attributes Property

ADO
Connection Object CommandTimeout Property

ADO
Connection Object ConnectionString Property

ADO
Connection Object ConnectionTimeout Property

ADO
Connection Object CursorLocation Property

ADO
Connection Object DefaultDatabase Property

ADO
Connection Object IsolationLevel Property

ADO
Connection Object Mode Property

ADO
Connection Object Provider Property

ADO
Connection Object State Property

ADO
Connection Object Version Property

ADOConnection Object Attributes Property

---

One or more characteristics of an object. This
property is read-only on UNIX.

Attributes Property Return Values (ADO Connection Object)

Sets or returns a Long value.

For a Connection object, the Attributes
property is read/write, and its value can be the sum of any one or more of
these XactAttributeEnum values (default is zero):

Value	Description
adXactCommitRetaining	Performs retaining commits, that is, calling the CommitTrans method automatically starts a new transaction. Not all providers support this, and it is always zero under UNIX.
adXactAbortRetaining	Performs retaining aborts, that is, calling the BeginTrans, CommitTrans, and RollbackTrans methods automatically starts a new transaction. Not all providers support this, and it is always zero under UNIX.

Attributes Property Remarks (ADO Connection Object)

Use the Attributes property to set
or return characteristics of Connection objects.

When you set multiple attributes, you can
sum the appropriate constants. If you set the property value to a sum including
incompatible constants, an error occurs.

Attributes Property Examples (ADO Connection Object)

This Visual Basic example displays the
value of the Attributes property for Connection objects. It uses
the ADO
Field Object Name Property to display the name of each Field and Property

object.

Public Sub AttributesX

Dim cnn1 As ADODB.Connection

Dim strCnn As String

' Open connection

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

Set cnn1 = New ADODB.Connection

cnn1.Open strCnn

' Display the attributes of the connection.

Debug.Print "Connection attributes = " & _

cnn1.Attributes

cnn1.Close

End Sub

ADO
Connection Object CommandTimeout Property

---

How long to wait while executing a command
before terminating the attempt and generating an error.

CommandTimeout Property Return Values (ADO Connection Object)

Sets or returns a Long value that
specifies, in seconds, how long to wait for a command to execute. Default is
30.

CommandTimeout Property Remarks (ADO Connection Object)

Use the CommandTimeout property on
a Connection object to allow the cancellation of an ADO
Connection Object Execute Method call, due to delays from network traffic
or heavy server use. If the interval set in the CommandTimeout property
elapses before the command completes execution, an error occurs and ADO cancels the command.
If you set the property to zero, ADO

will wait indefinitely until the execution is complete. Make sure the provider
and data source to which you are writing code supports the CommandTimeout
functionality.

The CommandTimeout setting on a Connection
object has no effect on the CommandTimeout setting on a Command

object on the same Connection; that is, the Command object’s CommandTimeout
property does not inherit the value of the Connection object’s CommandTimeout
value.

On a Connection object, the CommandTimeout
property remains read/write after the Connection is opened.

CommandTimeout Property Examples (ADO Connection Object)

See the ActiveConnection property.

ADO
Connection Object ConnectionString Property

---

Contains the information used to establish
a connection to a data source.

ConnectionString Property Return Values (ADO Connection Object)

Sets or returns a String value.

ConnectionString Property Remarks (ADO Connection Object)

Use the ConnectionString property
to specify a data source by passing a detailed connection string containing a
series of argument = value statements separated by
semicolons.

ADO
supports seven arguments for the ConnectionString property; any other
arguments pass directly to the provider without any processing by ADO. The arguments ADO supports are as
follows:

Argument	Description
Provider	Specifies the name of the provider to use for the connection.
DataSource	Specifies the name of a data source for the connection, for example, an Oracle database registered as an ODBC data source.
UserID	Specifies the user name to use when opening the connection.
Password	Specifies the password to use when opening the connection.
FileName	Specifies the name of a provider-specific file (for example, a persisted data source object) containing preset connection information.
RemoteProvider	Specifies the name of a provider to use when opening a client-side connection (Remote Data Service only).
RemoteServer	Specifies the path name of the server to use when opening a client-side connection (Remote Data Service only).

After you set the ConnectionString
property and open the Connection object, the provider may alter the
contents of the property, for example, by mapping the ADO-defined argument
names to their provider equivalents.

The ConnectionString property
automatically inherits the value used for the ConnectionString
argument of the ADO
Connection Object Open Method, so you can override the current ConnectionString
property during the Open method call.

Because the File Name argument
causes ADO to
load the associated provider, you cannot pass both the Provider and File
Name arguments.

The ConnectionString property is
read/write when the connection is closed and read-only when it is open.

Remote Data Service Usage: When
used on a client-side Connection object, the ConnectionString
property can only include the Remote Provider and Remote Server
parameters.

ConnectionString Property Example (ADO Connection Object)

This Visual Basic example demonstrates different
ways of using the ConnectionString property to open a Connection
object. It also uses the ConnectionTimeout property to set a connection
timeout period, and the ADO
Connection Object State Property to check the state of the connections. The

GetState function is required for this procedure to run.

Public Sub ConnectionStringX()

Dim cnn1 As ADODB.Connection

Dim cnn2 As ADODB.Connection

Dim cnn3 As ADODB.Connection

Dim cnn4 As ADODB.Connection

' Open a connection without using a Data Source Name (DSN).

Set cnn1 = New ADODB.Connection

cnn1.ConnectionString = "driver={SQL Server};" & _

"server=bigsmile;uid=sa;pwd=pwd;database=pubs"

cnn1.ConnectionTimeout = 30

cnn1.Open

' Open a connection using a DSN and ODBC tags.

Set cnn2 = New ADODB.Connection

cnn2.ConnectionString = "DSN=Pubs;UID=sa;PWD=pwd;"

cnn2.Open

' Open a connection using a DSN and OLE DB tags.

Set cnn3 = New ADODB.Connection

cnn3.ConnectionString = "Data Source=Pubs;User ID=sa;Password=pwd;"

cnn3.Open

' Open a connection using a DSN and individual

' arguments instead of a connection string.

Set cnn4 = New ADODB.Connection

cnn4.Open "Pubs", "sa", "pwd"

' Display the state of the connections.

MsgBox "cnn1 state: " & GetState(cnn1.State) & vbCr & _

"cnn2 state: " & GetState(cnn1.State) & vbCr & _

"cnn3 state: " & GetState(cnn1.State) & vbCr & _

"cnn4 state: " & GetState(cnn1.State)

cnn4.Close

cnn3.Close

cnn2.Close

cnn1.Close

End Sub

Public Function GetState(intState As Integer) As String

Select Case intState

Case adStateClosed

GetState = "adStateClosed"

Case adStateOpen

GetState = "adStateOpen"

End Select

End Function

ADO
Connection Object ConnectionTimeout Property

---

Sets how long to wait while establishing a
connection before terminating the attempt and generating an error.

ConnectionTimeout Property Return Values (ADO Connection Object)

Sets or returns a Long value that
specifies, in seconds, how long to wait for the connection to open. Default is
15.

ConnectionTimeout Property Remarks (ADO Connection Object)

Use the ConnectionTimeout property
on a Connection object if delays from network traffic or heavy server
use make it necessary to abandon a connection attempt. If the time from the ConnectionTimeout

property setting elapses prior to the opening of the connection, an error
occurs and ADO
cancels the attempt. If you set the property to zero, ADO will wait indefinitely until the
connection is opened. Make sure the provider to which you are writing code
supports the ConnectionTimeout functionality.

The ConnectionTimeout property is
read/write when the connection is closed and read-only when it is open.

ConnectionTimeout Property Example (ADO Connection Object)

See the ConnectionString property.

ADO
Connection Object CursorLocation Property

---

Sets or returns the location of the cursor
engine.

CursorLocation Property Return Values (ADO Connection Object)

Sets or returns a Long value that
can be set to one of the following constants:

Constant	Description
adUseClient	Uses client-side cursors supplied by a local cursor library. Local cursor engines will often allow many features that driver-supplied cursors may not, so using this setting may provide an advantage with respect to features that will be enabled. For backward-compatibility, the synonym adUseClientBatch is also supported. Note: With the Sun ONE ASP implementation of ADO, adUseClient has a value of 1, and adUseClientBatch has a value of 3.
adUseServer	Default. Uses data provider or driver-supplied cursors. These cursors are sometimes very flexible and allow for some additional sensitivity to reflecting changes that others make to the actual data source. However, some features of the Microsoft Client Cursor Provider (such as disassociated recordsets) cannot be simulated. Note: With the Sun ONE ASP implementation of ADO, adUseServer has a value of 2.

CursorLocation Property Remarks (ADO Connection Object)

This property allows you to choose between
various cursor libraries accessible to the provider. Usually, you can choose
between using a client-side cursor library or one that is located on the
server.

This property setting only affects
connections established after the property has been set. Changing the CursorLocation
property has no effect on existing connections.

This property is read/write on a Connection.

CursorLocation Property Example (ADO Connection Object)

See the AbsolutePosition property
example.

ADO
Connection Object DefaultDatabase Property

---

The default database for a Connection
object. This property is not currently supported on UNIX.

DefaultDatabase Property Return Values

Sets or returns a String that
evaluates to the name of a database available from the provider.

DefaultDatabase Property Remarks

Use the DefaultDatabase property to
set or return the name of the default database on a specific Connection

object.

If there is a default database, SQL
strings may use an unqualified syntax to access objects in that database. To
access objects in a database other than the one specified in the DefaultDatabase
property, you must qualify object names with the desired database name. Upon
connection, the provider will write default database information to the DefaultDatabase
property. Some providers allow only one database per connection, in which case
you cannot change the DefaultDatabase property.

Some data sources and providers may not
support this feature, and may return an error or an empty string.

Remote Data Service Usage: This
property is not available on a client-side Connection object.

DefaultDatabase Property Example

See ADO
Connection Object Provider Property

ADOConnection Object IsolationLevel Property

---

The level of transaction isolation for a Connection
object. Transactions are not currently supported on UNIX.

IsolationLevel Property Return Values

Sets or returns one of the following IsolationLevelEnum
values:

Constant	Description
adXactUnspecified	The provider is using a different IsolationLevel than specified, but the level cannot be determined.
adXactChaos	You cannot overwrite pending changes from more highly isolated transactions.
adXactBrowse	You can view uncommitted changes from one transaction in other transactions.
adXactReadUncommitted	Same as adXactBrowse.
adXactCursorStability	Default. You can view changes in other transactions only after they have been committed.
adXactReadCommitted	Same as adXactCursorStability.
adXactRepeatableRead	You cannot see changes in other transactions, but requerying can bring new Recordset objects.
adXactIsolated	Transactions are conducted in isolation of other transactions.
adXactSerializable	Same as adXactIsolated.

IsolationLevel Property Remarks

Use the IsolationLevel property to
set the isolation level of a Connection object. The IsolationLevel

property is read/write. The setting does not take effect until the next time
you call the BeginTrans method. If the level of isolation you request is
unavailable, the provider may return the next greater level of isolation.

IsolationLevel Property Example

This example uses the ADO
Connection Object Mode Property to open an exclusive connection, and the IsolationLevel
property to open a transaction that is conducted in isolation of other
transactions.

Public Sub IsolationLevelX()

Dim cnn1 As ADODB.Connection

Dim rstTitles As ADODB.Recordset

Dim strCnn As String

` Assign connection string to variable.

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

` Open connection and titles table.

Set cnn1 = New ADODB.Connection

cnn1.Mode = adModeShareExclusive

cnn1.IsolationLevel = adXactIsolated

cnn1.Open strCnn

Set rstTitles = New ADODB.Recordset

rstTitles.CursorType = adOpenDynamic

rstTitles.LockType = adLockPessimistic

rstTitles.Open "titles", cnn1, , , adCmdTable

cnn1.BeginTrans

` Display connection mode.

If cnn1.Mode = adModeShareExclusive Then

MsgBox "Connection mode is exclusive."

Else

MsgBox "Connection mode is not exclusive."

End If

` Display isolation level.

If cnn1.IsolationLevel = adXactIsolated Then

MsgBox "Transaction is isolated."

Else

MsgBox "Transaction is not isolated."

End If

` Change the type of psychology titles.

Do Until rstTitles.EOF

If Trim(rstTitles!Type) = "psychology" Then

rstTitles!Type = "self_help"

rstTitles.Update

End If

rstTitles.MoveNext

Loop

` Print current data in recordset.

rstTitles.Requery

Do While Not rstTitles.EOF

Debug.Print rstTitles!Title & " - " & rstTitles!Type

rstTitles.MoveNext

Loop

` Restore original data.

cnn1.RollbackTrans

rstTitles.Close

cnn1.Close

End Sub

ADO
Connection Object Mode Property

---

The available permissions for modifying
data in a Connection.

Mode Property Return Values (ADO Connection Object)

Sets or returns one of the following ConnectModeEnum
values:

Constant	Description
adModeUnknown	Default. The permissions have not been set or cannot be determined.
adModeRead	Read-only permission.
adModeWrite	Write-only permission.
adModeReadWrite	Read/write permission.
adModeShareDenyRead	Prevents others from opening a connection with read permission.
adModeShareDenyWrite	Prevents others from opening a connection with write permission.
adModeShareExclusive	Prevents others from opening a connection.
adModeShareDenyNone	Allows others to open a connection with any permissions. Neither read nor write access can be denied to others.

Mode Property Remarks (ADO Connection Object)

Use the Mode property to set or
return the access permissions in use by the provider on the current connection.
You can set the Mode property only when the Connection object is
closed.

Mode Property Example (ADO Connection Object)

See the IsolationLevel property
example.

ADO
Connection Object Provider Property

---

The name of the provider for a Connection
object. This property is not available on UNIX.

Provider Property Return Values

Sets or returns a String value.

Provider Property Remarks

Use the Provider property to set or
return the name of the provider for a connection. This property can also be set
by the contents of the ConnectionString property or the ConnectionString
argument of the ADO
Connection Object Open Method; however, specifying a provider in more than
one place while calling the Open method can have unpredictable results.
If no provider is specified, the property will default to MSDASQL (Microsoft
OLE DB Provider for ODBC).

The Provider property is read/write
when the connection is closed and read-only when it is open. The setting does
not take effect until you either open the Connection object or access
the ADO
Properties Collection of the Connection object. If the setting is
invalid, an error occurs.

Provider Property Example

This Visual Basic example demonstrates the
Provider property by opening two Connection objects using
different providers. It also uses the DefaultDatabase property to set
the default database for the Microsoft ODBC Provider.

Public Sub ProviderX()

Dim cnn1 As ADODB.Connection

Dim cnn2 As ADODB.Connection

` Open a connection using the Microsoft ODBC provider.

Set cnn1 = New ADODB.Connection

cnn1.ConnectionString = "driver={SQL Server};" & _

"server=bigsmile;uid=sa;pwd=pwd"

cnn1.Open strCnn

cnn1.DefaultDatabase = "pubs"

` Display the provider.

MsgBox "Cnn1 provider: " & cnn1.Provider

` Open a connection using the Microsoft Jet provider.

Set cnn2 = New ADODB.Connection

cnn2.Provider = "Microsoft.Jet.OLEDB.3.51"

cnn2.Open "C:\Samples\northwind.mdb", "admin", ""

` Display the provider.

MsgBox "Cnn2 provider: " & cnn2.Provider

cnn1.Close

cnn2.Close

End Sub

ADO Connection Object
State Property

---

Describes the current state of an object.

State Property Return Values (ADO Connection Object)

Sets or returns a Long value that
can be one of the following constants:

Constant	Description
adStateClosed	Default. The object is closed.
adStateOpen	The object is open.

State Property Remarks (ADO Connection Object)

You can use the State property to
determine the current state of a given object at any time.

State Property Examples (ADO Connection Object)

This Visual Basic example demonstrates
different ways of using the ConnectionString property to open a Connection
object. It also uses the ConnectionTimeout property to set a connection
timeout period, and the State property to check the state of the connections.
The GetState function is required for this procedure to run.

Public Sub ConnectionStringX()

Dim cnn1 As ADODB.Connection

Dim cnn2 As ADODB.Connection

Dim cnn3 As ADODB.Connection

Dim cnn4 As ADODB.Connection

` Open a connection without using a DSN.

Set cnn1 = New ADODB.Connection

cnn1.ConnectionString = "driver={SQL Server};" & _

"server=bigsmile;uid=sa;pwd=pwd;database=pubs"

cnn1.ConnectionTimeout = 30

cnn1.Open

` Open a connection using a DSN and ODBC tags.

Set cnn2 = New ADODB.Connection

cnn2.ConnectionString = "DSN=Pubs;UID=sa;PWD=pwd;"

cnn2.Open

` Open a connection using a DSN and OLE DB tags.

Set cnn3 = New ADODB.Connection

cnn3.ConnectionString = "Data Source=Pubs;User ID=sa;Password=pwd;"

cnn3.Open

` Open a connection using a DSN and individual

` arguments instead of a connection string.

Set cnn4 = New ADODB.Connection

cnn4.Open "Pubs", "sa", "pwd"

` Display the state of the connections.

MsgBox "cnn1 state: " & GetState(cnn1.State) & vbCr &_

"cnn2 state: " & GetState(cnn1.State) & vbCr & _

"cnn3 state: " & GetState(cnn1.State) & vbCr & _

"cnn4 state: " & GetState(cnn1.State)

cnn4.Close

cnn3.Close

cnn2.Close

cnn1.Close

End Sub

Public Function GetState(intState As Integer) As String

Select Case intState

Case adStateClosed

GetState = "adStateClosed"

Case adStateOpen

GetState = "adStateOpen"

End Select

End Function

ADO
Connection Object Version Property

---

The ADO
version number.

Version Property Return Values

Returns a String value.

Version Property Remarks

Use the Version property to return
the version number of the ADO

implementation. The version of the provider will be available on Windows
servers as a dynamic property in the ADO Properties
Collection. The Properties collection is not currently
supported on UNIX.

Version Property Example

This Visual Basic example uses the Version
property of a Connection object to display the current ADO version. It also uses several dynamic
properties to show the current DBMS name and version, OLE DB version, provider
name and version, driver name and version, and driver ODBC version.

Public Sub VersionX()

Dim cnn1 As ADODB.Connection

' Open connection.

Set cnn1 = New ADODB.Connection

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

cnn1.Open strCnn

strVersionInfo = "ADO Version: " & cnn1.Version & vbCr & _

"DBMS Name: " & cnn1.Properties("DBMS Name") & vbCr & _

"DBMS Version: " & cnn1.Properties("DBMS Version") & vbCr & _

"OLE DB Version: " & cnn1.Properties("OLE DB Version") & vbCr & _

"Provider Name: " & cnn1.Properties("Provider Name") & vbCr & _

"Provider Version: " & cnn1.Properties("Provider Version") & vbCr & _

"Driver Name: " & cnn1.Properties("Driver Name") & vbCr & _

"Driver Version: " & cnn1.Properties("Driver Version") & vbCr & _

"Driver ODBC Version: " & cnn1.Properties("Driver ODBC Version")

MsgBox strVersionInfo

cnn1.Close

End Sub

ADO
Connection Object Remarks

---

A Connection object represents a
session with a data source. In the case of a client/server database system, it
may represent an actual network connection to the server. Depending on the
functionality of the provider, some collections, properties, and methods of the
Connection object may not be available.

Use the collections, methods, and
properties of a Connection object for:

·
configuring the connection before opening it
with the ConnectionString, CommandTimeout, and ADO
Connection Object Mode Property properties.

·
setting the CursorLocation property to
invoke the Client Cursor Provider, which supports batch updates. Batch
updates are not currently supported on UNIX.

·
setting the default database for the connection
with the DefaultDatabase property.

·

setting the level of isolation for the
transactions opened on the connection with the IsolationLevel property. Transactions
are not currently supported on UNIX.

·
specifying an OLE DB provider with the ADO
Connection Object Provider Property.

·
establishing and breaking the physical
connection to the data source with the ADO
Connection Object Open Method and ADO
Connection Object Close Method methods.

·
executing a command on the connection with the ADO
Connection Object Execute Method and configuring the execution with the CommandTimeout
property.

·
managing transactions on the open connection,
including nested transactions if the provider supports them, with the BeginTrans,
CommitTrans, and RollbackTrans methods and the ADO
Connection Object Attributes Property. The transaction methods are not
currently supported on UNIX.

·
examining errors returned from the data source
with the ADO
Errors Collection.

·
reading the version from the ADO implementation in use with the ADO
Connection Object Version Property.

·
obtaining schema information about your database
with the ADO
Connection Object OpenSchema Method.

Note	To execute a query without using a Command object, pass a query string to the Execute method of a Connection object. However, a Command object is required when you want to retain the command text and re-execute it, or use query parameters.

ADO Error
Object

---

The ADO Error
object provides specific details about each ADO error.

In this section:

ADO
Error Object Properties

ADO
Error Object Remarks

ADO Error
Object Properties

---

Note	None of the ADO Error object properties listed in this section are currently supported on UNIX.

Property	Description
Description	A descriptive string associated with an error.
HelpContext	The help file topic associated with an error.
HelpContext, HelpFile	The help file associated with an error.
NativeError	The provider-specific error code for an error.
Number	The number that uniquely identifies an error.
Source	The name of the object or application that originally generated the error.
SQL State	The SQL state for a given error.

In this section:

ADO
Error Object Description Property

ADO
Error Object HelpContext, HelpFile Property

ADO
Error Object NativeError Property

ADO
Error Object Number Property

ADO
Error Object Source Property

ADO
Error Object SQLState Property

ADO Error
Object Description Property

---

A descriptive string associated with an Error

object. This property is not currently supported on UNIX.

Description Property Return Values (ADO Error Object)

Returns a String value.

Description Property Remarks (ADO Error Object)

Use the Description property to
obtain a short description of the error. Display this property to alert the
user to an error that you cannot or do not want to handle. The string will come
from either ADO
or a provider.

Providers are responsible for passing
specific error text to ADO.

ADO adds an Error
object to the ADO
Errors Collection for each provider error or warning it receives. Enumerate
the Errors collection to trace the errors that the provider passes.

Description Property Example (ADO Error Object)

This Visual Basic example triggers an
error, traps it, and displays the ADO
Error Object Description Property, ADO
Error Object HelpContext, HelpFile Property, ADO
Error Object NativeError Property, ADO
Error Object Number Property, ADO
Error Object Source Property, and ADO
Error Object SQLState Property properties of the resulting Error

object:

Public Sub DescriptionX()

Dim cnn1 As ADODB.Connection

Dim errLoop As ADODB.Error

Dim strError As String

On Error GoTo ErrorHandler

` Intentionally trigger an error.

Set cnn1 = New ADODB.Connection

cnn1.Open "nothing"

Exit Sub

ErrorHandler:

` Enumerate Errors collection and display

` properties of each Error object.

For Each errLoop In cnn1.Errors

strError = "Error #" & errLoop.Number & vbCr & _

" " & errLoop.Description & vbCr & _

" (Source: " & errLoop.Source & ")" & vbCr & _

" (SQL State: " & errLoop.SQLState & ")" & vbCr & _

" (NativeError: " & errLoop.NativeError & ")" & vbCr

If errLoop.HelpFile = "" Then

strError = strError & _

" No Help file available" & _

vbCr & vbCr

Else

strError = strError & _

" (HelpFile: " & errLoop.HelpFile & ")" & vbCr & _

" (HelpContext: " & errLoop.HelpContext & ")" & _

vbCr & vbCr

End If

Debug.Print strError

Next

Resume Next

End Sub

ADO Error
Object HelpContext, HelpFile Property

---

The help file and topic associated with an
Error object. This property is not currently supported on UNIX.

HelpContext, HelpFile Property Return Values

HelpContextID

Returns a context ID, as a Long
value, for a topic in a Microsoft Windows Help file.

HelpFile

Returns a String that evaluates to
a fully resolved path to a Help file.

HelpContext, HelpFile Property Remarks

If a Windows Help (.hlp) file is specified
in the HelpFile property, the HelpContext property is used to
automatically display the Help topic it identifies. If there is no relevant
help topic available, the HelpContext property returns zero and the HelpFile

property returns a zero-length string (“”).

HelpContext, HelpFile Property Examples

See the ADO
Error Object Description Property example.

ADO Error
Object NativeError Property

---

The provider-specific error code for a
given Error object. This property is not currently supported on UNIX.

NativeError Property Return Values

Returns a Long value.

NativeError Property Remarks

Use the NativeError property to
retrieve the database-specific error information for a particular Error
object. For example, when using the Microsoft ODBC Provider for OLE DB with a
SQL Server database, native error codes that originate from SQL Server pass
through ODBC and the ODBC Provider to the ADO

NativeError property.

NativeError Property Example

See the ADO
Error Object Description Property.

ADO Error
Object Number Property

---

The number that uniquely identifies an Error
object. This property is not currently supported on UNIX.

Number Property Return Values

Returns a Long value.

Number Property Remarks

Use the Number property to
determine which error occurred. The value of the property is a unique number
that corresponds to the error condition.

Number Property Example

See the ADO
Error Object Description Property.

ADO Error
Object Source Property

---

The name of the object or application that
originally generated an error. This property is not currently supported on
UNIX.

Source Property Return Values

Returns a String value.

Source Property Remarks

Use the Source property on an Error
object to determine the name of the object or application that originally
generated an error. This could be the object’s class name or programmatic ID.
For errors in ADODB, the property value will be ADODB.ObjectName.

Source Property Parameters (ADO Error Object)

ObjectName

The name of the object that triggered the
error. The Source property is read-only for Error objects.

Based on the error documentation from the Source,
ADO
Error Object Number Property, and ADO
Error Object Description Property properties of Error objects, you
can write code that will handle the error appropriately.

Source Property Example

See the ADO
Error Object Description Property example.

ADO Error
Object SQLState Property

---

The SQL state for a given Error
object. This property is not currently supported on UNIX.

SQLState Property Return Values

Returns a five-character String

that follows the ANSI SQL standard.

SQLState Property Remarks

Use the SQLState property to read
the five-character error code that the provider returns when an error occurs
during the processing of a SQL statement. For example, when using the Microsoft
OLE DB Provider for ODBC with a SQL Server database, SQL state error codes
originate from ODBC based either on errors specific to ODBC or on errors that
originate from Microsoft SQL Server, and are then mapped to ODBC errors. These
error codes are documented in the ANSI SQL standard, but may be implemented
differently by different data sources.

SQLState Property Example

See the ADO
Error Object Description Property example.

ADO Error
Object Remarks

---

Any operation involving ADO objects can generate one or more provider
errors. As each error occurs, one or more Error objects are placed in
the ADO
Errors Collection of the ADO Connection
Object. When another ADO

operation generates an error, the Errors collection is cleared, and the
new set of Error objects are placed in the Errors collection.

Note

Each Error object represents a specific provider error, not an ADO error. ADO errors are exposed to the run-time exception handling mechanism. For example, in Microsoft Visual Basic, the occurrence of an ADO-specific error will trigger an On Error event and appear in the Err object. For a complete list of ADO errors, see Appendix B.

Read the Error object’s properties
to obtain specific details about each error:

·
The ADO
Error Object Description Property contains the text of the error.

·

The ADO
Error Object Number Property contains the Long integer value of the
error constant.

·
The ADO
Error Object Source Property identifies the object that raised the error.
This is particularly useful when you have several Error objects in the Errors

collection following a request to a data source.

·
The ADO
Error Object HelpContext, HelpFile Property indicate the appropriate
Microsoft Windows Help file and Help topic, respectively (if any exist), for
the error.

·
The ADO
Error Object SQLState Property and ADO
Error Object NativeError Property properties provide information
from SQL data sources.

ADO
supports the return of multiple errors by a single ADO operation to allow for error information
specific to the provider. To obtain this error information in an error handler,
use the appropriate error-trapping features of the language or environment you
are working with, then use nested loops to enumerate the properties of each Error
object in the Errors collection.

ADO

clears the OLE Error Info object before making a call that could
potentially generate a new provider error. However, the Errors collection
on the Connection object is cleared and populated only when the provider
generates a new error, or when the ADO Collections
Clear Method is called.

Some properties and methods return
warnings that appear as Error objects in the Errors collection
but do not halt a program’s execution. Before you call the ADO
Recordset Object Resync Method, ADO
Recordset Object UpdateBatch Method, or ADO
Recordset Object CancelBatch Method methods on an ADO Recordset
Object, or before you set the ADO
Recordset Object Filter Property on a Recordset object, call the ADO Collections
Clear Method on the Errors collection so that you can read the Count

property of the Errors collection to test for returned warnings.

If there is no valid Connection
object when using Microsoft Visual Basic and VBScript, retrieve error
information from the Err object.

To refer to an Error object in a
collection by its ordinal number, use either of the following syntax forms:

connection.Errors.Item(0)

connection.Errors(0)

ADO Field Object

---

The ADO Field Object represents a column
of data with a common data type.

In this section:

ADO
Field Object Collections

ADO
Field Object Methods

ADO
Field Object Properties

ADO
Field Object Remarks

ADO Field Object Collections

---

Properties

All Property objects for a specific instance of a Field object. This collection is not currently supported on UNIX.

ADO Field Object Methods

---

Method	Description
AppendChunk	Appends data to a large text or binary data field.
GetChunk	Returns all or a portion of the contents of a large text or binary data field.

In this section:

ADO
Field Object AppendChunk Method

ADO
Field Object GetChunk Method

ADO Field Object AppendChunk Method

---

Appends data to a large text or binary
data Field object.

AppendChunk Method Syntax (ADO Field Object)

object.AppendChunk Data

AppendChunk Method Parameters (ADO Field Object)

object

A Field object.

Data

A Variant containing the data you want to
append to the object.

AppendChunk Method Remarks (ADO Field Object)

Use the AppendChunk method on a Field
object to fill it with long binary or character data. In situations where
system memory is limited, you can use the AppendChunk method to
manipulate long values in portions rather than in their entirety.

If the adFldLong bit in the ADO
Field Object Attributes Property of a Field object is set to True,
you can use the AppendChunk method for that field.

The first AppendChunk call on a Field
object writes data to the field, overwriting any existing data. Subsequent AppendChunk
calls add to existing data. If you are appending data to one field and then you
set or read the value of another field in the current record, ADO assumes that you are done appending data
to the first field. If you call the AppendChunk method on the first
field again, ADO

interprets the call as a new AppendChunk operation and overwrites the
existing data. Accessing fields in other ADO Recordset
Object objects (that are not clones of the first Recordset
object) will not disrupt AppendChunk operations.

If there is no current record when you
call AppendChunk on a Field object, an error occurs.

AppendChunk Method Examples (ADO Field Object)

See the ADO
Field Object GetChunk Method example.

ADO Field Object GetChunk Method

---

Returns all or a portion of the contents
of a large text or binary data Field object.

GetChunk Method Syntax (ADO Field Object)

variable = field.GetChunk( Size )

GetChunk Method Parameters (ADO Field Object)

variable

Variant to hold data returned.

Size

A Long expression equal to the
number of bytes or characters you want to retrieve.

GetChunk Method Remarks (ADO Field Object)

Use the GetChunk method on a Field
object to retrieve part or all of its long binary or character data. In
situations where system memory is limited, you can use the GetChunk
method to manipulate long values in portions rather than in their entirety.

The data a GetChunk call returns is
assigned to variable. If Size is greater than the remaining
data, the GetChunk method returns only the remaining data without
padding variable with empty spaces. If the field is empty, the GetChunk

method returns Null.

Each subsequent GetChunk call
retrieves data starting from where the previous GetChunk call left off.
However, if you are retrieving data from one field and then you set or read the
value of another field in the current record, ADO assumes you are done retrieving data from
the first field. If you call the GetChunk method on the first field
again, ADO

interprets the call as a new GetChunk operation and starts reading from
the beginning of the data. Accessing fields in other ADO Recordset
Object objects (that are not clones of the first Recordset
object) will not disrupt GetChunk operations.

If the adFldLong bit in the ADO
Field Object Attributes Property of a Field object is set to True,
you can use the GetChunk method for that field.

If there is no current record when you use
the GetChunk method on a Field object, error 3021 (no current
record) occurs.

GetChunk Method Return Values (ADO Field Object)

Returns a Variant.

GetChunk Method Example (ADO Field Object)

See the ADO
Field Object AppendChunk Method.

ADO Field Object Properties

---

Property	Description
ActualSize	The actual length of a field value.
Attributes	One or more characteristics of a field. This property is read-only on UNIX.
DefinedSize	The defined size of a field.
Name	The name of a field.
NumericScale	The scale of numeric values in a field.
OriginalValue	The value of a field that existed in the record before any changes were made. This property is not currently supported on UNIX.
Precision	The degree of precision for numeric values in a field.
Type	The data type of the field.
UnderlyingValue	The current value of the field in the database. This property is not currently supported on UNIX.
Value	The value assigned to the field.

In this section:

ADO
Field Object ActualSize Property

ADO
Field Object Attributes Property

ADO
Field Object DefinedSize Property

ADO
Field Object Name Property

ADO
Field Object NumericScale Property

ADO
Field Object OriginalValue Property

ADO
Field Object Precision Property

ADO
Field Object Type Property

ADO
Field Object UnderlyingValue Property

ADO
Field Object Value Property

ADO Field Object ActualSize Property

---

The actual length of a field’s value.

ActualSize Property Return Values (ADO Field Object)

Returns a Long value. Some
providers may allow this property to be set to reserve space for BLOB data, in
which case the default value is 0.

ActualSize Property Remarks (ADO Field Object)

Use the ActualSize property to
return the actual length of a Field object’s value. For all fields, the ActualSize

property is read-only. If ADO
cannot determine the length of the Field object’s value, the ActualSize
property returns adUnknown.

The ActualSize and ADO
Field Object DefinedSize Property properties are different as shown
in the following example: a Field object with a declared type of adVarChar

and a maximum length of 50 characters returns a DefinedSize property
value of 50, but the ActualSize property value it returns is the length
of the data stored in the field for the current record.

ActualSize Property Example (ADO Field Object)

This Visual Basic example uses the ActualSize
and DefinedSize properties to display the defined size and actual size
of a field.

Public Sub ActualSizeX()

Dim rstStores As ADODB.Recordset

Dim strCnn As String

' Open a recordset for the Stores table.

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

Set rstStores = New ADODB.Recordset

rstStores.Open "stores", strCnn, , , adCmdTable

' Loop through the recordset displaying the contents

' of the stor_name field, the field's defined size,

' and its actual size.

rstStores.MoveFirst

Do Until rstStores.EOF

MsgBox "Store name: " & rstStores!stor_name & _

vbCr & "Defined size: " & _

rstStores!stor_name.DefinedSize & _

vbCr & "Actual size: " & _

rstStores!stor_name.ActualSize & vbCr

rstStores.MoveNext

Loop

rstStores.Close

End Sub

ADO Field Object Attributes Property

---

One or more characteristics of an object. This
property is read-only on UNIX.

Attributes Property Return Values (ADO Field Object)

Sets or returns a Long value.

Attributes Property Field (ADO Field Object)

For a Field object, the Attributes
property is read-only, and its value can be the sum of any one or more of these
FieldAttributeEnum values:

Value	Description
adFldMayDefer	The field is deferred; that is, the field values are not retrieved from the data source with the whole record, but only when you explicitly access them.
adFldUpdatable	The field can be written.
adFldUnknownUpdatable	The provider cannot determine if the field can be written.
adFldFixed	The field contains fixed-length data.
adFldIsNullable	The field accepts Null values.
adFldMayBeNull	You can read Null values from the field.
adFldLong	The field is a long binary field. Also indicates that you can use the ADO Field Object AppendChunk Method and ADO Field Object GetChunk Method methods.
adFldRowID	The field contains some kind of record ID (record number, unique identifier, and so forth).
adFldRowVersion	The field contains some kind of time or date stamp used to track updates.
adFldCacheDeferred	The provider caches field values and subsequent reads are done from the cache.

Attributes Property Remarks (ADO Field Object)

Use the Attributes property to set
or return characteristics of Field objects.

When you set multiple attributes, you can
sum the appropriate constants. If you set the property value to a sum including
incompatible constants, an error occurs.

ADO Field Object DefinedSize Property

---

The defined size of a Field object.

DefinedSize Property Return Values (ADO Field Object)

Returns a Long value that reflects
the defined size of a field as a number of bytes.

DefinedSize Property Remarks (ADO Field Object)

Use the DefinedSize property to
determine the data capacity of a Field object.

The DefinedSize and ADO
Field Object ActualSize Property properties are different. For
example, consider a Field object with a declared type of adVarChar
and a DefinedSize property value of 50, containing a single character.
The ActualSize property value it returns is the length in bytes of the
single character.

DefinedSize Property Examples (ADO Field Object)

See the ADO
Field Object ActualSize Property example.

ADO Field Object Name Property

---

The name of an object.

Name Property Return Values (ADO Field Object)

Sets or returns a String value. The
value is read-only on a Field object.

Name Property Remarks (ADO Field Object)

Use the Name property to retrieve
the name of a Field object.

The Name property is read-only.
Names do not have to be unique within a collection.

Name Property Examples (ADO Field Object)

See the ADO
Field Object Attributes Property example.

ADO Field Object NumericScale Property

---

The scale of Numeric values in a Field
object.

NumericScale Property Return Values (ADO Field
Object)

Sets or returns a Byte value,
indicating the number of decimal places to which numeric values will be
resolved

NumericScale Property Remarks (ADO Field Object)

Use the NumericScale property to
determine how many digits to the right of the decimal point will be used to
represent values for a numeric Field object.

The NumericScale property is
read-only.

ADO Field Object OriginalValue Property

---

The value of a Field object that
existed in the record before any changes were made. This property is not
currently supported on UNIX.

OriginalValue Property Return Values (ADO Field
Object)

Returns a Variant value.

OriginalValue Property Remarks (ADO Field Object)

Use the OriginalValue property to
return the original field value for a field from the current record.

In immediate update mode (the provider
writes changes to the underlying data source once you call the ADO
Recordset Object Update Method), the OriginalValue property returns
the field value that existed prior to any changes (that is, since the last Update

method call). This is the same value that the ADO
Recordset Object CancelUpdate Method uses to replace the ADO
Field Object Value Property.

In batch update mode (the provider caches
multiple changes and writes them to the underlying data source only when you
call the ADO
Recordset Object UpdateBatch Method), the OriginalValue property
returns the field value that existed prior to any changes (that is, since the
last UpdateBatch method call). This is the same value that the ADO
Recordset Object CancelBatch Method uses to replace the Value

property. When you use this property with the UnderlyingValue property,
you can resolve conflicts that arise from batch updates. Batch updates are
currently not supported on UNIX.

OriginalValue Property Example (ADO Field Object)

This Visual Basic example demonstrates the
OriginalValue and UnderlyingValue properties by displaying a
message if a record’s underlying data has changed during a ADO Recordset
Object batch update.

Public Sub OriginalValueX()

Dim cnn1 As ADODB.Connection

Dim rstTitles As ADODB.Recordset

Dim fldType As ADODB.Field

Dim strCnn As String

' Open connection.

Set cnn1 = New ADODB.Connection

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

cnn1.Open strCnn

' Open recordset for batch update.

Set rstTitles = New ADODB.Recordset

Set rstTitles.ActiveConnection = cnn1

rstTitles.CursorType = adOpenKeyset

rstTitles.LockType = adLockBatchOptimistic

rstTitles.Open "titles"

' Set field object variable for Type field.

Set fldType = rstTitles!Type

' Change the type of psychology titles.

Do Until rstTitles.EOF

If Trim(fldType) = "psychology" Then

fldType = "self_help"

End If

rstTitles.MoveNext

Loop

' Similate a change by another user by updating

' data using a command string.

cnn1.Execute "UPDATE titles SET type = 'sociology' " & _

"WHERE type = 'psychology'"

'Check for changes.

rstTitles.MoveFirst

Do Until rstTitles.EOF

If fldType.OriginalValue <> _

fldType.UnderlyingValue Then

MsgBox "Data has changed!" & vbCr & vbCr & _

" Title ID: " & rstTitles!title_id & vbCr & _

" Current value: " & fldType & vbCr & _

" Original value: " & _

fldType.OriginalValue & vbCr & _

" Underlying value: " & _

fldType.UnderlyingValue & vbCr

End If

rstTitles.MoveNext

Loop

' Cancel the update because this is a demonstration.

rstTitles.CancelBatch

rstTitles.Close

' Restore original values.

cnn1.Execute "UPDATE titles SET type = 'psychology' " & _

"WHERE type = 'sociology'"

cnn1.Close

End Sub

ADO Field Object Precision Property

---

The degree of precision for numeric Field
objects.

Precision Property Return Values (ADO Field Object)

Sets or returns a Byte value,
indicating the maximum total number of digits used to represent values. The
value is read-only on a Field object.

Precision Property Remarks (ADO Field Object)

Use the Precision property to
determine the maximum number of digits used to represent values for a numeric Field

object.

Precision Property Example (ADO Field Object)

See the ADO
Field Object NumericScale Property.

ADO Field Object Type Property

---

The operational type or data type of a Field
object.

Type Property Return Values (ADO Field Object)

Sets or returns one of the following DataTypeEnum
values. The corresponding OLE DB type indicators are as follows:

Constant	Description
adArray	Or’d together with another type to indicate that the data is a safe-array of that type (DBTYPE_ARRAY).
adBigInt	An 8-byte signed integer (DBTYPE_I8).
adBinary	A binary value (DBTYPE_BYTES).
adBoolean	A Boolean value (DBTYPE_BOOL).
adByRef	Or’d together with another type to indicate that the data is a pointer to data of the other type (DBTYPE_BYREF).
adBSTR	A null-terminated character string (Unicode) (DBTYPE_BSTR).
adChar	A String value (DBTYPE_STR).
adCurrency	A currency value (DBTYPE_CY). Currency is a fixed-point number with 4 digits to the right of the decimal point. It is stored in an 8-byte signed integer scaled by 10,000.
adDate	A date value (DBTYPE_DATE). A date is stored as a Double, the whole part of which is the number of days since December 30, 1899, and the fractional part of which is the fraction of a day.
adDBDate	A date value (yyyymmdd) (DBTYPE_DBDATE).
adDBTime	A time value (hhmmss) (DBTYPE_DBTIME).
adDBTimeStamp	A date-time stamp (yyyymmddhhmmss plus a fraction in billionths) (DBTYPE_DBTIMESTAMP).
adDecimal	An exact numeric value with a fixed precision and scale (DBTYPE_DECIMAL).
adDouble	A double-precision floating point value (DBTYPE_R8).
adEmpty	No value was specified (DBTYPE_EMPTY).
adError	A 32-bit error code (DBTYPE_ERROR).
adGUID	A globally unique identifier (GUID) (DBTYPE_GUID).
adIDispatch	A pointer to an IDispatch interface on an OLE object (DBTYPE_IDISPATCH).
adInteger	A 4-byte signed integer (DBTYPE_I4).
adIUnknown	A pointer to an IUnknown interface on an OLE object (DBTYPE_IUNKNOWN).
adNumeric	An exact numeric value with a fixed precision and scale (DBTYPE_NUMERIC).
adSingle	A single-precision floating point value (DBTYPE_R4).
adSmallInt	A 2-byte signed integer (DBTYPE_I2).
adTinyInt	A 1-byte signed integer (DBTYPE_I1).
adUnsignedBigInt	An 8-byte unsigned integer (DBTYPE_UI8).
adUnsignedInt	A 4-byte unsigned integer (DBTYPE_UI4).
adUnsignedSmallInt	A 2-byte unsigned integer (DBTYPE_UI2).
adUnsignedTinyInt	A 1-byte unsigned integer (DBTYPE_UI1).
adUserDefined	A user-defined variable (DBTYPE_UDT).
adVariant	An Automation Variant (DBTYPE_VARIANT).
adVector	OR’d together with another type to indicate that the data is a DBVECTOR structure, as defined by OLE DB, that contains a count of elements and a pointer to data of the other type (DBTYPE_VECTOR).
adWChar	A null-terminated Unicode character string (DBTYPE_WSTR).

Type Property Remarks (ADO Field Object)

For Field objects, the Type

property is read-only.

Type Property Example (ADO Field Object)

This example demonstrates the Type
property by displaying the name of the constant corresponding to the value of
the Type property of all the Field objects in the Employees

table. The FieldType function is required for this procedure to run.

Public Sub TypeX()

Dim rstEmployees As ADODB.Recordset

Dim fldLoop As ADODB.Field

Dim strCnn As String

` Open recordset with data from Employee table.

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

Set rstEmployees = New ADODB.Recordset

rstEmployees.Open "employee", strCnn, , , adCmdTable

Debug.Print "Fields in Employee Table:" & vbCr

` Enumerate Fields collection of Employees table.

For Each fldLoop In rstEmployees.Fields

Debug.Print " Name: " & fldLoop.Name & vbCr & _

" Type: " & FieldType(fldLoop.Type) & vbCr

Next fldLoop

End Sub

Public Function FieldType(intType As Integer) As String

Select Case intType

Case adChar

FieldType = "adChar"

Case adVarChar

FieldType = "adVarChar"

Case adSmallInt

FieldType = "adSmallInt"

Case adUnsignedTinyInt

FieldType = "adUnsignedTinyInt"

Case adDBTimeStamp

FieldType = "adDBTimeStamp"

End Select

End Function

ADO Field Object UnderlyingValue Property

---

A Field object’s current value in
the database. This property is not currently supported on UNIX.

UnderlyingValue Property Return Values (ADO Field
Object)

Returns a Variant value.

UnderlyingValue Property Remarks (ADO Field Object)

Use the UnderlyingValue property to
return the current field value from the database. The field value in the UnderlyingValue
property is the value that is visible to your transaction and may be the result
of a recent update by another transaction. This may differ from the ADO
Field Object OriginalValue Property, which reflects the value that was
originally returned to the ADO Recordset
Object.

This is similar to using the ADO
Recordset Object Resync Method, but the UnderlyingValue property
returns only the value for a specific field from the current record. This is
the same value that the Resync method uses to replace the ADO
Field Object Value Property.

When you use this property with the OriginalValue

property, you can resolve conflicts that arise from batch updates.

UnderlyingValue Property Example (ADO Field Object)

See the ADO
Field Object OriginalValue Property example.

ADO Field Object Value Property

---

Indicates the value assigned to a Field
object.

Value Property Return Values (ADO Field Object)

Sets or returns a Variant value. Default
value depends on the ADO
Field Object Type Property.

Value Property Remarks (ADO Field Object)

Use the Value property to set or
return data from Field objects. ADO
allows setting and returning long binary data with the Value property.

Value Property Example (ADO Field Object)

This Visual Basic example demonstrates the
Value property with Field and Property objects by
displaying field and property values for the Employees table.

Public Sub ValueX()

Dim rstEmployees As ADODB.Recordset

Dim fldLoop As ADODB.Field

Dim prpLoop As ADODB.Property

Dim strCnn As String

' Open recordset with data from Employee table.

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

Set rstEmployees = New ADODB.Recordset

rstEmployees.Open "employee", strCnn, , , adCmdTable

Debug.Print "Field values in rstEmployees"

' Enumerate the Fields collection of the Employees

' table.

For Each fldLoop In rstEmployees.Fields

` Because Value is the default property of a

` Field object, the use of the actual keyword

` here is optional.

Debug.Print " " & fldLoop.Name & " = " &

fldLoop.Value

Next fldLoop

Debug.Print "Property values in rstEmployees"

' Enumerate the Properties collection of the

' Recordset object.

For Each prpLoop In rstEmployees.Properties

' Because Value is the default property of a

' Property object, the use of the actual keyword

' here is optional.

Debug.Print " " & prpLoop.Name & " = " &

prpLoop.Value

Next prpLoop

rstEmployees.Close

End Sub

ADO Field Object Remarks

---

A ADO Recordset
Object has an ADO Fields
Collection made up of Field objects. Each Field object
corresponds to a column in the recordset. You use the ADO
Field Object Value Property of Field objects to set or return data
for the current record. Depending on the functionality the provider exposes,
some collections, methods, or properties of a Field object may not be
available.

The collections, methods, and properties
of a Field object are used to:

·
return the name of a field with the ADO
Field Object Name Property.

·
view or change the data in the field with the ADO
Field Object Value Property.

·
return the basic characteristics of a field with
the ADO
Field Object Type Property, ADO
Field Object Precision Property, and ADO
Field Object NumericScale Property properties.

·
return the declared size of a field with the ADO
Field Object DefinedSize Property.

·
return the actual size of the data in a given
field with the ADO
Field Object ActualSize Property.

·
determine what types of functionality are
supported for a given field with the ADO
Field Object Attributes Property and ADO Properties
Collection.

·
manipulate the values of fields containing long
binary or long character data with the ADO
Field Object AppendChunk Method and ADO
Field Object GetChunk Method methods.

·
resolve discrepancies in field values during
batch updating with the ADO
Field Object OriginalValue Property and UnderlyingValue properties
(if the provider supports batch updates).

Note	All metadata properties (Name, Type, DefinedSize, Precision, and NumericScale) are available before opening the Field object’s recordset. Setting them at that time is useful for dynamically constructing forms.

ADO
Parameter Object

---

The ADO Parameter Object represents a
parameter or argument associated with a Command object based on a
parameterized query or stored procedure.

In this section:

ADO
Parameter Object Collections

ADO
Parameter Object Methods

ADO
Parameter Object Properties

ADO
Parameter Object Remarks

ADO
Parameter Object Collections

---

Properties

All the Property objects for a specific instance of a Parameter object. This collection is not currently supported on UNIX.

ADO
Parameter Object Methods

---

AppendChunk

Appends data to a large text or binary data parameter.

In this section:

ADO
Parameter Object AppendChunk Method

ADO
Parameter Object AppendChunk Method

---

Appends data to a large text or binary
data Parameter object.

AppendChunk Method Syntax (ADO Parameter Object)

object.AppendChunk Data

AppendChunk Method Parameters (ADO Parameter Object)

object

A Parameter object.

Data

A Variant containing the data you want to
append to the object.

AppendChunk Method Remarks (ADO Parameter Object)

Use the AppendChunk method on a Parameter

object to fill it with long binary or character data. In situations where
system memory is limited, you can use the AppendChunk method to
manipulate long values in portions rather than in their entirety.

If the adFldLong bit in the ADO
Parameter Object Attributes Property of a Parameter object is set to
True, you can use the AppendChunk method for that parameter.

The first AppendChunk call on a Parameter
object writes data to the parameter, overwriting any existing data. Subsequent AppendChunk
calls on a Parameter object adds to existing parameter data. An AppendChunk
call that passes a Null value generates an error; you must manually set
the ADO
Parameter Object Value Property of the Parameter object to a
zero-length string (“”) in order to clear its value.

ADO
Parameter Object Properties

---

Property	Description
Attributes	One or more characteristics of a parameter. This property is currently read-only on UNIX.
Direction	Indicates if the parameter is an input parameter, an output parameter, or both; or if the parameter is the output of a stored procedure.
Name	The name of the parameter.
NumericScale	The scale of numeric values in the parameter.
Precision	The degree of precision for numeric values in the parameter.
Size	The maximum size, in bytes or characters, of a parameter.
Type	The data type of the parameter.
Value	The value assigned to the parameter.

In this section:

ADO
Parameter Object Attributes Property

ADO
Parameter Object Direction Property

ADO
Parameter Object Name Property

ADO
Parameter Object NumericScale Property

ADO
Parameter Object Precision Property

ADO
Parameter Object Size Property

ADO
Parameter Object Type Property

ADO
Parameter Object Value Property

ADO
Parameter Object Attributes Property

---

One or more characteristics of an object. This
property is read-only on UNIX.

Attributes Property Return Values (ADO Parameter Object)

Sets or returns a Long value.

Attributes Property Parameters (ADO Parameter Object)

For an ADO Parameter
Object, the Attributes property is read/write, and its value can be
the sum of any one or more of these ParameterAttributesEnum values:

Value	Description
adParamSigned	Default. The parameter accepts signed values.
adParamNullable	The parameter accepts Null values.
adParamLong	The parameter accepts long binary data.

Attributes Property Remarks (ADO Parameter Object)

Use the Attributes property to set
or return characteristics of Parameter objects.

When you set multiple attributes, you can
sum the appropriate constants. If you set the property value to a sum including
incompatible constants, an error occurs.

Attributes Property Examples (ADO Parameter Object)

This Visual Basic example displays the
value of the Attributes property for Connection, Field,
and Property objects. It uses the ADO
Parameter Object Name Property to display the name of each Field and

Property object.

Public Sub AttributesX

Dim cnn1 As ADODB.Connection

Dim rstEmployees As ADODB.Recordset

Dim fldLoop As ADODB.Field

Dim proLoop As ADODB.Property

Dim strCnn As String

' Open connection and recordset.

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

Set cnn1 = New ADODB.Connection

cnn1.Open strCnn

Set rstEmployees = New ADODB.Recordset

rstEmployees.Open "employee", cnn1, , ,

adCmdTable

' Display the attributes of the connection.

Debug.Print "Connection attributes = " & _

cnn1.Attributes

' Display attributes of the Employee table fields

Debug.Print "Field attributes:"

For Each fldLoop In rstEmployees.Fields

Debug.Print " " & fldLoop.Name & " = " & _

fldLoop.Attributes

Next fldLoop

' Display attributes of the Employee table properties.

Debug.Print "Property attributes:"

For Each proLoop In rstEmployees.Properties

Debug.Print " " & proLoop.Name & " = " & _

proLoop.Attributes

Next proLoop

rstEmployees.Close

cnn1.Close

End Sub

ADO
Parameter Object Direction Property

---

Indicates whether the Parameter
object represents an input parameter, an output parameter, or both, or if the
parameter is the return value from a stored procedure.

Direction Property Return Values

Sets or returns one of the following ParameterDirectionEnum
values

Constant	Description
AdParamInput	Default. Indicates an input parameter.
AdParamOutput	Indicates an output parameter.
AdParamInputOutput	Indicates a two-way parameter.
AdParamReturnValue	Indicates a return value.

Direction Property Remarks

Use the Direction property to
specify how a parameter is passed to or from a procedure. The Direction

property is read/write; this allows you to work with providers that do not
return this information, or to set this information when you do not want ADO to make an extra call
to the provider to retrieve parameter information.

Not all providers can determine the
direction of parameters in their stored procedures. In these cases, you must
set the Direction property prior to executing the query.

ADO
Parameter Object Name Property

---

The name of an object.

Name Property Return Values (ADO Parameter Object)

Sets or returns a String value. The
value is read/write on a Parameter object.

Name Property Remarks (ADO Parameter Object)

Use the Name property to assign a
name to or retrieve the name of a Parameter object.

For Parameter objects not yet
appended to the ADO Parameters
Collection, the Name property is read/write. For appended Parameter

objects and all other objects, the Name property is read-only. Names do
not have to be unique within a collection.

ADO Parameter
Object NumericScale Property

---

The scale of Numeric values in a Parameter
object.

NumericScale Property Return Values

Sets or returns a Byte value,
indicating the number of decimal places to which numeric values will be
resolved.

NumericScale Property Remarks

Use the NumericScale property to
determine how many digits to the right of the decimal point will be used to
represent values for a numeric Parameter object.

For Parameter objects, the NumericScale

property is read/write.

ADO
Parameter Object Precision Property

---

The degree of precision for Numeric

values in a Parameter object.

Precision Property Return Values (ADO Parameter Object)

Sets or returns a Byte value,
indicating the maximum total number of digits used to represent values. The
value is read/write on a Parameter object.

Precision Property Remarks (ADO Parameter Object)

Use the Precision property to
determine the maximum number of digits used to represent values for a numeric Parameter
object.

ADOParameter Object Size Property

---

The maximum size, in bytes or characters,
of a Parameter object.

Size Property Return Values (ADO Parameter Object)

Sets or returns a Long value that
indicates the maximum size in bytes or characters of a value in a Parameter
object.

Size Property Remarks (ADO Parameter Object)

Use the Size property to determine
the maximum size for values written to or read from the ADO
Parameter Object Value Property of a Parameter object. The Size

property is read/write. If you specify a variable-length data type for a Parameter
object, you must set the object’s Size property before appending it to
the ADO
Parameters Collection; otherwise an error occurs. If you have already
appended the Parameter object to the Parameters collection of an ADO Command
Object and you change its type to a variable-length data type, you must set
the Parameter object’s Size property before executing the Command

object; otherwise an error occurs.

If you use the ADO Collections
Refresh Method to obtain parameter information from the provider and it
returns one or more variable-length data type Parameter objects, ADO may
allocate memory for the parameters based on their maximum potential size, which
could cause an error during execution. To prevent an error, you should
explicitly set the Size property for these parameters before executing
the command.

Size Property Example (ADO Parameter Object)

See ActiveConnection property
example.

ADO
Parameter Object Type Property

---

The operational type or data type of a Parameter
object.

Type Property Return Values (ADO Parameter Object)

Sets or returns one of the following DataTypeEnum
values. The corresponding OLE DB type indicators are as follows:

Constant	Description
adArray	OR’d together with another type to indicate that the data is a safe-array of that type (DBTYPE_ARRAY).
adBigInt	An 8-byte signed integer (DBTYPE_I8).
adBinary	A binary value (DBTYPE_BYTES).
adBoolean	A Boolean value (DBTYPE_BOOL).
adByRef	Or’d together with another type to indicate that the data is a pointer to data of the other type (DBTYPE_BYREF).
adBSTR	A null-terminated character string (Unicode) (DBTYPE_BSTR).
adChar	A String value (DBTYPE_STR).
adCurrency	A currency value (DBTYPE_CY). Currency is a fixed-point number with 4 digits to the right of the decimal point. It is stored in an 8-byte signed integer scaled by 10,000.
adDate	A date value (DBTYPE_DATE). A date is stored as a Double, the whole part of which is the number of days since December 30, 1899, and the fractional part of which is the fraction of a day.
adDBDate	A date value (yyyymmdd) (DBTYPE_DBDATE).
adDBTime	A time value (hhmmss) (DBTYPE_DBTIME).
adDBTimeStamp	A date-time stamp (yyyymmddhhmmss plus a fraction in billionths) (DBTYPE_DBTIMESTAMP).
adDecimal	An exact numeric value with a fixed precision and scale (DBTYPE_DECIMAL).
adDouble	A double-precision floating point value (DBTYPE_R8).
adEmpty	No value was specified (DBTYPE_EMPTY).
adError	A 32-bit error code (DBTYPE_ERROR).
adGUID	A globally unique identifier (GUID) (DBTYPE_GUID).
adIDispatch	A pointer to an IDispatch interface on an OLE object (DBTYPE_IDISPATCH).
adInteger	A 4-byte signed integer (DBTYPE_I4).
adIUnknown	A pointer to an IUnknown interface on an OLE object (DBTYPE_IUNKNOWN).
adLongVarBinary	A long binary value.
adLongVarChar	A long String value.
adLongVarWChar	A long null-terminated string value.
adNumeric	An exact numeric value with a fixed precision and scale (DBTYPE_NUMERIC).
adSingle	A single-precision floating point value (DBTYPE_R4).
adSmallInt	A 2-byte signed integer (DBTYPE_I2).
adTinyInt	A 1-byte signed integer (DBTYPE_I1).
adUnsignedBigInt	An 8-byte unsigned integer (DBTYPE_UI8).
adUnsignedInt	A 4-byte unsigned integer (DBTYPE_UI4).
adUnsignedSmallInt	A 2-byte unsigned integer (DBTYPE_UI2).
adUnsignedTinyInt	A 1-byte unsigned integer (DBTYPE_UI1).
adUserDefined	A user-defined variable (DBTYPE_UDT).
adVarBinary	A binary value.
adVarChar	A String value.
adVariant	An Automation Variant (DBTYPE_VARIANT).
adVector	OR’d together with another type to indicate that the data is a DBVECTOR structure, as defined by OLE DB, that contains a count of elements and a pointer to data of the other type (DBTYPE_VECTOR).
adVarWChar	A null-terminated Unicode character string.
adWChar	A null-terminated Unicode character string (DBTYPE_WSTR).

Type Property Remarks (ADO Parameter Object)

For Parameter objects, the Type

property is read/write.

ADO
Parameter Object Value Property

---

Indicates the value assigned to a Parameter

object.

Value Property Return Values (ADO Parameter Object)

Sets or returns a Variant value. Default
value depends on the ADO
Parameter Object Type Property.

Value Property Remarks (ADO Parameter Object)

Use the Value property to set or
return parameter values with Parameter objects.

ADO
allows setting and returning long binary data with the Value property.

ADO Parameter
Object Remarks

---

The Properties collection
is not currently supported on UNIX.

Many providers support parameterized
commands. These are commands where the desired action is defined once, but
variables (or parameters) are used to alter some details of the command. For
example, an SQL SELECT statement could use a parameter to define the matching
criteria of a WHERE clause, and another to define the column name for a SORT BY
clause.

The Parameter objects represent
parameters associated with parameterized queries, or the in/out arguments and
the return values of stored procedures. Depending on the functionality of the
provider, some collections, methods, or properties of a Parameter object
may not be available.

The collections, methods, and properties
of a Parameter object are used to:

·
set or return the name of a parameter with the ADO
Parameter Object Name Property.

·
set or return the value of a parameter with the ADO
Parameter Object Value Property.

·

set or return parameter characteristics with the
ADO
Parameter Object Attributes Property, ADO
Parameter Object Direction Property, ADO
Parameter Object Precision Property, ADO
Parameter Object NumericScale Property, ADO
Parameter Object Size Property, and ADO
Parameter Object Type Property properties.

·
pass long binary or character data to a
parameter with the ADO
Parameter Object AppendChunk Method.

If you know the names and properties of
the parameters associated with the stored procedure or parameterized query you
wish to call, you can use the CreateParameter method to create Parameter
objects with the appropriate property settings and use the ADO Collections
Append Method to add them to the ADO Parameters
Collection. This lets you set and return parameter values without having to
call the ADO
Collections Refresh Method on the Parameters collection to retrieve
the parameter information from the provider, a potentially resource-intensive

ADO
Property Object

---

The ADO Property

object represents a dynamic characteristic of an ADO object that is defined by the provider. This
object is not currently supported on UNIX.

In this section:

ADO
Property Object Properties

ADO
Property Object Remarks

ADOProperty Object Properties

---

Property	Description
Attributes	One or more characteristics of a property.
Name	The name of the property.
Type	The operational or data type of the property.
Value	The value assigned to the property.

In this section:

ADO
Property Object Attributes Property

ADO
Property Object Name Property

ADO
Property Object Type Property

ADO
Property Object Value Property

ADO
Property Object Attributes Property

---

One or more characteristics of an object.

Attributes Property Return Values (ADO Property Object)

Sets or returns a Long value.

Attributes Property Property (ADO Property Object)

For a Property object, the Attributes
property is read-only, and its value can be the sum of any one or more of these
PropertyAttributesEnum values:

Value	Description
adPropNotSupported	The property is not supported by the provider.
adPropRequired	The user must specify a value for this property before the data source is initialized.
adPropOptional	The user does not need to specify a value for this property before the data source is initialized.
adPropRead	The user can read the property.
adPropWrite	The user can set the property.

Attributes Property Remarks (ADO Property Object)

Use the Attributes property to set
or return characteristics of Property objects.

When you set multiple attributes, you can
sum the appropriate constants. If you set the property value to a sum including
incompatible constants, an error occurs.

Attributes Property Examples (ADO Property Object)

This Visual Basic example displays the
value of the Attributes property for Property objects. It uses
the ADO
Property Object Name Property to display the name of each Property

object.

Public Sub AttributesX

Dim cnn1 As ADODB.Connection

Dim rstEmployees As ADODB.Recordset

Dim fldLoop As ADODB.Field

Dim proLoop As ADODB.Property

Dim strCnn As String

' Open connection and recordset.

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

Set cnn1 = New ADODB.Connection

cnn1.Open strCnn

Set rstEmployees = New ADODB.Recordset

rstEmployees.Open "employee", cnn1, , ,

adCmdTable

' Display attributes of the Employee table properties.

Debug.Print "Property attributes:"

For Each proLoop In rstEmployees.Properties

Debug.Print " " & proLoop.Name & " = " & _

proLoop.Attributes

Next proLoop

rstEmployees.Close

cnn1.Close

End Sub

ADO
Property Object Name Property

---

The name of an object.

Name Property Return Values (ADO Property Object)

Sets or returns a String value. The
value is read-only on a Property object.

Name Property Remarks (ADO Property Object)

Use the Name property to assign a
name to or retrieve the name of a Property object.

You can retrieve the Name property
of an object by an ordinal reference, after which the object can be referred to
directly by name. For example, if rstMain.Properties(20).Name yields
Updatability, you can subsequently refer to this property as
rstMain.Properties(“Updatability”).

Name Property Examples (ADO Property Object)

See the ADO
Property Object Attributes Property example.

ADO
Property Object Type Property

---

The operational type or data type of a Property
object.

Type Property Return Values (ADO Property Object)

Sets or returns one of the following DataTypeEnum
values. The corresponding OLE DB type indicators are as follows:

Constant	Description
adArray	Or’d together with another type to indicate that the data is a safe-array of that type (DBTYPE_ARRAY).
adBigInt	An 8-byte signed integer (DBTYPE_I8).
adBinary	A binary value (DBTYPE_BYTES).
adBoolean	A Boolean value (DBTYPE_BOOL).
adByRef	Or’d together with another type to indicate that the data is a pointer to data of the other type (DBTYPE_BYREF).
adBSTR	A null-terminated character string (Unicode) (DBTYPE_BSTR).
adChar	A String value (DBTYPE_STR).
adCurrency	A currency value (DBTYPE_CY). Currency is a fixed-point number with 4 digits to the right of the decimal point. It is stored in an 8-byte signed integer scaled by 10,000.
adDate	A date value (DBTYPE_DATE). A date is stored as a Double, the whole part of which is the number of days since December 30, 1899, and the fractional part of which is the fraction of a day.
adDBDate	A date value (yyyymmdd) (DBTYPE_DBDATE).
adDBTime	A time value (hhmmss) (DBTYPE_DBTIME).
adDBTimeStamp	A date-time stamp (yyyymmddhhmmss plus a fraction in billionths) (DBTYPE_DBTIMESTAMP).
adDecimal	An exact numeric value with a fixed precision and scale (DBTYPE_DECIMAL).
adDouble	A double-precision floating point value (DBTYPE_R8).
adEmpty	No value was specified (DBTYPE_EMPTY).
adError	A 32-bit error code (DBTYPE_ERROR).
adGUID	A globally unique identifier (GUID) (DBTYPE_GUID).
adIDispatch	A pointer to an IDispatch interface on an OLE object (DBTYPE_IDISPATCH).
adInteger	A 4-byte signed integer (DBTYPE_I4).
adIUnknown	A pointer to an IUnknown interface on an OLE object (DBTYPE_IUNKNOWN).
adLongVarBinary	A long binary value.
adLongVarChar	A long String value.
adLongVarWChar	A long null-terminated string value.
adNumeric	An exact numeric value with a fixed precision and scale (DBTYPE_NUMERIC).
adSingle	A single-precision floating point value (DBTYPE_R4).
adSmallInt	A 2-byte signed integer (DBTYPE_I2).
adTinyInt	A 1-byte signed integer (DBTYPE_I1).
adUnsignedBigInt	An 8-byte unsigned integer (DBTYPE_UI8).
adUnsignedInt	A 4-byte unsigned integer (DBTYPE_UI4).
adUnsignedSmallInt	A 2-byte unsigned integer (DBTYPE_UI2).
adUnsignedTinyInt	A 1-byte unsigned integer (DBTYPE_UI1).
adUserDefined	A user-defined variable (DBTYPE_UDT).
adVarBinary	A binary value.
adVarChar	A String value.
adVariant	An Automation Variant (DBTYPE_VARIANT).
adVector	OR’d together with another type to indicate that the data is a DBVECTOR structure, as defined by OLE DB, that contains a count of elements and a pointer to data of the other type (DBTYPE_VECTOR).
adVarWChar	A null-terminated Unicode character string.
adWChar	A null-terminated Unicode character string (DBTYPE_WSTR).

Type Property Remarks (ADO Property Object)

The Type property is read-only.

ADO Property
Object Value Property

---

Indicates the value assigned to a Property
object.

Value Property Return Values (ADO Property Object)

Sets or returns a Variant value. Default
value depends on the ADO
Property Object Type Property.

Value Property Remarks (ADO Property Object)

Use the Value property to set or
return property settings with Property objects.

Value Property Example (ADO Property Object)

This Visual Basic example demonstrates the

Value property with Field and Property objects by
displaying field and property values for the Employees table.

Public Sub ValueX()

Dim rstEmployees As ADODB.Recordset

Dim fldLoop As ADODB.Field

Dim prpLoop As ADODB.Property

Dim strCnn As String

' Open recordset with data from Employee table.

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

Set rstEmployees = New ADODB.Recordset

rstEmployees.Open "employee", strCnn, , , adCmdTable

Debug.Print "Field values in rstEmployees"

' Enumerate the Fields collection of the Employees

' table.

For Each fldLoop In rstEmployees.Fields

` Because Value is the default property of a

` Field object, the use of the actual keyword

` here is optional.

Debug.Print " " & fldLoop.Name & " = " &

fldLoop.Value

Next fldLoop

Debug.Print "Property values in rstEmployees"

' Enumerate the Properties collection of the

' Recordset object.

For Each prpLoop In rstEmployees.Properties

' Because Value is the default property of a

' Property object, the use of the actual keyword

' here is optional.

Debug.Print " " & prpLoop.Name & " = " &

prpLoop.Value

Next prpLoop

rstEmployees.Close

End Sub

ADO
Property Object Remarks

---

ADO
objects have two types of properties: built-in and dynamic. Built-in properties
are those properties implemented in ADO

and immediately available to any new object, using the familiar
MyObject.Property syntax.

Built-in properties do not appear as Property
objects in an object’s ADO Properties
Collection, so while you can change their values, you cannot modify their
characteristics or delete them.

Dynamic properties are defined by the
underlying data provider, and appear in the Properties collection for
the appropriate ADO

object. For example, a property specific to the provider may indicate if an ADO Recordset
Object supports transactions or updating. These additional properties will
appear as Property objects in that Recordset object’s Properties
collection. Dynamic properties can be referenced only through the collection,
using the MyObject.Properties(0) or MyObject.Properties(“Name”)
syntax.

A dynamic Property object has four
built-in properties:

·
The ADO
Property Object Name Property is a string that identifies the property.

·
The ADO
Property Object Type Property is an integer that specifies the property
data type.

·
The ADO
Property Object Value Property is a Variant that contains the property
setting.

·
The ADO
Property Object Attributes Property is a long value that indicates
characteristics of the property specific to the provider.

ADORecordset Object

---

The Recordset object represents the
entire set of records from a database table or the results of an executed
command.

In this section:

ADO
Recordset Object Collections

ADO
Recordset Object Methods

UpdateBatch
Method Remarks

ADO Recordset
Object Properties

ADO Recordset
Object Remarks

ADORecordset Object Collections

---

Collection	Description
Fields	All the stored Field objects of a Recordset object.
Properties	All the Property objects for a specific instance of a Recordset object. This collection is not currently supported on UNIX.

ADO
Recordset Object Methods

---

Method	Description
AddNew	Creates a new record for an updatable Recordset object.
CancelBatch	Cancels a pending batch update. This method is not currently supported on UNIX.
CancelUpdate	Cancels any changes made to the current record prior to calling the Update method.
Clone	Creates a new Recordset object from an existing Recordset object. This method is not currently supported on UNIX.
Close	Closes an open Recordset object and any dependent objects.
Delete	Deletes the current record or group of records from a Recordset object.
GetRows	Retrieves multiple rows from a Recordset object into an array.
Move	Moves the position of the current record in a Recordset object.
MoveFirst, MoveLast, MoveNext, MovePrevious	Moves to the first, first, last, next record in a Recordset object and makes that record the current record.
NextRecordset	Clears the current Recordset object and returns the next recordset by advancing through a series of commands. This method is not currently supported on UNIX.
Open	Opens a cursor.
Requery	Updates the data in a recordset by re-executing the query on which the object is based.
Resync	Refreshes the data in the Recordset object from the underlying database.
Supports	Determines whether a specified Recordset object supports a particular type of functionality.
Update	Saves any changes you make to the current record of a Recordset object.
UpdateBatch	Writes all pending batch updates. This method is not currently supported on UNIX.

In this section:

ADO
Recordset Object AddNew Method

ADO
Recordset Object CancelBatch Method

ADO
Recordset Object CancelUpdate Method

ADO
Recordset Object Clone Method

ADO
Recordset Object Delete Method

ADO
Recordset Object GetRows Method

ADO
Recordset Object Move Method

ADO
Recordset Object MoveFirst, MoveLast, MoveNext, MovePrevious Methods

ADO
Recordset Object NextRecordset Method

ADO
Recordset Object Open Method

ADO
Recordset Object Requery Method

ADO Recordset
Object Resync Method

ADO Recordset
Object Supports Method

ADO Recordset
Object Update Method

ADO Recordset
Object UpdateBatch Method

ADO
Recordset Object AddNew Method

---

Creates a new record for an updateable Recordset

object.

AddNew Method Syntax

recordset.AddNew Fields, Values

AddNew Method Parameters

Fields

An optional single name or an array of
names or ordinal positions of the fields in the new record.

Values

An optional single value or an array of
values for the fields in the new record. If Fields is an array, Values
must also be an array with the same number of members; otherwise, an error
occurs. The order of field names must match the order of field values in each
array.

AddNew Method Remarks

Use the AddNew method to create and
initialize a new record. Use the ADO
Recordset Object Supports Method with adAddNew to verify whether you
can add records to the current Recordset object.

After you call the AddNew method,
the new record becomes the current record and remains current after you call
the ADO
Recordset Object Update Method. If the Recordset object does not
support bookmarks, you may not be able to access the new record once you move
to another record. Depending on your cursor type, you may need to call the ADO
Recordset Object Requery Method to make the new record accessible.

If you call AddNew while editing
the current record or while adding a new record, ADO calls the Update method to save
any changes and then creates the new record.

The behavior of the AddNew method
depends on the updating mode of the Recordset object and whether or not
you pass the Fields and Values arguments.

In immediate update mode (the
provider writes changes to the underlying data source once you call the Update

method), calling the AddNew method without arguments sets the ADO
Recordset Object EditMode Property to adEditAdd. The provider caches
any field value changes locally. Calling the Update method posts the new
record to the database and resets the EditMode property to adEditNone.
If you pass the Fields and Values arguments, ADO immediately posts the new record to the
database (no Update call is necessary); the EditMode property
value does not change (adEditNone).

In batch update mode (the
provider caches multiple changes and writes them to the underlying data source
only when you call the UpdateBatch method), calling the AddNew
method without arguments sets the EditMode property to adEditAdd.
The provider caches any field value changes locally. Calling the Update

method adds the new record to the current recordset and resets the EditMode
property to adEditNone, but the provider does not post the changes to
the underlying database until you call the ADO
Recordset Object UpdateBatch Method. If you pass the Fields and Values
arguments, ADO
sends the new record to the provider for storage in a cache; you need to call
the UpdateBatch method to post the new record to the underlying database.

Batch updating is not currently supported on UNIX.

ADO
Recordset Object CancelBatch Method

---

Cancels a pending batch update. This
method is not currently supported on UNIX.

CancelBatch Method Syntax

recordset.CancelBatch AffectRecords

CancelBatch Method Parameters

AffectRecords

An optional AffectEnum value that
determines how many records the CancelBatch method will affect. It can
be one of the following constants:

Constant	Description
adAffectCurrent	Cancels pending updates only for the current record.
adAffectGroup	Cancels pending updates for records that satisfy the current ADO Recordset Object Filter Property setting. You must set the Filter property to one of the valid predefined constants in order to use this option.
adAffectAll	Default. Cancels pending updates for all the records in the Recordset object, including any hidden by the current Filter property setting.

CancelBatch Method Remarks

Use the CancelBatch method to
cancel any pending updates in a recordset in batch update mode. If the
recordset is in immediate update mode, calling CancelBatch without adAffectCurrent
generates an error.

If you are editing the current record or
are adding a new record when you call CancelBatch, ADO first calls the ADO
Recordset Object CancelUpdate Method to cancel any cached changes; after
that, all pending changes in the recordset are canceled.

It’s possible that the current record will
be indeterminable after a CancelBatch call, especially if you were in
the process of adding a new record. For this reason, it is prudent to set the
current record position to a known location in the recordset after the CancelBatch

call. For example, call the ADO
Recordset Object MoveFirst, MoveLast, MoveNext, MovePrevious Methods.

If the attempt to cancel the pending
updates fails because of a conflict with the underlying data (for example, a
record has been deleted by another user), the provider returns warnings to the ADO Errors
Collection but does not halt program execution. A run-time error occurs
only if there are conflicts on all the requested records. Use the Filter
property (adFilterAffectedRecords) and the ADO
Recordset Object Status Property to locate records with conflicts.

CancelBatch Method Examples

This Visual Basic example demonstrates the
ADO
Recordset Object UpdateBatch Method in conjunction with the CancelBatch method.

Public Sub UpdateBatchX()

Dim rstTitles As ADODB.Recordset

Dim strCnn As String

Dim strTitle As String

Dim strMessage As String

` Assign connection string to variable.

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

Set rstTitles = New ADODB.Recordset

rstTitles.CursorType = adOpenKeyset

rstTitles.LockType = adLockBatchOptimistic

rstTitles.Open "titles", strCnn, , , adCmdTable

rstTitles.MoveFirst

` Loop through recordset and ask user if she wants

` to change the type for a specified title.

Do Until rstTitles.EOF

If Trim(rstTitles!Type) = "psychology" Then

strTitle = rstTitles!Title

strMessage = "Title: " & strTitle & vbCr & _

"Change type to self help?"

If MsgBox(strMessage, vbYesNo) = vbYes Then

rstTitles!Type = "self_help"

End If

End If

rstTitles.MoveNext

Loop

` Ask if the user wants to commit to all the

` changes made above.

If MsgBox("Save all changes?", vbYesNo) = vbYes Then

rstTitles.UpdateBatch

Else

rstTitles.CancelBatch

End If

` Print current data in recordset.

rstTitles.Requery

rstTitles.MoveFirst

Do While Not rstTitles.EOF

Debug.Print rstTitles!Title & " - " & rstTitles!Type

rstTitles.MoveNext

Loop

` Restore original values because this is a demonstration.

rstTitles.MoveFirst

Do Until rstTitles.EOF

If Trim(rstTitles!Type) = "self_help" Then

rstTitles!Type = "psychology"

End If

rstTitles.MoveNext

Loop

rstTitles.UpdateBatch

rstTitles.Close

End Sub

ADO
Recordset Object CancelUpdate Method

---

Cancels any changes made to the current
record or to a new record prior to calling the Update method.

CancelUpdate Method Syntax

recordset.CancelUpdate

CancelUpdate Method Remarks

Use the CancelUpdate method to
cancel any changes made to the current record or to discard a newly added
record.

Note

You cannot undo changes to the current record or to a new record after you call the ADO Recordset Object Update Method unless the changes are either part of a transaction that you can roll back with the RollbackTrans method or part of a batch update that you can cancel with the ADO Recordset Object CancelBatch Method.

If you are adding a new record
when you call the CancelUpdate method, the record that was current prior
to the ADO
Recordset Object AddNew Method call becomes the current record again. If
you have not changed the current record or added a new record, calling the CancelUpdate
method generates an error.

CancelUpdate Method Examples

These Visual Basic examples demonstrate
the ADO
Recordset Object Update Method in conjunction with the CancelUpdate
method.

Public Sub UpdateX()

Dim rstEmployees As ADODB.Recordset

Dim strOldFirst As String

Dim strOldLast As String

Dim strMessage As String

` Open recordset with names from Employee table.

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

Set rstEmployees = New ADODB.Recordset

rstEmployees.CursorType = adOpenKeyset

rstEmployees.LockType = adLockOptimistic

rstEmployees.Open "SELECT fname, lname " & _

"FROM Employee ORDER BY lname", strCnn, , , adCmdText

` Store original data.

strOldFirst = rstEmployees!fname

strOldLast = rstEmployees!lname

` Change data in edit buffer.

rstEmployees!fname = "Linda"

rstEmployees!lname = "Kobara"

` Show contents of buffer and get user input.

strMessage = "Edit in progress:" & vbCr & _

" Original data = " & strOldFirst & " " & _

strOldLast & vbCr & " Data in buffer = " & _

rstEmployees!fname & " " & rstEmployees!lname & vbCr & vbCr & _

"Use Update to replace the original data with " & _

"the buffered data in the Recordset?"

If MsgBox(strMessage, vbYesNo) = vbYes Then

rstEmployees.Update

Else

rstEmployees.CancelUpdate

End If

` Show the resulting data.

MsgBox "Data in recordset = " & rstEmployees!fname & " " & _

rstEmployees!lname

` Restore original data because this is a demonstration.

If Not (strOldFirst = rstEmployees!fname And _

strOldLast = rstEmployees!lname) Then

rstEmployees!fname = strOldFirst

rstEmployees!lname = strOldLast

rstEmployees.Update

End If

rstEmployees.Close

End Sub

This example demonstrates the Update
method in conjunction with the AddNew method.

Public Sub UpdateX2()

Dim cnn1 As ADODB.Connection

Dim rstEmployees As ADODB.Recordset

Dim strEmpID As String

Dim strOldFirst As String

Dim strOldLast As String

Dim strMessage As String

` Open a connection.

Set cnn1 = New ADODB.Connection

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

cnn1.Open strCnn

` Open recordset with data from Employee table.

Set rstEmployees = New ADODB.Recordset

rstEmployees.CursorType = adOpenKeyset

rstEmployees.LockType = adLockOptimistic

rstEmployees.Open "employee", cnn1, , , adCmdTable

rstEmployees.AddNew

strEmpID = "B-S55555M"

rstEmployees!emp_id = strEmpID

rstEmployees!fname = "Bill"

rstEmployees!lname = "Sornsin"

` Show contents of buffer and get user input.

strMessage = "AddNew in progress:" & vbCr & _

"Data in buffer = " & rstEmployees!emp_id & ", " & _

rstEmployees!fname & " " & rstEmployees!lname & vbCr & vbCr & _

"Use Update to save buffer to recordset?"

If MsgBox(strMessage, vbYesNoCancel) = vbYes Then

rstEmployees.Update

` Go to the new record and show the resulting data.

MsgBox "Data in recordset = " & rstEmployees!emp_id & ", " & _

rstEmployees!fname & " " & rstEmployees!lname

Else

rstEmployees.CancelUpdate

MsgBox "No new record added."

End If

` Delete new data because this is a demonstration.

cnn1.Execute "DELETE FROM employee WHERE emp_id = '" & strEmpID & "'"

rstEmployees.Close

End Sub

ADO
Recordset Object Clone Method

---

Creates a duplicate Recordset object
from an existing Recordset object. This method is not currently
supported on UNIX.

Clone Method Syntax

Set rstDuplicate = rstOriginal.Clone ()

Clone Method Parameters

rstDuplicate

An object variable identifying the
duplicate Recordset object you’re creating.

rstOriginal

An object variable identifying the Recordset
object you want to duplicate.

Clone Method Remarks

Use the Clone method to create
multiple, duplicate Recordset objects, particularly if you want to be
able to maintain more than one current record in a given set of records. Using
the Clone method is more efficient than creating and opening a new Recordset
object with the same definition as the original.

The current record of a newly created
clone is set to the first record.

Changes you make to one Recordset
object are visible in all of its clones regardless of cursor type. However,
once you execute the ADO
Recordset Object Requery Method on the original Recordset,
the clones will no longer be synchronized to the original.

Closing the original recordset does not
close its copies; closing a copy does not close the original or any of the
other copies.

You can only clone a Recordset
object that supports bookmarks. Bookmark values are interchangeable; that is, a
bookmark reference from one Recordset object refers to the same record
in any of its clones.

Clone Method Return Values

Returns a Recordset object
reference.

ADO
Recordset Object Close Method

Closes an open object and any dependent
objects.

Close Method Syntax

object.Close

Close Method Remarks

Use the Close method to close a Recordset
object to free any associated system resources. Closing an object does not
remove it from memory; you may change its property settings and open it again
later. To completely eliminate an object from memory, set the object variable
to Nothing.

Using the Close method to close a Recordset

object releases the associated data and any exclusive access you may have had
to the data through this particular Recordset object. You can later call
the ADO
Recordset Object Open Method to reopen the Recordset with the same
or modified attributes. While the Recordset object is closed, calling
any methods that require a live cursor generates an error.

If an edit is in progress while in
immediate update mode, calling the Close method generates an error; call
the ADO
Recordset Object Update Method or ADO
Recordset Object CancelUpdate Method first. If you close the Recordset

object during batch updating, all changes since the last ADO
Recordset Object UpdateBatch Method call are lost.

If you use the Clone method to
create copies of an open Recordset object, closing the original or a
clone does not affect any of the other copies.

Close Method Examples

This VBScript example uses the Open
and Close methods on both Recordset and Connection objects
that have been opened.

<!-- #Include file="ADOVBS.INC" -->

<HTML><HEAD>

<TITLE>ADO 1.5 Open Method</TITLE>

</HEAD><BODY>

<FONT FACE="MS SANS SERIF" SIZE=2>

<Center><H3>ADO Open Method</H3>

<TABLE WIDTH=600 BORDER=0>

<TD VALIGN=TOP ALIGN=LEFT COLSPAN=3><FONT SIZE=2>

<!--- ADO Connection used to create 2 recordsets-->

<%

Set OBJdbConnection = Server.CreateObject("ADODB.Connection")

OBJdbConnection.Open "AdvWorks"

SQLQuery = "SELECT * FROM Customers"

'First Recordset RSCustomerList

Set RSCustomerList = OBJdbConnection.Execute(SQLQuery)

'Second Recordset RsProductist

Set RsProductList = Server.CreateObject("ADODB.Recordset")

RsProductList.CursorType = adOpenDynamic

RsProductList.LockType = adLockOptimistic

RsProductList.Open "Products", OBJdbConnection

%>

<TABLE COLSPAN=8 CELLPADDING=5 BORDER=0>

<!-- BEGIN column header row for Customer Table-->

<TR><TD ALIGN=CENTER BGCOLOR="#008080">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Company Name</FONT></TD>

<TD ALIGN=CENTER BGCOLOR="#008080">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Contact Name</FONT></TD>

<TD ALIGN=CENTER WIDTH=150 BGCOLOR="#008080">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>E-mail address</FONT></TD>

<TD ALIGN=CENTER BGCOLOR="#008080">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>City</FONT></TD>

<TD ALIGN=CENTER BGCOLOR="#008080">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>State/Province</FONT></TD></TR>

<!--Display ADO Data from Customer Table-->

<% Do While Not RScustomerList.EOF %>

<TR><TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RSCustomerList("CompanyName")%>

</FONT></TD>

<TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RScustomerList("ContactLastName") & ", " %>

<%= RScustomerList("ContactFirstName") %>

</FONT></TD>

<TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RScustomerList("ContactLastName")%>

</FONT></TD>

<TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RScustomerList("City")%>

</FONT></TD>

<TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RScustomerList("StateOrProvince")%>

</FONT></TD></TR>

<!-Next Row = Record Loop and add to html table-->

<%

RScustomerList.MoveNext

Loop

RScustomerList.Close

OBJdbConnection.Close

%>

</TABLE>

<HR>

<TABLE COLSPAN=8 CELLPADDING=5 BORDER=0>

<!-- BEGIN column header row for Product List Table-->

<TR><TD ALIGN=CENTER BGCOLOR="#800000">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Product Type</FONT></TD>

<TD ALIGN=CENTER BGCOLOR="#800000">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Product Name</FONT></TD>

<TD ALIGN=CENTER WIDTH=350 BGCOLOR="#800000">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Product Description</FONT></TD>

<TD ALIGN=CENTER BGCOLOR="#800000">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Unit Price</FONT></TD></TR>

<!-- Display ADO Data Product List-->

<% Do While Not RsProductList.EOF %>

<TR> <TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RsProductList("ProductType")%>

</FONT></TD>

<TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RsProductList("ProductName")%>

</FONT></TD>

<TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RsProductList("ProductDescription")%>

</FONT></TD>

<TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RsProductList("UnitPrice")%>

</FONT></TD>

<!-- Next Row = Record -->

<%

RsProductList.MoveNext

Loop

'Remove Objects from Memory Freeing

Set RsProductList = Nothing

Set OBJdbConnection = Nothing

%>

</TABLE></FONT></Center></BODY></HTML>

ADO
Recordset Object Delete Method

---

Deletes the current record or a group of
records.

Delete Method Syntax

recordset.Delete AffectRecords

Delete Method Parameters

AffectRecords

An optional AffectEnum value that
determines how many records the Delete method will affect. Can be one of
the following constants:

Constant	Description
adAffectCurrent	Default. Delete only the current record.
adAffectGroup	Delete the records that satisfy the current ADO Recordset Object Filter Property setting. You must set the Filter property to one of the valid predefined constants in order to use this option. The Filter property is not currently supported on UNIX.

Delete Method Remarks

Using the Delete method marks the
current record or a group of records in a Recordset object for deletion.
If the Recordset object doesn’t allow record deletion, an error occurs.
If you are in immediate update mode, deletions occur in the database
immediately. Otherwise, the records are marked for deletion from the cache and
the actual deletion happens when you call the ADO
Recordset Object UpdateBatch Method. (Use the Filter property to
view the deleted records.)

Retrieving field values from the deleted
record generates an error. After deleting the current record, the deleted
record remains current until you move to a different record. Once you move away
from the deleted record, it is no longer accessible.

If you nest deletions in a transaction,
you can recover deleted records with the RollbackTrans method. If you
are in batch update mode, you can cancel a pending deletion or group of pending
deletions with the ADO
Recordset Object CancelBatch Method.

If the attempt to delete records fails
because of a conflict with the underlying data (for example, a record has
already been deleted by another user), the provider returns warnings to the ADO Errors
Collection, but does not halt program execution. A run-time error occurs
only if there are conflicts on all the requested records.

Delete Method Examples

This VBScript example uses the Delete
method to remove a specified record from a recordset.

<!-- #Include file="ADOVBS.INC" -->

<% Language = VBScript %>

<HTML>

<HEAD><TITLE>ADO 1.5 Delete Method</TITLE>

</HEAD><BODY>

<FONT FACE="MS SANS SERIF" SIZE=2>

<Center><H3>ADO Delete Method</H3>

<!--- ADO Connection Object used to create recordset-->

<%

'Create and Open Connection Object

Set OBJdbConnection = Server.CreateObject("ADODB.Connection")

OBJdbConnection.Open "AdvWorks"

'Create and Open Recordset Object

Set RsCustomerList = Server.CreateObject("ADODB.Recordset")

RsCustomerList.ActiveConnection = OBJdbConnection

RsCustomerList.CursorType = adOpenKeyset

RsCustomerList.LockType = adLockOptimistic

RsCustomerList.Source = "Customers"

RsCustomerList.Open

%>

<!-- Move to designated Record and Delete It -->

<%

If Not IsEmpty(Request.Form("WhichRecord")) Then

`Get value to move from Form Post method

Moves = Request.Form("WhichRecord")

RsCustomerList.Move CInt(Moves)

If Not RsCustomerList.EOF or RsCustomerList.BOF Then

RsCustomerList.Delete 1

RsCustomerList.MoveFirst

Else

Response.Write "Not a Valid Record Number"

RsCustomerList.MoveFirst

End If

End If

%>

<!-- BEGIN column header row for Customer Table-->

<TABLE COLSPAN=8 CELLPADDING=5 BORDER=0><TR>

<TD ALIGN=CENTER BGCOLOR="#008080">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Company Name</FONT>

</TD>

<TD ALIGN=CENTER BGCOLOR="#008080">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Contact Name</FONT>

</TD>

<TD ALIGN=CENTER WIDTH=150 BGCOLOR="#008080">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Phone Number</FONT>

</TD>

<TD ALIGN=CENTER BGCOLOR="#008080">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>City</FONT>

</TD>

<TD ALIGN=CENTER BGCOLOR="#008080">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>State/Province</FONT>

</TD></TR>

<!--Display ADO Data from Customer Table Loop through Recordset

adding one Row to HTML Table each pass-->

<% Do While Not RsCustomerList.EOF %>

<TR><TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RSCustomerList("CompanyName")%>

</FONT></TD>

<TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RScustomerList("ContactLastName") & ", " %>

<%= RScustomerList("ContactFirstName") %>

</FONT></TD>

<TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RScustomerList("PhoneNumber")%>

</FONT></TD>

<TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RScustomerList("City")%>

</FONT></TD>

<TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RScustomerList("StateOrProvince")%>

</FONT></TD>

</TR>

<!-Next Row = Record Loop and add to html table-->

<%

RScustomerList.MoveNext

Loop

%>

</Table></Center></FONT>

<!-- Do Client side Input Data Validation Move to named

 record and Delete it -->

<Center>

<H4>Clicking Button Will Remove Designated Record</H4>

<H5>There are <%=RsCustomerList.RecordCount - 1%> Records in this Set</H5>

<Form Method = Post Action = "Delete.asp" Name = Form>

<Input Type = Text Name = "WhichRecord" Size = 3></Form>

<Input Type = Button Name = cmdDelete Value = "Delete Record"></Center>

</BODY>

<Script Language = "VBScript">

Sub cmdDelete_OnClick

If IsNumeric(Document.Form.WhichRecord.Value) Then

Document.Form.WhichRecord.Value = CInt(Document.Form.WhichRecord.Value)

Dim Response

Response = MsgBox("Are You Sure About Deleting This Record?", vbYesNo, "ADO-ASP Example")

If Response = vbYes Then

Document.Form.Submit

End If

Else

MsgBox "You Must Enter a Valid Record Number",,"ADO-ASP Example"

End If

End Sub

</Script>

</HTML>

ADO
Recordset Object GetRows Method

---

Retrieves multiple records of a recordset
into an array.

GetRows Method Syntax

array = recordset.GetRows( Rows, Start, Fields )

GetRows Method Parameters

array

Two-dimensional Array containing
records.

Rows

An optional Long expression
indicating the number of records to retrieve. Default is adGetRowsRest

(-1).

Start

An optional String or Variant that
evaluates to the bookmark for the record from which the GetRows
operation should begin. You can also use one of the following BookmarkEnum
values:

Constant	Description
AdBookmarkCurrent	Start at the current record.
AdBookmarkFirst	Start at the first record.
AdBookmarkLast	Start at the last record.

Fields

An optional Variant representing
a single field name or ordinal position or an array of field names or ordinal
position numbers. ADO
returns only the data in these fields.

GetRows Method Return Values

Returns a two-dimensional array.

GetRows Method Remarks

Use the GetRows method to copy
records from a recordset into a two-dimensional array. The first subscript
identifies the field and the second identifies the record number. The array
variable is automatically dimensioned to the correct size when the GetRows

method returns the data.

If you do not specify a value for the Rows
argument, the GetRows method automatically retrieves all the records in
the Recordset object. If you request more records than are available, GetRows
returns only the number of available records.

If the Recordset object supports
bookmarks, you can specify at which record the GetRows method should
begin retrieving data by passing the value of that record’s ADO
Recordset Object Bookmark Property.

If you want to restrict the fields the GetRows
call returns, you can pass either a single field name/number or an array of
field names/numbers in the Fields argument.

After you call GetRows, the next
unread record becomes the current record, or the ADO
Recordset Object BOF, EOF Properties property is set to True
if there are no more records.

GetRows Method Examples

This Visual Basic example uses the GetRows

method to retrieve a specified number of rows from a recordset and to fill an
array with the resulting data. The GetRows method will return fewer than
the desired number of rows in two cases: either if EOF has been reached,
or if GetRows tried to retrieve a record that was deleted by another
user. The function returns False only if the second case occurs. The GetRowsOK
function is required for this procedure to run.

Public Sub GetRowsX()

Dim rstEmployees As ADODB.Recordset

Dim strCnn As String

Dim strMessage As String

Dim intRows As Integer

Dim avarRecords As Variant

Dim intRecord As Integer

' Open recordset with names and hire dates from employee table.

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

Set rstEmployees = New ADODB.Recordset

rstEmployees.Open "SELECT fName, lName, hire_date " & _

"FROM Employee ORDER BY lName", strCnn, , , adCmdText

Do While True

` Get user input for number of rows.

strMessage = "Enter number of rows to retrieve."

intRows = Val(InputBox(strMessage))

If intRows <= 0 Then Exit Do

` If GetRowsOK is successful, print the results,

` noting if the end of the file was reached.

If GetRowsOK(rstEmployees, intRows, _

avarRecords) Then

If intRows > UBound(avarRecords, 2) + 1 Then

Debug.Print "(Not enough records in " & _

"Recordset to retrieve " & intRows & _

" rows.)"

End If

Debug.Print UBound(avarRecords, 2) + 1 & _

" records found."

` Print the retrieved data.

For intRecord = 0 To UBound(avarRecords, 2)

Debug.Print " " & _

avarRecords(0, intRecord) & " " & _

avarRecords(1, intRecord) & ", " & _

avarRecords(2, intRecord)

Next intRecord

Else

` Assuming the GetRows error was due to data

` changes by another user, use Requery to

` refresh the Recordset and start over.

If MsgBox("GetRows failed--retry?", _

vbYesNo) = vbYes Then

rstEmployees.Requery

Else

Debug.Print "GetRows failed!"

Exit Do

End If

End If

` Because using GetRows leaves the current

` record pointer at the last record accessed,

` move the pointer back to the beginning of the

` Recordset before looping back for another search.

rstEmployees.MoveFirst

Loop

rstEmployees.Close

End Sub

Public Function GetRowsOK(rstTemp As ADODB.Recordset, _

intNumber As Integer, avarData As Variant) As Boolean

` Store results of GetRows method in array.

avarData = rstTemp.GetRows(intNumber)

` Return False only if fewer than the desired

` number of rows were returned, but not because the

` end of the Recordset was reached.

If intNumber > UBound(avarData, 2) + 1 And _

Not rstTemp.EOF Then

GetRowsOK = False

Else

GetRowsOK = True

End If

End Function

ADO
Recordset Object Move Method

---

Moves the position of the current record
in a Recordset object.

Move Method Syntax

recordset.Move NumRecords, Start

Move Method Parameters

NumRecords

A signed Long expression specifying
the number of records the current record position moves.

Start

An optional String or Variant that
evaluates to a bookmark. You can also use one of the following BookmarkEnum
values:

Constant	Description
AdBookmarkCurrent	Default. Start at the current record.
AdBookmarkFirst	Start at the first record.
AdBookmarkLast	Start at the last record.

Move Method Remarks

The Move method is supported on all
Recordset objects.

If the NumRecords argument is
greater than zero, the current record position moves forward (toward the end of
the recordset). If NumRecords is less than zero, the current record
position moves backward (toward the beginning of the recordset).

If the Move call would move the
current record position to a point before the first record, ADO sets the current record to the position
before the first record in the recordset (BOF is True). An
attempt to move backward when the ADO
Recordset Object BOF, EOF Properties property is already True

generates an error.

If the Move call would move the
current record position to a point after the last record, ADO sets the current record to the position
after the last record in the recordset (EOF is True). An attempt
to move forward when the ADO
Recordset Object BOF, EOF Properties property is already True

generates an error.

Calling the Move method from an
empty Recordset object generates an error.

If you pass the Start argument,
the move is relative to the record with this bookmark, assuming the Recordset

object supports bookmarks. If not specified, the move is relative to the
current record.

If you are using the ADO
Recordset Object CacheSize Property to locally cache records from the
provider, passing a NumRecords that moves the current record position
outside of the current group of cached records forces ADO to retrieve a new group of records
starting from the destination record. The CacheSize property determines
the size of the newly retrieved group, and the destination record is the first
record retrieved.

If the Recordset object is
forward-only, a user can still pass a NumRecords less than zero as
long as the destination is within the current set of cached records. If the Move
call would move the current record position to a record before the first cached
record, an error will occur. Thus, you can use a record cache that supports
full scrolling over a provider that only supports forward scrolling. Because
cached records are loaded into memory, you should avoid caching more records
than is necessary. Even if a forward-only Recordset object supports
backward moves in this way, calling the ADO
Recordset Object MoveFirst, MoveLast, MoveNext, MovePrevious Methods method
on any forward-only Recordset object still generates an error.

Move Method Example

This VBScript example uses the Move method
to position the record pointer based on user input. Try entering a letter or
non-integer to see the error-handling work.

<!-- #Include file="ADOVBS.INC" -->

<% Language = VBScript %>

<HTML><HEAD>

<TITLE>ADO 1.5 Move Methods</TITLE></HEAD>

<BODY>

<FONT FACE="MS SANS SERIF" SIZE=2>

<Center>

<H3>ADO Move Methods</H3>

<%

'Create and Open Connection Object

Set OBJdbConnection = Server.CreateObject("ADODB.Connection")

OBJdbConnection.Open "AdvWorks"

'Create and Open Recordset Object

Set RsCustomerList = Server.CreateObject("ADODB.Recordset")

RsCustomerList.ActiveConnection = OBJdbConnection

RsCustomerList.CursorType = adOpenKeyset

RsCustomerList.LockType = adLockOptimistic

RsCustomerList.Source = "Customers"

RsCustomerList.Open

'Check number of user moves this session

'Increment by amount in Form

Session("Clicks") = Session("Clicks") + Request.Form("MoveAmount")

Clicks = Session("Clicks")

'Move to last known recordset position plus amount passed by Form Post method

RsCustomerList.Move CInt(Clicks)

'Error Handling

If RsCustomerList.EOF Then

Session("Clicks") = RsCustomerList.RecordCount

Response.Write "This is the Last Record"

RsCustomerList.MoveLast

Else If RsCustomerList.BOF Then

Session("Clicks") = 1

RsCustomerList.MoveFirst

Response.Write "This is the First Record"

End If

End If

%>

<H3>Current Record Number is <BR>

<% If Session("Clicks") = 0 Then

Session("Clicks") = 1

End If

Response.Write(Session("Clicks") )%> of <%=RsCustomerList.RecordCount%></H3>

<HR>

<Center><TABLE COLSPAN=8 CELLPADDING=5 BORDER=0>

<!-- BEGIN column header row for Customer Table-->

<TR>

<TD ALIGN=CENTER BGCOLOR="#008080">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Company Name</FONT>

</TD>

<TD ALIGN=CENTER BGCOLOR="#008080">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Contact Name</FONT>

</TD>

<TD ALIGN=CENTER WIDTH=150 BGCOLOR="#008080">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Phone Number</FONT>

</TD>

<TD ALIGN=CENTER BGCOLOR="#008080">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>City</FONT>

</TD>

<TD ALIGN=CENTER BGCOLOR="#008080">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>State/Province</FONT>

</TD>

</TR>

<!--Display ADO Data from Customer Table-->

<TR>

<TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RSCustomerList("CompanyName")%>

</FONT></TD>

<TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RScustomerList("ContactLastName") & ", " %>

<%= RScustomerList("ContactFirstName") %>

</FONT></TD>

<TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RScustomerList("PhoneNumber")%>

</FONT></TD>

<TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RScustomerList("City")%>

</FONT></TD>

<TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RScustomerList("StateOrProvince")%>

</FONT></TD>

</TR> </Table></FONT>

<HR>

<Input Type = Button Name = cmdDown Value = "< ">

<Input Type = Button Name = cmdUp Value = " >">

<H5>Click Direction Arrows for Previous or Next Record

<BR> Click Move Amount to use Move Method

Enter Number of Records to Move + or - </H5>

<Table>

<Form Method = Post Action="Move.asp" Name=Form>

<TR><TD><Input Type="Button" Name = Move Value="Move Amount "></TD><TD></TD><TD>

<Input Type="Text" Size="4" Name="MoveAmount" Value = 0></TD><TR>

</Form></Table></Center>

</BODY>

<Script Language = "VBScript">

Sub Move_OnClick

' Make sure move value entered is an integer

If IsNumeric(Document.Form.MoveAmount.Value)Then

Document.Form.MoveAmount.Value = CInt(Document.Form.MoveAmount.Value)

Document.Form.Submit

Else

MsgBox "You Must Enter a Number", ,"ADO-ASP Example"

Document.Form.MoveAmount.Value = 0

End If

End Sub

Sub cmdDown_OnClick

Document.Form.MoveAmount.Value = -1

Document.Form.Submit

End Sub

Sub cmdUp_OnClick

Document.Form.MoveAmount.Value = 1

Document.Form.Submit

End Sub

</Script>

</HTML>

ADO
Recordset Object MoveFirst, MoveLast, MoveNext, MovePrevious Methods

---

These methods move to the first, last,
next, or previous record in a specified Recordset object and make that
record the current record.

MoveFirst, MoveLast, MoveNext, MovePrevious Methods
Syntax

recordset.{MoveFirst | MoveLast | MoveNext | MovePrevious}

MoveFirst, MoveLast, MoveNext, MovePrevious Methods
Remarks

Use the MoveFirst method to move
the current record position to the first record in the recordset.

Use the MoveLast method to move the
current record position to the last record in the recordset. The Recordset
object must support bookmarks or backward cursor movement; otherwise, the
method call will generate an error.

Use the MoveNext method to move the
current record position one record forward (toward the bottom of the recordset).
If the last record is the current record and you call the MoveNext
method, ADO

sets the current record to the position after the last record in the recordset
(EOF is True). An attempt to move forward when the ADO
Recordset Object BOF, EOF Properties property is already True
generates an error.

Use the MovePrevious method to move
the current record position one record backward (toward the top of the
recordset). The Recordset object must support bookmarks or backward
cursor movement; otherwise, the method call will generate an error. If the
first record is the current record and you call the MovePrevious method,

ADO sets the
current record to the position before the first record in the recordset (BOF
is True). An attempt to move backward when the ADO
Recordset Object BOF, EOF Properties property is already True
generates an error. If the Recordset object does not support either
bookmarks or backward cursor movement, the MovePrevious method will
generate an error.

If the recordset is forward-only and you
want to support both forward and backward scrolling, you can use the ADO
Recordset Object CacheSize Property to create a record cache that will
support backward cursor movement through the ADO
Recordset Object Move Method. Because cached records are loaded into
memory, you should avoid caching more records than is necessary. You can call
the MoveFirst method in a forward-only Recordset object; doing so
may cause the provider to re-execute the command that generated the Recordset
object.

MoveFirst, MoveLast, MoveNext, MovePrevious Methods
Example

This VBScript example uses the MoveFirst,
MoveLast, MoveNext, and MovePrevious methods to move the
record pointer of a recordset based on the supplied command. The MoveAny

function is required for this procedure to run. Try moving beyond the upper or
lower limits of the recordset to see error-handling work.

<!-- #Include file="ADOVBS.INC" -->

<% Language = VBScript %>

<HTML><HEAD>

<TITLE>ADO 1.5 MoveNext MovePrevious MoveLast MoveFirst Methods</TITLE></HEAD>

<BODY>

<FONT FACE="MS SANS SERIF" SIZE=2>

<Center>

<H3>ADO Methods<BR>MoveNext MovePrevious MoveLast MoveFirst</H3>

<!-- Create Connection and Recordset Objects on Server -->

<%

'Create and Open Connection Object

Set OBJdbConnection = Server.CreateObject("ADODB.Connection")

OBJdbConnection.Open "AdvWorks"

'Create and Open Recordset Object

Set RsCustomerList = Server.CreateObject("ADODB.Recordset")

RsCustomerList.ActiveConnection = OBJdbConnection

RsCustomerList.CursorType = adOpenKeyset

RsCustomerList.LockType = adLockOptimistic

RsCustomerList.Source = "Customers"

RsCustomerList.Open

' Check Request.Form collection to see if any moves are recorded

If Not IsEmpty(Request.Form("MoveAmount")) Then

'Keep track of the number and direction of moves this session

Session("Moves") = Session("Moves") + Request.Form("MoveAmount")

Clicks = Session("Moves")

'Move to last known position

RsCustomerList.Move CInt(Clicks)

'Check if move is + or - and do error checking

If CInt(Request.Form("MoveAmount")) = 1 Then

If RsCustomerList.EOF Then

Session("Moves") = RsCustomerList.RecordCount

RsCustomerList.MoveLast

End If

RsCustomerList.MoveNext

End If

If Request.Form("MoveAmount") < 1 Then

RsCustomerList.MovePrevious

End If

'Check if First Record or Last Record Command Buttons Clicked

If Request.Form("MoveLast") = 3 Then

RsCustomerList.MoveLast

Session("Moves") = RsCustomerList.RecordCount

End If

If Request.Form("MoveFirst") = 2 Then

RsCustomerList.MoveFirst

Session("Moves") = 1

End If

End If

' Do Error checking for combination of Move Button clicks

If RsCustomerList.EOF Then

Session("Moves") = RsCustomerList.RecordCount

RsCustomerList.MoveLast

Response.Write "This is the Last Record"

End If

If RsCustomerList.BOF Then

Session("Moves") = 1

RsCustomerList.MoveFirst

Response.Write "This is the First Record"

End If

%>

<H3>Current Record Number is <BR>

<!-- Display Current Record Number and Recordset Size -->

<% If IsEmpty(Session("Moves")) Then

Session("Moves") = 1

End If

%>

<%Response.Write(Session("Moves") )%> of <%=RsCustomerList.RecordCount%></H3>

<HR>

<Center><TABLE COLSPAN=8 CELLPADDING=5 BORDER=0>

<!-- BEGIN column header row for Customer Table-->

<TR><TD ALIGN=CENTER BGCOLOR="#008080">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Company Name</FONT>

</TD>

<TD ALIGN=CENTER BGCOLOR="#008080">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Contact Name</FONT>

</TD>

<TD ALIGN=CENTER WIDTH=150 BGCOLOR="#008080">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Phone Number</FONT>

</TD>

<TD ALIGN=CENTER BGCOLOR="#008080">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>City</FONT>

</TD>

<TD ALIGN=CENTER BGCOLOR="#008080">

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>State/Province</FONT>

</TD></TR>

<!--Display ADO Data from Customer Table-->

<TR>

<TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RSCustomerList("CompanyName")%>

</FONT></TD>

<TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RScustomerList("ContactLastName") & ", " %>

<%= RScustomerList("ContactFirstName") %>

</FONT></TD>

<TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RScustomerList("PhoneNumber")%>

</FONT></TD>

<TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RScustomerList("City")%>

</FONT></TD>

<TD BGCOLOR="f7efde" ALIGN=CENTER>

<FONT STYLE="ARIAL NARROW" SIZE=1>

<%= RScustomerList("StateOrProvince")%>

</FONT></TD>

</TR> </Table></FONT>

<HR>

<Input Type = Button Name = cmdDown Value = "< ">

<Input Type = Button Name = cmdUp Value = " >">

<BR>

<Input Type = Button Name = cmdFirst Value = "First Record">

<Input Type = Button Name = cmdLast Value = "Last Record">

<H5>Click Direction Arrows to Use MovePrevious or MoveNext

<BR> </H5>

<!-- Use Hidden Form Fields to send values to Server -->

<Form Method = Post Action="MoveOne.asp" Name=Form>

<Input Type="Hidden" Size="4" Name="MoveAmount" Value = 0>

<Input Type="Hidden" Size="4" Name="MoveLast" Value = 0>

<Input Type="Hidden" Size="4" Name="MoveFirst" Value = 0>

</Form></BODY>

<Script Language = "VBScript">

Sub cmdDown_OnClick

'Set Values in Form Input Boxes and Submit Form

Document.Form.MoveAmount.Value = -1

Document.Form.Submit

End Sub

Sub cmdUp_OnClick

Document.Form.MoveAmount.Value = 1

Document.Form.Submit

End Sub

Sub cmdFirst_OnClick

Document.Form.MoveFirst.Value = 2

Document.Form.Submit

End Sub

Sub cmdLast_OnClick

Document.Form.MoveLast.Value = 3

Document.Form.Submit

End Sub

</Script></HTML>

ADO
Recordset Object NextRecordset Method

---

Clears the current Recordset object
and returns the next recordset by advancing through a series of commands. This
method is not currently supported on UNIX.

NextRecordset Method Syntax

Set recordset2 = recordset1.NextRecordset( RecordsAffected )

NextRecordset Method Parameters

recordset2

Recordset containing results of command.

RecordsAffected

An optional Long variable to which the
provider returns the number of records that the current operation affected.

NextRecordset Method Return Values

Returns a Recordset object. In the
syntax model, recordset1 and recordset2 can be the same Recordset
object, or you can use separate objects.

NextRecordset Method Remarks

Use the NextRecordset method to
return the results of the next command in a compound command statement or of a
stored procedure that returns multiple results. If you open a Recordset
object based on a compound command statement (for example, “SELECT * FROM
table1;SELECT * FROM table2”) using the ADO
Command Object Execute Method on an ADO Command
Object or the ADO
Recordset Object Open Method on a recordset, ADO executes only the first
command and returns the results to recordset. To access the results of
subsequent commands in the statement, call the NextRecordset method.

As long as there are additional results,
the NextRecordset method will continue to return Recordset
objects. If a row-returning command returns no records, the returned Recordset
object will be empty; test for this case by verifying that the ADO
Recordset Object BOF, EOF Properties are both True. If a non
row-returning command executes successfully, the returned Recordset

object will be closed, which you can verify by testing the ADO
Recordset Object State Property on the recordset. When there are no more
results, recordset will be set to Nothing.

If an edit is in progress while in
immediate update mode, calling the NextRecordset method generates an
error; call the ADO
Recordset Object Update Method or the ADO
Recordset Object CancelUpdate Method first.

If you need to pass parameters for more
than one command in the compound statement by filling the ADO Parameters
Collection or by passing an array with the original Open or Execute
call, the parameters must be in the same order in the collection or array as
their respective commands in the command series. You must finish reading all
the results before reading output parameter values.

When you call the NextRecordset
method, ADO

executes only the next command in the statement. If you explicitly close the Recordset
object before stepping through the entire command statement, ADO never executes the remaining commands.

The NextRecordset method is not
available on a client-side (ADOR) Recordset object.

NextRecordset Method Example

This Visual Basic example uses the NextRecordset
method to view the data in a recordset that uses a compound command statement
made up of three separate SELECT statements.

Public Sub NextRecordsetX()

Dim rstCompound As ADODB.Recordset

Dim strCnn As String

Dim intCount As Integer

` Open compound recordset.

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

Set rstCompound = New ADODB.Recordset

rstCompound.Open "SELECT * FROM authors; " & _

"SELECT * FROM stores; " & _

"SELECT * FROM jobs", strCnn, , , adCmdText

` Display results from each SELECT statement.

intCount = 1

Do Until rstCompound Is Nothing

Debug.Print "Contents of recordset #" & intCount

Do While Not rstCompound.EOF

Debug.Print , rstCompound.Fields(0), _

rstCompound.Fields(1)

rstCompound.MoveNext

Loop

Set rstCompound = rstCompound.NextRecordset

intCount = intCount + 1

Loop

End Sub

ADO
Recordset Object Open Method

---

Opens a cursor.

Open Method Syntax

recordset.Open Source, ActiveConnection, CursorType, LockType, Options

Open Method Parameters

Source

An optional Variant that evaluates to a
valid Command object variable name, an SQL statement, a table name, or a
stored procedure call.

ActiveConnection

An optional Variant that evaluates to a
valid Connection object variable name, or a String containing ConnectionString

parameters.

CursorType

An optional CursorTypeEnum value
that determines the type of cursor that the provider should use when opening
the recordset. Can be one of the following constants (See the ADO
Recordset Object CursorType Property for definitions of these settings.):

Constant	Description
adOpenForwardOnly	Default. Opens a forward-only cursor.
adOpenKeyset	Opens a keyset cursor.
adOpenDynamic	Opens a dynamic cursor.
adOpenStatic	Opens a static cursor.

LockType

An optional LockTypeEnum value that
determines what type of locking (concurrency) the provider should use when
opening the recordset. Can be one of the following constants (See the LockType

property for more information.):

Constant	Description
adLockReadOnly	Default. Read-only; you cannot alter the data.
adLocPessimistic	Pessimistic locking, record by record. The provider does what is necessary to ensure successful editing of the records, usually by locking records at the data source immediately upon editing.
adLockOptimistic	Optimistic locking, record by record. The provider uses optimistic locking, locking records only when you call the Update method.
adLockBatchOptimistic	Optimistic batch updates. Required for batch update mode as opposed to immediate update mode.

Options

An optional Long value that
indicates how the provider should evaluate the Source argument if it
represents something other than a Command object. Can be one of the
following constants (See the CommandType property for a more detailed
explanation of these constants.):

Constant	Description
adCmdText	The provider should evaluate Source as a textual definition of a command.
adCmdTable	The provider should evaluate Source as a table name.
adCmdStoredProc	The provider should evaluate Source as a stored procedure.
adCmdUnknown	The type of command in the Source argument is not known.

See the ADO
Command Object CommandType Property for a more detailed explanation of the
four constants in this list.

Open Method Remarks

Using the Open method on a Recordset
object opens a cursor that represents records from a base table or the results
of a query.

Use the optional Source argument
to specify a data source using one of the following: an ADO Command
Object variable, an SQL statement, a stored procedure, or a table name.

The ActiveConnection argument
corresponds to the ActiveConnection property and specifies in which
connection to open the Recordset object. If you pass a connection
definition for this argument, ADO
opens a new connection using the specified parameters. You can change the value
of this property after opening the recordset to send updates to another
provider. Or, you can set this property to Nothing (in Microsoft Visual
Basic) to disconnect the recordset from any provider.

For the other arguments that correspond
directly to properties of a Recordset object (Source, CursorType,
and LockType), the relationship of the arguments to the properties is
as follows:

·
The property is read/write before the Recordset

object is opened.

·
The property settings are used unless you pass
the corresponding arguments when executing the Open method. If you pass
an argument, it overrides the corresponding property setting, and the property
setting is updated with the argument value.

·
After you open the Recordset object,
these properties become read-only.

Note	For Recordset objects whose ADO Recordset Object Source Property is set to a valid Command object, the ActiveConnection property is read-only, even if the Recordset object isn’t open.

If you pass a Command object in the
Source argument and also pass an ActiveConnection argument,
an error occurs. The ActiveConnection property of the Command

object must already be set to a valid ADO Connection
Object or connection string.

If you pass something other than a Command
object in the Source argument, you can use the Options
argument to optimize evaluation of the Source argument. If the Options

argument is not defined, you may experience diminished performance because ADO must make calls to
the provider to determine if the argument is an SQL statement, a stored
procedure, or a table name. If you know what Source type you’re using,
setting the Options argument instructs ADO to jump directly to the relevant code. If
the Options argument does not match the Source type, an error
occurs.

If the data source returns no records, the
provider sets both the ADO
Recordset Object BOF, EOF Properties to True, and the current
record position is undefined. You can still add new data to this empty Recordset
object if the cursor type allows it.

When you have concluded your operations
over an open Recordset object, use the ADO
Recordset Object Close Method to free any associated system resources.
Closing an object does not remove it from memory; you may change its property
settings and use the Open method to open it again later. To completely eliminate
an object from memory, set the object variable to Nothing.

Open Method Examples

See the ADO
Recordset Object Close Method.

ADO
Recordset Object Requery Method

---

Updates the data in a Recordset
object by re-executing the query on which the object is based.

Requery Method Syntax

recordset.Requery

Requery Method Remarks

Use the Requery method to refresh
the entire contents of a Recordset object from the data source by
reissuing the original command and retrieving the data a second time. Calling
this method is equivalent to calling the ADO
Recordset Object Close Method and ADO
Recordset Object Open Method methods in succession. If you are
editing the current record or adding a new record, an error occurs.

While the Recordset object is open,
the properties that define the nature of the cursor (ADO
Recordset Object CursorType Property, ADO
Recordset Object LockType Property, ADO
Recordset Object MaxRecords Property, and so forth) are read-only. Thus,
the Requery method can only refresh the current cursor. To change any of
the cursor properties and view the results, you must use the Close

method so that the properties become read/write again. You can then change the
property settings and call the Open method to reopen the cursor.

Requery Method Example

See the command ADO
Command Object Execute Method.

ADO
Recordset Object Resync Method

---

Refreshes the data in the current Recordset
object from the underlying database.

Resync Method Syntax

recordset.Resync AffectRecords

Resync Method Parameters

AffectRecords

An optional AffectEnum constant
that determines how many records the Resync method will affect. Can be
one of the following constants:

Constant	Description
adAffectCurrent	Refresh only the current record.
adAffectGroup	Refresh the records that satisfy the current Filter property setting. You must set the Filter property to one of the valid predefined constants in order to use this option. The Filter property is not currently supported on UNIX.
adAffectAll	Default. Refresh all the records in the Recordset object, including any hidden by the current Filter property setting.

Resync Method Remarks

Use the Resync method to
re-synchronize records in the current recordset with the underlying database.
This is useful if you are using either a static or forward-only cursor but you
want to see any changes in the underlying database. Calling the Resync
method cancels any pending batch updates.

Unlike the ADO
Recordset Object Requery Method, the Resync method does not
re-execute the Recordset object’s underlying command; new records in the
underlying database will not be visible.

If the attempt to resynchronize fails
because of a conflict with the underlying data (for example, a record has been
deleted by another user), the provider returns warnings to the ADO Errors
Collection, but does not halt program execution. A run-time error occurs
only if there are conflicts on all the requested records. Use the ADO
Recordset Object Filter Property (adFilterAffectedRecords) and the ADO
Recordset Object Status Property to locate records with conflicts.

Resync Method Examples

This Visual Basic example demonstrates
using the Resync method to refresh data in a static recordset.

Public Sub ResyncX()

Dim strCnn As String

Dim rstTitles As ADODB.Recordset

' Open connections.

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

' Open recordset for titles table.

Set rstTitles = New ADODB.Recordset

rstTitles.CursorType = adOpenStatic

rstTitles.LockType = adLockBatchOptimistic

rstTitles.Open "titles", strCnn, , , adCmdTable

' Change the type of the first title in the recordset.

rstTitles!Type = "database"

' Display the results of the change.

MsgBox "Before resync: " & vbCr & vbCr & _

"Title - " & rstTitles!Title & vbCr & _

"Type - " & rstTitles!Type

' Resync with database and redisplay results.

rstTitles.Resync

MsgBox "After resync: " & vbCr & vbCr & _

"Title - " & rstTitles!Title & vbCr & _

"Type - " & rstTitles!Type

rstTitles.CancelBatch

rstTitles.Close

End Sub

ADO
Recordset Object Supports Method

---

Determines whether a specified Recordset
object supports a particular type of functionality.

Supports Method Syntax

boolean = recordset.Supports( CursorOptions )

Supports Method Parameters

CursorOptions

A Long expression that consists of
one or more of the following CursorOptionEnum values:

Value	Description
adAddNew	The AddNew method adds new records.
adApproxPosition	You can read and set the AbsolutePosition and AbsolutePage properties.
adBookmark	The Bookmark property accesses specific records.
adDelete	The Delete method deletes records.
adHoldRecords	You can retrieve more records or change the next retrieve position without committing all pending changes.
adMovePrevious	The MoveFirst, MovePrevious, Move, and GetRows methods move the current position backward without requiring bookmarks.
adResync	The Resync method modifies existing data.
adUpdate	The Update method modifies existing data.
adUpdateBatch	The UpdateBatch and CancelBatch methods transmit changes to the provider in groups.

Supports Method Remarks

Use the Supports method to
determine what types of functionality a Recordset object supports. If
the Recordset object supports the features whose corresponding constants
are in CursorOptions, the Supports method returns True.
Otherwise, it returns False.

Note

Although the Supports method may return True for a given functionality, it does not guarantee that the provider can make the feature available under all circumstances. The Supports method simply returns whether or not the provider can support the specified functionality assuming certain conditions are met. For example, the Supports method may indicate that a Recordset object supports updates even though the cursor is based on a multi-table join, some columns of which are not updatable.

Supports Method Examples

This Visual Basic example uses the Supports
method to display the options supported by a recordset opened with different
cursor types. The DisplaySupport function is required for this procedure
to run.

Public Sub SupportsX()

Dim aintCursorType(4) As Integer

Dim rstTitles As ADODB.Recordset

Dim strCnn As String

Dim intIndex As Integer

` Open connections.

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

` Fill array with CursorType constants.

aintCursorType(0) = adOpenForwardOnly

aintCursorType(1) = adOpenKeyset

aintCursorType(2) = adOpenDynamic

aintCursorType(3) = adOpenStatic

` Open recordset using each CursorType and

` optimitic locking. Then call the DisplaySupport

` procedure to display the supported options.

For intIndex = 0 To 3

Set rstTitles = New ADODB.Recordset

rstTitles.CursorType = aintCursorType(intIndex)

rstTitles.LockType = adLockOptimistic

rstTitles.Open "titles", strCnn, , , adCmdTable

Select Case aintCursorType(intIndex)

Case adOpenForwardOnly

Debug.Print "ForwardOnly cursor supports:"

Case adOpenKeyset

Debug.Print "Keyset cursor supports:"

Case adOpenDynamic

Debug.Print "Dynamic cursor supports:"

Case adOpenStatic

Debug.Print "Static cursor supports:"

End Select

DisplaySupport rstTitles

rstTitles.Close

Next intIndex

End Sub

Public Sub DisplaySupport(rstTemp As ADODB.Recordset)

Dim alngConstants(9) As Long

Dim booSupports As Boolean

Dim intIndex As Integer

' Fill array with cursor option constants.

alngConstants(0) = adAddNew

alngConstants(1) = adApproxPosition

alngConstants(2) = adBookmark

alngConstants(3) = adDelete

alngConstants(4) = adHoldRecords

alngConstants(5) = adMovePrevious

alngConstants(6) = adResync

alngConstants(7) = adUpdate

alngConstants(8) = adUpdateBatch

For intIndex = 0 To 8

booSupports = _

rstTemp.Supports(alngConstants(intIndex))

If booSupports Then

Select Case alngConstants(intIndex)

Case adAddNew

Debug.Print " AddNew"

Case adApproxPosition

Debug.Print " AbsolutePosition and AbsolutePage"

Case adBookmark

Debug.Print " Bookmark"

Case adDelete

Debug.Print " Delete"

Case adHoldRecords

Debug.Print " holding records"

Case adMovePrevious

Debug.Print " MovePrevious and Move"

Case adResync

Debug.Print " resyncing data"

Case adUpdate

Debug.Print " Update"

Case adUpdateBatch

Debug.Print " batch updating"

End Select

End If

Next intIndex

End Sub

ADO
Recordset Object Update Method

---

Saves any changes you make to the current
record of a Recordset object.

Note	This method is not available for some databases and ODBC drivers.

Update Method Syntax

recordset.Update Fields, Values

Update Method Parameters

Fields

An optional Variant representing a single
name or a Variant array representing names or ordinal positions of the field or
fields you wish to modify.

Values

An optional Variant representing a single
value or a Variant array representing values for the field or fields in the new
record.

Update Method Remarks

Use the Update method to save any
changes you make to the current record of a Recordset object since
calling the ADO
Recordset Object AddNew Method or since changing any field values in an
existing record. The Recordset object must support updates.

To set field values, do one of the
following:

·
Assign values to a ADO Field Object
object’s ADO
Field Object Value Property and call the ADO
Recordset Object Update Method.

·
Pass a field name and a value as arguments with
the Update call.

·
Pass an array of field names and an array of
values with the Update call.

When you use arrays of fields and values,
there must be an equal number of elements in both arrays. Also, the order of
field names must match the order of field values. If the number and order of
fields and values do not match, an error occurs.

If the Recordset object supports
batch updating, then you can cache multiple changes to one or more records
locally until you call the ADO
Recordset Object UpdateBatch Method. If you are editing the current record
or adding a new record when you call the UpdateBatch method, ADO will automatically
call the Update method to save any pending changes to the current record
before transmitting the batched changes to the provider. Batch updating is
not currently supported on UNIX.

If you move from the record you are adding
or editing before calling the Update method, ADO will automatically call Update to
save the changes. You must call the ADO
Recordset Object CancelUpdate Method if you want to cancel any changes made
to the current record or to discard a newly added record.

The current record remains current after
you call the Update method.

Update Method Examples

The following Visual Basic examples show
how to use the Update method.

The first example demonstrates using the Update
method in conjunction with CancelUpdate method.

Public Sub UpdateX()

Dim rstEmployees As ADODB.Recordset

Dim strOldFirst As String

Dim strOldLast As String

Dim strMessage As String

` Open recordset with names from Employee table.

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

Set rstEmployees = New ADODB.Recordset

rstEmployees.CursorType = adOpenKeyset

rstEmployees.LockType = adLockOptimistic

rstEmployees.Open "SELECT fname, lname " & _

"FROM Employee ORDER BY lname", strCnn, , , adCmdText

` Store original data.

strOldFirst = rstEmployees!fname

strOldLast = rstEmployees!lname

` Change data in edit buffer.

rstEmployees!fname = "Linda"

rstEmployees!lname = "Kobara"

` Show contents of buffer and get user input.

strMessage = "Edit in progress:" & vbCr & _

" Original data = " & strOldFirst & " " & _

strOldLast & vbCr & " Data in buffer = " & _

rstEmployees!fname & " " & rstEmployees!lname & vbCr & vbCr & _

"Use Update to replace the original data with " & _

"the buffered data in the Recordset?"

If MsgBox(strMessage, vbYesNo) = vbYes Then

rstEmployees.Update

Else

rstEmployees.CancelUpdate

End If

` Show the resulting data.

MsgBox "Data in recordset = " & rstEmployees!fname & " " & _

rstEmployees!lname

` Restore original data because this is a demonstration.

If Not (strOldFirst = rstEmployees!fname And _

strOldLast = rstEmployees!lname) Then

rstEmployees!fname = strOldFirst

rstEmployees!lname = strOldLast

rstEmployees.Update

End If

rstEmployees.Close

End Sub

The following example demonstrates using
the Update method in conjunction with the AddNew method:

Public Sub UpdateX2()

Dim cnn1 As ADODB.Connection

Dim rstEmployees As ADODB.Recordset

Dim strEmpID As String

Dim strOldFirst As String

Dim strOldLast As String

Dim strMessage As String

' Open a connection.

Set cnn1 = New ADODB.Connection

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

cnn1.Open strCnn

' Open recordset with data from Employee table.

Set rstEmployees = New ADODB.Recordset

rstEmployees.CursorType = adOpenKeyset

rstEmployees.LockType = adLockOptimistic

rstEmployees.Open "employee", cnn1, , , adCmdTable

rstEmployees.AddNew

strEmpID = "B-S55555M"

rstEmployees!emp_id = strEmpID

rstEmployees!fname = "Bill"

rstEmployees!lname = "Sornsin"

' Show contents of buffer and get user input.

strMessage = "AddNew in progress:" & vbCr & _

"Data in buffer = " & rstEmployees!emp_id & ", " & _

rstEmployees!fname & " " & rstEmployees!lname & vbCr & vbCr & _

"Use Update to save buffer to recordset?"

If MsgBox(strMessage, vbYesNoCancel) = vbYes Then

rstEmployees.Update

` Go to the new record and show the resulting data.

MsgBox "Data in recordset = " & rstEmployees!emp_id & ", " & _

rstEmployees!fname & " " & rstEmployees!lname

Else

rstEmployees.CancelUpdate

MsgBox "No new record added."

End If

' Delete new data because this is a demonstration.

cnn1.Execute "DELETE FROM employee WHERE emp_id = '" & strEmpID & "'"

rstEmployees.Close

End Sub

ADO
Recordset Object UpdateBatch Method

---

Writes all pending batch updates to disk. This
method is not currently supported on UNIX.

UpdateBatch Method Syntax

recordset.UpdateBatch AffectRecords

UpdateBatch Method Parameters

AffectRecords

An optional AffectEnum value that
determines how many records the UpdateBatch method will affect. Can be
one of the following constants:

Constant	Description
AdAffectCurrent	Write pending changes only for the current record.
AdAffectGroup	Write pending changes for the records that satisfy the current Filter property setting. You must set the Filter property to one of the valid predefined constants in order to use this option.
adAffectAll	Default. Write pending changes for all the records in the Recordset object, including any hidden by the current Filter property setting.

UpdateBatch Method Remarks

---

Use the UpdateBatch method when
modifying a Recordset object in batch update mode to transmit all
changes made in a Recordset object to the underlying database.

If the Recordset object supports
batch updating, then you can cache multiple changes to one or more records
locally until you call the UpdateBatch method. If you are editing the
current record or adding a new record when you call the UpdateBatch

method, ADO
will automatically call the ADO
Recordset Object Update Method to save any pending changes to the current
record before transmitting the batched changes to the provider.

Note	You should use batch updating only with either a keyset or static cursor.

If the attempt to transmit changes fails
because of a conflict with the underlying data (for example, a record has
already been deleted by another user), the provider returns warnings to the ADO Errors
Collection but does not halt program execution. A run-time error occurs
only if there are conflicts on all the requested records. Use the ADO
Recordset Object Filter Property (adFilterAffectedRecords) and the ADO
Recordset Object Status Property to locate records with conflicts.

To cancel all pending batch updates, use
the ADO
Recordset Object CancelBatch Method.

UpdateBatch Method Example

See the ADO
Recordset Object CancelBatch Method example.

ADORecordset Object Properties

---

Property	Description
AbsolutePage	The page in which the current record resides.
AbsolutePosition	The ordinal position of a Recordset object’s current position.
ActiveConnection	The Connection object to which the Recordset object currently belongs.
BOF, EOF	If BOF is True, the current record position is before the first record in a Recordset object. If EOF is True, the record position is after the last record in a Recordset object.
Bookmark	A value that uniquely identifies the current record in a Recordset object. Setting the Bookmark property to a valid bookmark changes the current record.
CacheSize	The number of records from a Recordset object that are cached locally in memory. This property is not currently supported on UNIX.
CursorLocation	The location of the cursor engine.
CursorType	The type of cursor used in a Recordset object.
EditMode	The editing status of the current record.
Filter	A filter for data in a Recordset object.
LockType	The type of locks placed on records during editing.
MarshalOptions	Which records are to be marshaled back to the server.
MaxRecords	The maximum number of records to return to a Recordset object from a query.
PageCount	The number of pages of data the Recordset object contains.
PageSize	The number of records that make up one page in the Recordset object.
RecordCount	The current number of records in a Recordset object.
Source	The source for the data in a Recordset object.
State	Describes the current state of the Recordset object.
Status	The status of the current record with respect to batch updates or other bulk operations.

In this section:

ADO Recordset
Object AbsolutePage Property

ADO Recordset
Object AbsolutePosition Property

ADO Recordset
Object ActiveConnection Property

ADO Recordset
Object BOF, EOF Properties

ADO Recordset
Object Bookmark Property

ADO Recordset
Object CacheSize Property

ADO Recordset
Object CursorLocation Property

ADO Recordset
Object CursorType Property

ADO Recordset
Object EditMode Property

ADO Recordset
Object Filter Property

ADO Recordset
Object LockType Property

ADO Recordset
Object MarshalOptions Property

ADO Recordset
Object MaxRecords Property

ADO Recordset
Object PageCount Property

ADO Recordset
Object PageSize Property

ADO Recordset
Object State Property

ADO Recordset
Object Status Property

ADO Recordset
Object Source Property

ADO Recordset
Object RecordCount Property

ADO
Recordset Object AbsolutePage Property

---

Specifies in which page the current record
resides.

AbsolutePage Property Return Values

Sets or returns a Long value from 1
to the number of pages in the Recordset object (PageCount), or
returns one of the following constants:

Constant	Description
adPosUnknown	The recordset is empty, the current position is unknown, or the provider does not support the AbsolutePage property
adPosBOF	The current record pointer is at BOF (that is, the BOF property is True).
adPosEOF	The current record pointer is at EOF (that is, the EOF property is True).

AbsolutePage Property Remarks

Use the AbsolutePage property to
identify the page number on which the current record is located. Use the ADO
Recordset Object PageSize Property to logically divide the Recordset

object into a series of pages, each of which has the number of records equal to
PageSize (except for the last page, which may have fewer records). The
provider must support the appropriate functionality for this property to be
available.

Like the AbsolutePosition property,
AbsolutePage is 1-based and equals 1 when the current record is the
first record in the recordset. Set this property to move to the first record of
a particular page. Obtain the total number of pages from the ADO
Recordset Object PageCount Property.

AbsolutePage Property Example

This Visual Basic example uses the AbsolutePage,
PageCount, and PageSize properties to display names and hire
dates from the Employees table five records at a time.

Public Sub AbsolutePageX()

Dim rstEmployees As ADODB.Recordset

Dim strCnn As String

Dim strMessage As String

Dim intPage As Integer

Dim intPageCount As Integer

Dim intRecord As Integer

` Open a recordset using a client cursor

` for the employee table.

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

Set rstEmployees = New ADODB.Recordset

` Use client cursor to enable AbsolutePosition property.

rstEmployees.CursorLocation = adUseClient

rstEmployees.Open "employee", strCnn, , , adCmdTable

` Display names and hire dates, five records

` at a time.

rstEmployees.PageSize = 5

intPageCount = rstEmployees.PageCount

For intPage = 1 To intPageCount

rstEmployees.AbsolutePage = intPage

strMessage = ""

For intRecord = 1 To rstEmployees.PageSize

strMessage = strMessage & _

rstEmployees!fname & " " & _

rstEmployees!lname & " " & _

rstEmployees!hire_date & vbCr

rstEmployees.MoveNext

If rstEmployees.EOF Then Exit For

Next intRecord

MsgBox strMessage

Next intPage

rstEmployees.Close

End Sub

ADO
Recordset Object AbsolutePosition Property

---

Specifies the ordinal position of a Recordset
object’s current record.

AbsolutePosition Property Return Values

Sets or returns a Long value from 1
to the number of records in the Recordset object (RecordCount),
or returns one of the following constants:

Constant	Description
adPosUnknown	The recordset is empty, the current position is unknown, or the provider does not support the AbsolutePosition property.
adPosBOF	The current record pointer is at BOF (that is, the BOF property is True).
adPosEOF	The current record pointer is at EOF (that is, the EOF property is True).

AbsolutePosition Property Remarks

Use the AbsolutePosition property
to move to a record based on its ordinal position in the Recordset

object, or to determine the ordinal position of the current record. The
provider must support the appropriate functionality for this property to be
available.

Like the AbsolutePage property, AbsolutePosition
is 1-based and equals 1 when the current record is the first record in the
recordset. You can obtain the total number of records in the Recordset
object from the RecordCount property.

When you set the AbsolutePosition

property, even if it is to a record in the current cache, ADO reloads the cache with a new group of
records starting with the record you specified. The ADO
Recordset Object CacheSize Property determines the size of this group.

Note

You should not use the AbsolutePosition property as a surrogate record number. The position of a given record changes when you delete a preceding record. There is also no assurance that a given record will have the same AbsolutePosition if the Recordset object is requeried or reopened. Bookmarks are still the recommended way of retaining and returning to a given position, and are the only way of positioning across all types of Recordset objects.

AbsolutePosition Property Example

This Visual Basic example demonstrates how
the AbsolutePosition property can track the progress of a loop that
enumerates all the records of a recordset. It uses the CursorLocation property
to enable the AbsolutePosition property by setting the cursor to a
client cursor.

Public Sub AbsolutePositionX()

Dim rstEmployees As ADODB.Recordset

Dim strCnn As String

Dim strMessage As String

' Open a recordset for the Employee table

' using a client cursor.

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

Set rstEmployees = New ADODB.Recordset

' Use client cursor to enable AbsolutePosition property.

rstEmployees.CursorLocation = adUseClient

rstEmployees.Open "employee", strCnn, , , adCmdTable

' Enumerate Recordset.

Do While Not rstEmployees.EOF

' Display current record information.

strMessage = "Employee: " & rstEmployees!lName & vbCr & _

"(record " & rstEmployees.AbsolutePosition & _

" of " & rstEmployees.RecordCount & ")"

If MsgBox(strMessage, vbOKCancel) = vbCancel _

Then Exit Do

rstEmployees.MoveNext

Loop

rstEmployees.Close

End Sub

ADO
Recordset Object ActiveConnection Property

---

Specifies to which Connection
object the Recordset object currently belongs.

ActiveConnection Property Return Values (ADO Recordset Object)

Sets or returns a String containing
the definition for a connection or an ADO Connection
Object. Default is a Null object reference.

ActiveConnection Property Remarks (ADO Recordset Object)

Use the ActiveConnection property
to determine the Connection object over which the specified Command
object will execute or the specified recordset will be opened.

For open Recordset objects or for Recordset
objects whose ADO
Recordset Object Source Property is set to a valid ADO Command
Object, the ActiveConnection property is read-only. Otherwise, it is
read/write.

You can set this property to a valid Connection
object or to a valid connection string. In this case, the provider creates a
new Connection object using this definition and opens the connection.
Additionally, the provider may set this property to the new Connection
object to give you a way to access the Connection object for extended
error information or to execute other commands.

If you use the ActiveConnection

argument of the ADO
Recordset Object Open Method to open a Recordset object, the ActiveConnection
property will inherit the value of the argument.

If you set the Source property of
the Recordset object to a valid Command object variable, the ActiveConnection

property of the recordset inherits the setting of the Command object’s ActiveConnection
property.

ActiveConnection Property Example (ADO Recordset Object)

This Visual Basic example uses the ActiveConnection,

ADO
Command Object CommandText Property, CommandTimeout, ADO
Command Object CommandType Property, ADO
Parameter Object Size Property, and ADO
Parameter Object Direction Property properties to execute a stored
procedure:

Public Sub ActiveConnectionX()

Dim cnn1 As ADODB.Connection

Dim cmdByRoyalty As ADODB.Command

Dim prmByRoyalty As ADODB.Parameter

Dim rstByRoyalty As ADODB.Recordset

Dim rstAuthors As ADODB.Recordset

Dim intRoyalty As Integer

Dim strAuthorID As String

Dim strCnn As String

` Define a command object for a stored procedure.

Set cnn1 = New ADODB.Connection

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

cnn1.Open strCnn

Set cmdByRoyalty = New ADODB.Command

Set cmdByRoyalty.ActiveConnection = cnn1

cmdByRoyalty.CommandText = "byroyalty"

cmdByRoyalty.CommandType = adCmdStoredProc

cmdByRoyalty.CommandTimeout = 15

` Define the stored procedure's input parameter.

intRoyalty = Trim(InputBox( _

"Enter royalty:"))

Set prmByRoyalty = New ADODB.Parameter

prmByRoyalty.Type = adInteger

prmByRoyalty.Size = 3

prmByRoyalty.Direction = adParamInput

prmByRoyalty.Value = intRoyalty

cmdByRoyalty.Parameters.Append prmByRoyalty

` Create a recordset by executing the command.

Set rstByRoyalty = cmdByRoyalty.Execute()

` Open the Authors table to get author names for display.

Set rstAuthors = New ADODB.Recordset

rstAuthors.Open "authors", strCnn, , , adCmdTable

` Print current data in the recordset, adding

` author names from Authors table.

Debug.Print "Authors with " & intRoyalty & _

" percent royalty"

Do While Not rstByRoyalty.EOF

strAuthorID = rstByRoyalty!au_id

Debug.Print , rstByRoyalty!au_id & ", ";

rstAuthors.Filter = "au_id = '" & strAuthorID & "'"

Debug.Print rstAuthors!au_fname & " " & _

rstAuthors!au_lname

rstByRoyalty.MoveNext

Loop

rstByRoyalty.Close

rstAuthors.Close

cnn1.Close

End Sub

ADO
Recordset Object BOF, EOF Properties

---

BOF indicates that the current
record position is before the first record in a Recordset object.

EOF indicates that the current
record position is after the last record in a Recordset object.

BOF, EOF Properties Return Values

The BOF and EOF properties
return Boolean values.

BOF, EOF Properties Remarks

Use the BOF and EOF
properties to determine whether a Recordset object contains records or
whether you’ve gone beyond the limits of a Recordset object when you
move from record to record.

The BOF property returns True
(-1) if the current record position is before the first record and False
(0) if the current record position is on or after the first record.

The EOF property returns True

if the current record position is after the last record and False if the
current record position is on or before the last record.

If either the BOF or EOF
property is True, there is no current record.

If you open a Recordset object
containing no records, the BOF and EOF properties are set to True,
and the Recordset object’s RecordCount property setting is zero.
When you open a Recordset object that contains at least one record, the
first record is the current record and the BOF and EOF properties
are False.

If you delete the last remaining record in
the Recordset object, the BOF and EOF properties may
remain False until you attempt to reposition the current record.

This table shows which ADO
Recordset Object Move Method methods are allowed with different
combinations of the BOF and EOF properties:

	MoveFirst MoveLast	Move Previous Move < 0	Move 0	Move Next Move > 0
BOF = True, EOF = False	Allowed	Error	Error	Allowed
BOF=False EOF=True	Allowed	Allowed	Error	Error
Both True	Error	Error	Error	Error
Both False	Allowed	Allowed	Allowed	Allowed

Allowing a Move method doesn’t
guarantee that the method will successfully locate a record; it only means that
calling the specified Move method won’t generate an error.

The following table shows what happens to
the BOF and EOF property settings when you call various Move

methods but are unable to successfully locate a record.

	BOF	EOF
MoveFirst, MoveLast	Set to True	Set to True
Move 0	No change	No change
MovePrevious, Move < 0	Set to True	No change
MoveNext, Move > 0	No change	Set to True

BOF, EOF Properties Example

This Visual Basic example uses the BOF
and EOF properties to display a message if a user tries to move past the
first or last record of a recordset. It uses the ADO
Recordset Object Bookmark Property to let the user flag a record in a
recordset and return to it later.

Public Sub BOFX()

Dim rstPublishers As ADODB.Recordset

Dim strCnn As String

Dim strMessage As String

Dim intCommand As Integer

Dim varBookmark As Variant

` Open recordset with data from Publishers table.

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

Set rstPublishers = New ADODB.Recordset

rstPublishers.CursorType = adOpenStatic

` Use client cursor to enable AbsolutePosition property.

rstPublishers.CursorLocation = adUseClient

rstPublishers.Open "SELECT pub_id, pub_name FROM publishers " & _

"ORDER BY pub_name", strCnn, , , adCmdText

rstPublishers.MoveFirst

Do While True

` Display information about current record

` and get user input.

strMessage = "Publisher: " & rstPublishers!pub_name & _

vbCr & "(record " & rstPublishers.AbsolutePosition & _

" of " & rstPublishers.RecordCount & ")" & vbCr & vbCr & _

"Enter command:" & vbCr & _

"[1 - next / 2 - previous /" & vbCr & _

"3 - set bookmark / 4 - go to bookmark]"

intCommand = Val(InputBox(strMessage))

Select Case intCommand

` Move forward or backward, trapping for BOF

` or EOF.

Case 1

rstPublishers.MoveNext

If rstPublishers.EOF Then

MsgBox "Moving past the last record." & _

vbCr & "Try again."

rstPublishers.MoveLast

End If

Case 2

rstPublishers.MovePrevious

If rstPublishers.BOF Then

MsgBox "Moving past the first record." &

_vbCr & "Try again."

rstPublishers.MoveFirst

End If

` Store the bookmark of the current record.

Case 3

varBookmark = rstPublishers.Bookmark

` Go to the record indicated by the stored

` bookmark.

Case 4

If IsEmpty(varBookmark) Then

MsgBox "No Bookmark set!"

Else

rstPublishers.Bookmark = varBookmark

End If

Case Else

Exit Do

End Select

Loop

rstPublishers.Close

End Sub

ADO
Recordset Object Bookmark Property

---

Returns a bookmark that uniquely identifies
the current record in a Recordset object or sets the current record in a
Recordset object to the record identified by a valid bookmark.

Bookmark Property Return Values

Sets or returns a Variant expression that
evaluates to a valid bookmark.

Bookmark Property Remarks

Use the Bookmark property to save
the position of the current record and return to that record at any time.
Bookmarks are available only in Recordset objects that support bookmark
functionality.

When you open a Recordset object,
each of its records has a unique bookmark. To save the bookmark for the current
record, assign the value of the Bookmark property to a variable. To
quickly return to that record at any time after moving to a different record,
set the Recordset object’s Bookmark property to the value of that
variable.

The user may not be able to view the value
of the bookmark. Also, users should not expect bookmarks to be directly
comparable—two bookmarks that refer to the same record may have different
values.

If you use the ADO
Recordset Object Clone Method to create a copy of a Recordset
object, the Bookmark property settings for the original and the
duplicate Recordset objects are identical and you can use them
interchangeably. However, you can’t use bookmarks from different Recordset
objects interchangeably, even if they were created from the same source or
command.

Bookmark Property Examples

See the ADO
Recordset Object BOF, EOF Properties.

ADO
Recordset Object CacheSize Property

---

The number of records from a Recordset
object that are cached locally in memory. This property is not currently
supported on UNIX.

CacheSize Property Return Values

Sets or returns a Long value that
must be greater than 0. Default is 1.

CacheSize Property Remarks

Use the CacheSize property to
control how many records the provider keeps in its buffer and how many to
retrieve at one time into local memory. For example, if the CacheSize is
10, after first opening the Recordset object, the provider retrieves the
first 10 records into local memory. As you move through the Recordset
object, the provider returns the data from the local memory buffer. As soon as
you move past the last record in the cache, the provider retrieves the next 10
records from the data source into the cache.

The value of this property can be adjusted
during the life of the Recordset object, but changing this value only
affects the number of records in the cache after subsequent retrievals from the
data source. Changing the property value alone will not change the current
contents of the cache.

If there are fewer records to retrieve
than CacheSize specifies, the provider returns the remaining records; no
error occurs.

A CacheSize setting of zero is not
allowed and returns an error.

Records retrieved from the cache don’t
reflect concurrent changes that other users made to the source data. To force
an update of all the cached data, use the ADO
Recordset Object Resync Method.

CacheSize Property Example

This Visual Basic example uses the CacheSize
property to show the difference in performance for an operation performed with
and without a 30-record cache.

Public Sub CacheSizeX()

Dim rstRoySched As ADODB.Recordset

Dim strCnn As String

Dim sngStart As Single

Dim sngEnd As Single

Dim sngNoCache As Single

Dim sngCache As Single

Dim intLoop As Integer

Dim strTemp As String

` Open the RoySched table.

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

Set rstRoySched = New ADODB.Recordset

rstRoySched.Open "roysched", strCnn, , , adCmdTable

` Enumerate the Recordset object twice and record

` the elapsed time.

sngStart = Timer

For intLoop = 1 To 2

rstRoySched.MoveFirst

Do While Not rstRoySched.EOF

' Execute a simple operation for the performance test.

strTemp = rstRoySched!title_id

rstRoySched.MoveNext

Loop

Next intLoop

sngEnd = Timer

sngNoCache = sngEnd - sngStart

' Cache records in groups of 30 records.

rstRoySched.MoveFirst

rstRoySched.CacheSize = 30

sngStart = Timer

` Enumerate the Recordset object twice and record

' the elapsed time.

For intLoop = 1 To 2

rstRoySched.MoveFirst

Do While Not rstRoySched.EOF

` Execute a simple operation for the

` performance test.

strTemp = rstRoySched!title_id

rstRoySched.MoveNext

Loop

Next intLoop

sngEnd = Timer

sngCache = sngEnd - sngStart

' Display performance results.

MsgBox "Caching Performance Results:" & vbCr & _

" No cache: " & Format(sngNoCache, _

"##0.000") & " seconds" & vbCr & _

" 30-record cache: " & Format(sngCache, _

"##0.000") & " seconds"

rstRoySched.Close

End Sub

ADO
Recordset Object CursorLocation Property

---

Sets or returns the location of the cursor
engine. This property is read-only on UNIX.

CursorLocation Property Return Values

Sets or returns a Long value that
can be set to one of the following constants:

Constant	Description
adUseClient	Uses client-side cursors supplied by a local cursor library. Local cursor engines will often allow many features that driver-supplied cursors may not, so using this setting may provide an advantage with respect to features that will be enabled. For backward-compatibility, the synonym adUseClientBatch is also supported.
adUseServer	Default. Uses data-provider or driver-supplied cursors. These cursors are sometimes very flexible and allow for some additional sensitivity to reflecting changes that others make to the actual data source. However, some features of the Microsoft Client Cursor Provider (such as disassociated recordsets) cannot be simulated.

CursorLocation Property Remarks

This property allows you to choose between
various cursor libraries accessible to the provider. Usually, you can choose
between using a client-side cursor library or one that is located on the
server.

This property setting only affects
connections established after the property has been set. Changing the CursorLocation
property has no effect on existing connections.

This property is read/write on a closed
recordset, and read-only on an open recordset.

CursorLocation Property Example

See the AbsolutePosition property
example.

ADO
Recordset Object CursorType Property

---

The type of cursor used in a Recordset
object.

CursorType Property Return Values

Sets or returns one of the following CursorTypeEnum
values:

Constant	Description
adOpenForwardOnly	Forward-only cursor. Default. Identical to a static cursor except that you can only scroll forward through records. This improves performance in situations when you only need to make a single pass through a recordset.
adOpenKeyset	Keyset cursor. Like a dynamic cursor, except that you can’t see records that other users add, although records that other users delete are inaccessible from your recordset. Data changes by other users are still visible.
adOpenDynamic	Dynamic cursor. Additions, changes, and deletions by other users are visible, and all types of movement through the recordset are allowed, except for bookmarks if the provider doesn’t support them.
adOpenStatic	Static cursor. A static copy of a set of records that you can use to find data or generate reports. Additions, changes, or deletions by other users are not visible.

CursorType Property Remarks

Use the CursorType property to
specify the type of cursor that should be used when opening the Recordset

object. The CursorType property is read/write when the recordset is
closed and read-only when it is open.

If a provider does not support the
requested cursor type, the provider may return another cursor type. The CursorType
property will change to match the actual cursor type in use when the recordset
object is open. To verify specific functionality of the returned cursor, use
the ADO
Recordset Object Supports Method. After you close the recordset, the CursorType
property reverts to its original setting.

The following chart shows the provider
functionality (identified by Supports method constants) required for
each cursor type.

Cursor Type	The Supports method must return True for these constants
adOpenForwardOnly	none
adOpenKeyset	adBookmark, adHoldRecords, adMovePrevious, adResync
adOpenDynamic	adMovePrevious
adOpenStatic	adBookmark, adHoldRecords, adMovePrevious, adResync

Note

Although Supports(adUpdateBatch) may be true for dynamic and forward-only cursors, for batch updates you should use either a keyset or static cursor. Set the ADO Recordset Object LockType Property to adLockBatchOptimistic, and set the CursorLocation property to adUseClient (or its synonym, adUseClientBatch) to enable the Microsoft Client Cursor Engine, which is required for batch updates.

CursorType Property Example

This Visual Basic example demonstrates
setting the CursorType and LockType properties before opening a
recordset. It also shows the value of the ADO
Recordset Object EditMode Property under various conditions. The EditModeOutput

function is required for this procedure to run.

Public Sub EditModeX()

Dim cnn1 As ADODB.Connection

Dim rstEmployees As ADODB.Recordset

Dim strCnn As String

` Open recordset with data from Employee table.

Set cnn1 = New ADODB.Connection

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

cnn1.Open strCnn

Set rstEmployees = New ADODB.Recordset

Set rstEmployees.ActiveConnection = cnn1

rstEmployees.CursorType = adOpenKeyset

rstEmployees.LockType = adLockBatchOptimistic

rstEmployees.Open "employee", , , , adCmdTable

` Show the EditMode property under different editing

` states.

rstEmployees.AddNew

rstEmployees!emp_id = "T-T55555M"

rstEmployees!fname = "temp_fname"

rstEmployees!lname = "temp_lname"

EditModeOutput "After AddNew:", rstEmployees.EditMode

rstEmployees.UpdateBatch

EditModeOutput "After UpdateBatch:", rstEmployees.EditMode

rstEmployees!fname = "test"

EditModeOutput "After Edit:", rstEmployees.EditMode

rstEmployees.Close

` Delete new record because this is a demonstration.

cnn1.Execute "DELETE FROM employee WHERE emp_id = 'T-T55555M'"

End Sub

Public Function EditModeOutput(strTemp As String, _

intEditMode As Integer)

` Print report based on the value of the EditMode

` property.

Debug.Print strTemp

Debug.Print " EditMode = ";

Select Case intEditMode

Case adEditNone

Debug.Print "adEditNone"

Case adEditInProgress

Debug.Print "adEditInProgress"

Case adEditAdd

Debug.Print "adEditAdd"

End Select

End Function

ADO
Recordset Object EditMode Property

---

The editing status of the current record.

EditMode Property Return Values

Returns one of the following EditModeEnum
values:

Constant	Description
adEditNone	No editing operation is in progress.
adEditInProgress	The data in the current record has been modified but not yet saved.
adEditAdd	The AddNew method has been invoked and the current record in the copy buffer is a new record that hasn’t been saved in the database.

EditMode Property Remarks

ADO
maintains an editing buffer associated with the current record. This property
indicates whether changes have been made to this buffer, or whether a new
record has been created. Use the EditMode property to determine the
editing status of the current record. You can test for pending changes if an
editing process has been interrupted and determine whether you need to use the ADO
Recordset Object Update Method or ADO
Recordset Object CancelUpdate Method.

See the ADO
Recordset Object AddNew Method for a more detailed description of the EditMode
property under different editing conditions.

EditMode Property Example

See the ADO
Recordset Object CursorType Property example.

ADO
Recordset Object Filter Property

---

A filter for data in a recordset.

Filter Property Return Values

Sets or returns a Variant value, which can
contain one of the following:

Criteria string

A string made up of one or more individual
clauses concatenated with AND or OR operators.

Array of bookmarks

An array of unique bookmark values that
point to records in the Recordset object. This return value is not
currently supported on UNIX.

One of the following FilterGroupEnum
values:

Constant	Description
adFilterNone	Removes the current filter and restores all records to view.
adFilterPendingRecords	Enables you to view only records that have changed but have not yet been sent to the server. Only applicable for batch update mode. Not currently supported on UNIX.
adFilterAffectedRecords	Enables you to view only records affected by the last Delete, Resync, UpdateBatch, or CancelBatch call. Not currently supported on UNIX.
adFilterFetchedRecords	Enables you to view records in the current cache, that is, the results of the last call to retrieve records from the database. Not currently supported on UNIX.

Filter Property Remarks

Use the Filter property to
selectively screen out records in a Recordset object. The filtered
recordset becomes the current cursor. This affects other properties such as AbsolutePosition,
AbsolutePage, RecordCount, and ADO
Recordset Object PageCount Property that return values based on the
current cursor, since setting the Filter property to a specific value
will move the current record to the first record that satisfies the new value.

On UNIX systems the Filter property
is implemented for Recordset objects whose source is a SELECT query.
Setting the Filter property will resubmit the query with the criteria
string AND’d with the WHERE clause.

The criteria string is made up of clauses
in the form FieldName-Operator-Value (for example, “LastName =
‘Smith'”). You can create compound clauses by concatenating individual
clauses with AND (for example, “LastName = ‘Smith’ AND FirstName =
‘John'”) or OR (for example, “LastName = ‘Smith’ OR LastName =
‘Jones'”). Use the following guidelines for criteria strings:

FieldName

Must be a valid field name from the
recordset. If the field name contains spaces, you must enclose the name in
square brackets.

Operator

Must be one of the following: <, >,
<=, >=, <>, =, LIKE.

Value

The value with which you will compare the
field values (for example, ‘Smith’, #8/24/95#, 12.345 or $50.00). Use single
quotes with strings and pound signs (#) with dates. For numbers, you can use
decimal points, dollar signs, and scientific notation. If Operator is LIKE,
Value can use wildcards. Only the asterisk (*) and percent sign (%)
wildcards are allowed, and they must be the last character in the string. Value
may not be Null.

There is no precedence between AND
and OR. Clauses can be grouped within parentheses. However, you cannot
group clauses joined by an OR and then join the group to another clause
with an AND, like this:

(LastName = 'Smith' OR LastName = 'Jones') AND FirstName = 'John'

Instead, you would construct this filter
as:

(LastName = 'Smith' AND FirstName = 'John') OR

(LastName = 'Jones' AND FirstName = 'John')

In a LIKE clause, you can use a
wildcard at the beginning and end of the pattern (for example, LastName Like
‘*mit*’), or only at the end of the pattern (for example, LastName Like
‘Smit*’).

The filter constants make it easier to
resolve individual record conflicts during batch update mode by allowing you to
view, for example, only those records that were affected during the last ADO
Recordset Object UpdateBatch Method call.

Setting the Filter property itself
may fail because of a conflict with the underlying data (for example, a record
has already been deleted by another user); in such a case, the provider returns
warnings to the ADO Errors
Collection but does not halt program execution. A run-time error occurs
only if there are conflicts on all the requested records. Use the ADO
Recordset Object Status Property to locate records with conflicts.

Setting the Filter property to a
zero-length string (“”) has the same effect as using the adFilterNone

constant.

Whenever the Filter property is
set, the current record position moves to the first record in the filtered
subset of records in the recordset. Similarly, when the Filter property
is cleared, the current record position moves to the first record in the
recordset.

See the ADO
Recordset Object Bookmark Property for an explanation of bookmark values
from which you can build an array to use with the Filter property.

Filter Property Example

This Visual Basic example uses the Filter
property to open a new recordset based on a specified condition applied to an
existing recordset. It uses the RecordCount property to show the number
of records in the two recordsets. The FilterField function is required
for this procedure to run.

Public Sub FilterX()

Dim rstPublishers As ADODB.Recordset

Dim rstPublishersCountry As ADODB.Recordset

Dim strCnn As String

Dim intPublisherCount As Integer

Dim strCountry As String

Dim strMessage As String

` Open recordset with data from Publishers table.

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

Set rstPublishers = New ADODB.Recordset

rstPublishers.CursorType = adOpenStatic

rstPublishers.Open "publishers", strCnn, , , adCmdTable

` Populate the Recordset.

intPublisherCount = rstPublishers.RecordCount

` Get user input.

strCountry = Trim(InputBox( _

"Enter a country to filter on:"))

If strCountry <> "" Then

` Open a filtered Recordset object.

Set rstPublishersCountry = _

FilterField(rstPublishers, "Country", strCountry)

If rstPublishersCountry.RecordCount = 0 Then

MsgBox "No publishers from that country."

Else

` Print number of records for the original

` Recordset object and the filtered Recordset

` object.

strMessage = "Orders in original recordset: " & _

vbCr & intPublisherCount & vbCr & _

"Orders in filtered recordset (Country = '" & _

strCountry & "'): " & vbCr & _

rstPublishersCountry.RecordCount

MsgBox strMessage

End If

rstPublishersCountry.Close

End If

End Sub

Public Function FilterField(rstTemp As ADODB.Recordset, _

strField As String, strFilter As String) As ADODB.Recordset

` Set a filter on the specified Recordset object and then

` open a new Recordset object.

rstTemp.Filter = strField & " = '" & strFilter & "'"

Set FilterField = rstTemp

End Function

Note	When you know the data you want to select, it’s usually more efficient to open a recordset with an SQL statement. This example shows how you can create just one recordset and obtain records from a particular country.

Public Sub FilterX2()

Dim rstPublishers As ADODB.Recordset

Dim strCnn As String

` Open recordset with data from Publishers table.

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

Set rstPublishers = New ADODB.Recordset

rstPublishers.CursorType = adOpenStatic

rstPublishers.Open "SELECT * FROM publishers " & _

"WHERE Country = 'USA'", strCnn, , , adCmdText

` Print current data in recordset.

rstPublishers.MoveFirst

Do While Not rstPublishers.EOF

Debug.Print rstPublishers!pub_name & ", " & _

rstPublishers!country

rstPublishers.MoveNext

Loop

rstPublishers.Close

End Sub

ADO
Recordset Object LockType Property

---

The type of locks placed on records during
editing.

LockType Property Return Values

Sets or returns one of the following LockTypeEnum
values:

Constant	Description
adLockReadOnly	Default. Read-only; the data cannot be modified.
adLockPessimistic	Pessimistic locking, record by record. The provider does what is necessary to ensure successful editing of the records, usually by locking records at the data source immediately upon editing.
adLockOptimistic	Optimistic locking, record by record. The provider uses optimistic locking, locking records only when you call the Update method.
adLockBatchOptimistic	Optimistic batch updates. Required for batch update mode as opposed to immediate update mode.

LockType Property Remarks

Set the LockType property before
opening a recordset to specify what type of locking the provider should use
when opening it. Read the property to return the type of locking in use on an
open Recordset object. The LockType property is read/write when
the recordset is closed and read-only when it is open.

Providers may not support all lock types.
If a provider cannot support the requested LockType setting, it will
substitute another type of locking. To determine the actual locking
functionality available in a Recordset object, use the ADO
Recordset Object Supports Method with adUpdate and adUpdateBatch.

LockType Property Example

See the ADO
Recordset Object CursorType Property example.

ADO
Recordset Object MarshalOptions Property

---

Indicates which records are to be
marshaled back to the server. This is a client-side only property.

MarshalOptions Property Return Values

Sets or returns a Long value that
can be one of the following constants:

Constant	Description
adMarshalAll	Default. All rows are returned to the server.
adMarshalModifiedOnly	Only modified rows are returned to the server.

MarshalOptions Property Remarks

When using a client-side (ADOR) recordset,
records that have been modified on the client are written back to the
middle-tier or Web server through a technique called marshaling, the
process of packaging and sending interface method parameters across thread or
process boundaries. Setting the MarshalOptions property can improve
performance when modified remote data is marshaled for updating back to the
middle-tier or Web server.

Remote Data Service Usage: This
property is only used on a client-side (ADOR) recordset.

MarshalOptions Property Example

This Visual Basic example uses the MarshalOptions
property to specify what rows are sent back to the server—All Rows or only
Modified Rows.

Public Sub MarshalOptionsX()

Dim rstEmployees As ADODB.Recordset

Dim strCnn As String

Dim strOldFirst As String

Dim strOldLast As String

Dim strMessage As String

Dim strMarshalAll As String

Dim strMarshalModified As String

` Open recordset with names from Employee table.

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

Set rstEmployees = New ADODB.Recordset

rstEmployees.CursorType = adOpenKeyset

rstEmployees.LockType = adLockOptimistic

rstEmployees.CursorLocation = adUseClient

rstEmployees.Open "SELECT fname, lname " & _

"FROM Employee ORDER BY lname", strCnn, , , adCmdText

` Store original data.

strOldFirst = rstEmployees!fname

strOldLast = rstEmployees!lname

` Change data in edit buffer.

rstEmployees!fname = "Linda"

rstEmployees!lname = "Kobara"

` Show contents of buffer and get user input.

strMessage = "Edit in progress:" & vbCr & _

" Original data = " & strOldFirst & " " & _

strOldLast & vbCr & " Data in buffer = " & _

rstEmployees!fname & " " & rstEmployees!lname & vbCr & vbCr & _

"Use Update to replace the original data with " & _

"the buffered data in the Recordset?"

strMarshalAll = "Would you like to send all the rows " & _

"in the recordset back to the server?"

strMarshalModified = "Would you like to send only " & _

"modified rows back to the server?"

If MsgBox(strMessage, vbYesNo) = vbYes Then

If MsgBox(strMarshalAll, vbYesNo) = vbYes Then

rstEmployees.MarshalOptions = adMarshalAll

rstEmployees.Update

ElseIf MsgBox(strMarshalModified, vbYesNo) = vbYes Then

rstEmployees.MarshalOptions = adMarshalModifiedOnly

rstEmployees.Update

End If

End If

` Show the resulting data.

MsgBox "Data in recordset = " & rstEmployees!fname & " " & _

rstEmployees!lname

` Restore original data because this is a demonstration.

If Not (strOldFirst = rstEmployees!fname And _

strOldLast = rstEmployees!lname) Then

rstEmployees!fname = strOldFirst

rstEmployees!lname = strOldLast

rstEmployees.Update

End If

rstEmployees.Close

End Sub

ADO
Recordset Object MaxRecords Property

---

The maximum number of records to return to
a recordset from a query.

MaxRecords Property Return Values

Sets or returns a Long value.
Default is zero (no limit).

MaxRecords Property Remarks

Use the MaxRecords property to
limit the number of records the provider returns from the data source. The
default setting of this property is zero, which means the provider returns all
requested records. The MaxRecords property is read/write when the
recordset is closed and read-only when it is open.

MaxRecords Property Example

This Visual Basic example uses the MaxRecords
property to open a recordset containing the 10 most expensive titles in the Titles
table.

Public Sub MaxRecordsX()

Dim rstTemp As ADODB.Recordset

Dim strCnn As String

` Open recordset containing the 10 most expensive

` titles in the Titles table.

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

Set rstTemp = New ADODB.Recordset

rstTemp.MaxRecords = 10

rstTemp.Open "SELECT Title, Price FROM Titles " & _

"ORDER BY Price DESC", strCnn, , , adCmdText

` Display the contents of the recordset.

Debug.Print "Top Ten Titles by Price:"

Do While Not rstTemp.EOF

Debug.Print " " & rstTemp!Title & " - " & rstTemp!Price

rstTemp.MoveNext

Loop

rstTemp.Close

End Sub

ADO
Recordset Object PageCount Property

---

The number of pages of data the Recordset
object contains.

PageCount Property Return Values

Returns a Long value.

PageCount Property Remarks

Use the PageCount property to
determine how many pages of data are in the Recordset object. Pages

are groups of records whose size equals the ADO
Recordset Object PageSize Property setting. Even if the last page is
incomplete, because there are fewer records than the PageSize value, it
counts as an additional page in the PageCount value. If the Recordset
object does not support this property, the value will be -1 to indicate that
the PageCount is indeterminable.

See the PageSize and AbsolutePage
properties for more on page functionality.

PageCount Property Example

See the AbsolutePage example.

ADO
Recordset Object PageSize Property

---

The number of records that constitute one
page in the recordset.

PageSize Property Return Values (ADO Recordset Object)

Sets or returns a Long value, the
number of records on a page. Default is 10.

PageSize Property Remarks (ADO Recordset Object)

Use the PageSize property to
determine how many records make up a logical page of data. Establishing a page
size allows you to use the AbsolutePage property to move to the first
record of a particular page. This is useful in Web-server scenarios when you
want to allow the user to page through data, viewing a certain number of
records at a time.

This property can be set at any time, and
its value will be used for calculating where the first record of a particular
page is.

PageSize Property Example (ADO Recordset Object)

See the AbsolutePage property
example.

ADO Recordset ObjectState Property

---

Describes the current state of an object.

State Property Return Values (ADO Recordset Object)

Sets or returns a Long value that
can be one of the following constants:

Constant	Description
AdStateClosed	Default. The object is closed.
AdStateOpen	The object is open.

State Property Remarks (ADO Recordset Object)

You can use the State property to
determine the current state of a given object at any time.

ADO
Recordset Object Status Property

---

Indicates the status of the current record
with respect to batch updates or other bulk operations.

Status Property Return Values (ADO Recordset Object)

Returns a sum of one or more of the
following RecordStatusEnum values:

Constant	Description
adRecOK	The record was successfully updated.
adRecNew	The record is new.
adRecModified	The record was modified.
adRecDeleted	The record was deleted.
adRecUnmodified	The record was not modified.
adRecInvalid	The record was not saved because its bookmark is invalid.
adRecMultipleChanges	The record was not saved because it would have affected multiple records.
adRecPendingChanges	The record was not saved because it refers to a pending insert.
adRecCanceled	The record was not saved because the operation was canceled.
adRecCantRelease	The new record was not saved because of existing record locks.
adRecConcurrencyViolation	The record was not saved because optimistic concurrency was in use.
adRecIntegrityViolation	The record was not saved because the user violated integrity constraints.
adRecMaxChangesExceeded	The record was not saved because there were too many pending changes.
adRecObjectOpen	The record was not saved because of a conflict with an open storage object.
adRecOutOfMemory	The record was not saved because the computer has run out of memory.
adRecPermissionDenied	The record was not saved because the user has insufficient permissions.
adRecSchemaViolation	The record was not saved because it violates the structure of the underlying database.
adRecDBDeleted	The record has already been deleted from the data source.

Status Property Remarks (ADO Recordset Object)

Use the Status property to see what
changes are pending for records modified during batch updating. You can also
use the Status property to view the status of records that fail during
bulk operations such as when you call the ADO
Recordset Object Resync Method, ADO
Recordset Object UpdateBatch Method, or ADO
Recordset Object CancelBatch Method methods on a Recordset

object, or set the ADO
Recordset Object Filter Property on a Recordset object to an array
of bookmarks. With this property, you can determine how a given record failed
and resolve it accordingly.

Status Property Example (ADO Recordset Object)

This example uses the Status

property to display which records have been modified in a batch operation
before a batch update has occurred.

Public Sub StatusX()

Dim rstTitles As ADODB.Recordset

Dim strCnn As String

` Open recordset for batch update.

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

Set rstTitles = New ADODB.Recordset

rstTitles.CursorType = adOpenKeyset

rstTitles.LockType = adLockBatchOptimistic

rstTitles.Open "titles", strCnn, , , adCmdTable

` Change the type of psychology titles.

Do Until rstTitles.EOF

If Trim(rstTitles!Type) = "psychology" Then

rstTitles!Type = "self_help"

End If

rstTitles.MoveNext

Loop

` Display Title ID and status.

rstTitles.MoveFirst

Do Until rstTitles.EOF

If rstTitles.Status = adRecModified Then

Debug.Print rstTitles!title_id & " - Modified"

Else

Debug.Print rstTitles!title_id

End If

rstTitles.MoveNext

Loop

` Cancel the update because this is a demonstration.

rstTitles.CancelBatch

rstTitles.Close

End Sub

ADO
Recordset Object Source Property

---

The source for the data in a Recordset
object (Command object, SQL statement, table name, or stored procedure).

Source Property Return Values (ADO Recordset Object)

Sets a String value or Command
object reference; returns only a String value.

Source Property Remarks (ADO Recordset Object)

Use the Source property to specify
a data source for a Recordset object using one of the following: an ADO Command
Object variable, an SQL statement, a stored procedure, or a table name. The

Source property is read/write for closed Recordset objects and
read-only for open Recordset objects.

If you set the Source property to a
Command object, the ActiveConnection property of the Recordset

object will inherit the value of the ActiveConnection property for the
specified Command object. However, reading the Source property
does not return a Command object; instead, it returns the CommandText
property of the Command object to which you set the Source

property.

If the Source property is an SQL
statement, a stored procedure, or a table name, you can optimize performance by
passing the appropriate Options argument with the ADO
Recordset Object Open Method call.

Source Property Example (ADO Recordset Object)

This Visual Basic example demonstrates the
Source property by opening three Recordset objects based on
different data sources.

Public Sub SourceX()

Dim cnn1 As ADODB.Connection

Dim rstTitles As ADODB.Recordset

Dim rstPublishers As ADODB.Recordset

Dim rstTitlesPublishers As ADODB.Recordset

Dim cmdSQL As ADODB.Command

Dim strCnn As String

Dim strSQL As String

` Open a connection.

Set cnn1 = New ADODB.Connection

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

cnn1.Open strCnn

` Open a recordset based on a command object.

Set cmdSQL = New ADODB.Command

Set cmdSQL.ActiveConnection = cnn1

cmdSQL.CommandText = "Select title, type, pubdate " & _

"FROM titles ORDER BY title"

Set rstTitles = cmdSQL.Execute()

` Open a recordset based on a table.

Set rstPublishers = New ADODB.Recordset

rstPublishers.Open "publishers", strCnn, , , adCmdTable

` Open a recordset based on an SQL string.

Set rstTitlesPublishers = New ADODB.Recordset

strSQL = "SELECT title_ID AS TitleID, title AS Title, " & _

"publishers.pub_id AS PubID, pub_name AS PubName " & _

"FROM publishers INNER JOIN titles " & _

"ON publishers.pub_id = titles.pub_id " & _

"ORDER BY Title"

rstTitlesPublishers.Open strSQL, strCnn, , , adCmdText

` Use the Source property to display the source of each recordset.

MsgBox "rstTitles source: " & vbCr & _

rstTitles.Source & vbCr & vbCr & _

"rstPublishers source: " & vbCr & _

rstPublishers.Source & vbCr & vbCr & _

"rstTitlesPublishers source: " & vbCr & _

rstTitlesPublishers.Source

rstTitles.Close

rstPublishers.Close

rstTitlesPublishers.Close

cnn1.Close

End Sub

ADO
Recordset Object RecordCount Property

---

The current number of records in a Recordset
object.

RecordCount Property Return Values

Returns a Long value.

RecordCount Property Remarks

Use the RecordCount property to
find out how many records are in a Recordset object. The property
returns -1 when ADO

cannot determine the number of records. Reading the RecordCount property
on a closed recordset causes an error.

If the Recordset object supports
approximate positioning or bookmarks—that is, ADO
Recordset Object Supports Method (adApproxPosition) or Supports
(adBookmark), respectively, returns True—this value will be the
exact number of records in the recordset regardless of whether it has been
fully populated. If the Recordset object does not support approximate
positioning, this property may be a significant drain on resources because all
records will have to be retrieved and counted to return an accurate RecordCount

value.

RecordCount Property Example

This Visual Basic example uses the Filter
property to open a new recordset based on a specified condition applied to an
existing recordset. It uses the RecordCount property to show the number
of records in the two recordsets. The FilterField function is required
for this procedure to run.

Public Sub FilterX()

Dim rstPublishers As ADODB.Recordset

Dim rstPublishersCountry As ADODB.Recordset

Dim strCnn As String

Dim intPublisherCount As Integer

Dim strCountry As String

Dim strMessage As String

` Open recordset with data from Publishers table.

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

Set rstPublishers = New ADODB.Recordset

rstPublishers.CursorType = adOpenStatic

rstPublishers.Open "publishers", strCnn, , , adCmdTable

` Populate the Recordset.

intPublisherCount = rstPublishers.RecordCount

` Get user input.

strCountry = Trim(InputBox( _

"Enter a country to filter on:"))

If strCountry <> "" Then

` Open a filtered Recordset object.

Set rstPublishersCountry = _

FilterField(rstPublishers, "Country", strCountry)

If rstPublishersCountry.RecordCount = 0 Then

MsgBox "No publishers from that country."

Else

` Print number of records for the original

` Recordset object and the filtered Recordset

` object.

strMessage = "Orders in original recordset: " & _

vbCr & intPublisherCount & vbCr & _

"Orders in filtered recordset (Country = '" & _

strCountry & "'): " & vbCr & _

rstPublishersCountry.RecordCount

MsgBox strMessage

End If

rstPublishersCountry.Close

End If

End Sub

Public Function FilterField(rstTemp As ADODB.Recordset, _

strField As String, strFilter As String) As ADODB.Recordset

` Set a filter on the specified Recordset object and then

` open a new Recordset object.

rstTemp.Filter = strField & " = '" & strFilter & "'"

Set FilterField = rstTemp

End Function

Note	When you know the data you want to select, it’s usually more efficient to open a recordset with an SQL statement. This example shows how you can create just one recordset and obtain records from a particular country.

Public Sub FilterX2()

Dim rstPublishers As ADODB.Recordset

Dim strCnn As String

` Open recordset with data from Publishers table.

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

Set rstPublishers = New ADODB.Recordset

rstPublishers.CursorType = adOpenStatic

rstPublishers.Open "SELECT * FROM publishers " & _

"WHERE Country = 'USA'", strCnn, , , adCmdText

` Print current data in recordset.

rstPublishers.MoveFirst

Do While Not rstPublishers.EOF

Debug.Print rstPublishers!pub_name & ", " & _

rstPublishers!country

rstPublishers.MoveNext

Loop

rstPublishers.Close

End Sub

ADO
Recordset Object Remarks

---

Use Recordset objects to manipulate
data from a provider. In ADO,
data is almost entirely manipulated using Recordset objects. All Recordset

objects are constructed using records (rows) and fields (columns). Depending on
the functionality supported by the provider, some Recordset methods or
properties may not be available.

Recordset objects can also be run
remotely. For example, in a Web-based application, you can open a Recordset
on the client, using the progID “ADOR.” The Remote Data Service
provides a mechanism for local data caching and local cursor movement in remote
recordset data. A client-side recordset can be used in the same way as a server-side
recordset, and supports almost all of the Recordset object’s normal
methods and properties. Recordset methods and properties that are not
supported on a client-side recordset, or that behave differently, are noted in
the topics for those properties and methods.

There are four different cursor types
defined in ADO:

Cursor	Description
Dynamic	Allows you to view additions, changes and deletions by other users, and allows all types of movement through the recordset that don’t rely on bookmarks; allows bookmarks if the provider supports them.
Keyset	Behaves like a dynamic cursor, except that it prevents you from seeing records that other users add, and prevents access to records that other users delete. Data change by other users will still be visible. It always supports bookmarks and therefore allows all types of movement through the recordset.
Static	Provides a static copy of a set of records for you to use to find data or generate reports. Always allows bookmarks and therefore allows all types of movement through the recordset. Additions, changes, or deletions by other users will not be visible. This is the only type of cursor allowed when you open a client-side (ADOR) Recordset object.
Forward-only	Behaves identically to a dynamic cursor except that it allows you to scroll only forward through records. This improves performance in situations where you need to make only a single pass through a recordset.

Set the ADO
Recordset Object CursorType Property prior to opening the recordset to
choose the cursor type, or pass a CursorType argument with the ADO
Recordset Object Open Method. Some providers don’t support all cursor
types. Check the documentation for the provider. If you don’t specify a cursor
type, ADO opens
a forward-only cursor by default.

When used with some providers (such as the
Microsoft ODBC Provider for OLE DB in conjunction with Microsoft SQL Server),
you can create Recordset objects independently of a previously defined ADO Connection
Object by passing a connection string with the Open method. ADO still creates a Connection
object, but it doesn’t assign that object to an object variable. However, if
you are opening multiple Recordset objects over the same connection, you
should explicitly create and open a Connection object; this assigns the Connection

object to an object variable. If you do not use this object variable when
opening your Recordset objects, ADO
creates a new Connection object for each new recordset, even if you pass
the same connection string.

You can create as many Recordset
objects as needed.

When you open a recordset, the current
record is positioned to the first record (if any) and the ADO
Recordset Object BOF, EOF Properties are set to False. If there are
no records, the BOF and EOF property settings are True.

Use the ADO
Recordset Object MoveFirst, MoveLast, MoveNext, MovePrevious Methods, as well
as the ADO
Recordset Object Move Method, and the AbsolutePosition, AbsolutePage,
and ADO
Recordset Object Filter Property properties to reposition the
current record, assuming the provider supports the relevant functionality.
Forward-only Recordset objects support only the MoveNext method.
When you use the Move methods to visit each record (or enumerate the
recordset), you can use the BOF and EOF properties to see if
you’ve moved beyond the beginning or end of the recordset.

Recordset objects may support two
types of updating: immediate and batched. In immediate updating, all changes to
data are written immediately to the underlying data source once you call the ADO
Recordset Object Update Method. You can also pass arrays of values as
parameters with the ADO
Recordset Object AddNew Method and Update methods and simultaneously
update several fields in a record.

If a provider supports batch updating, you
can have the provider cache changes to more than one record and then transmit
them in a single call to the database with the ADO
Recordset Object UpdateBatch Method. This applies to changes made with the AddNew,

Update, and ADO
Recordset Object Delete Method methods. After you call the UpdateBatch
method, you can use the ADO
Recordset Object Status Property to check for any data conflicts in order
to resolve them. Batch updating is not currently supported on UNIX.

Note	To execute a query without using an ADO Command Object, pass a query string to the ADO Recordset Object Open Method of a Recordset object. However, a Command object is required when you want to retain the command text and re-execute it, or use query parameters.

ADO
Collections

---

Collections	Description
Errors	Contains all stored Error objects, all of which pertain to a single operation involving ADO.
Fields	Contains all stored Field objects of a Recordset object.
Parameters	Contains all the Parameter objects of a Command object.
Properties	Contains all the Property objects for the specific instance of an object. This collection is not currently supported on UNIX.
Methods
Append	Appends a new object to the Parameters collection.
Clear	Clears the contents of an Errors collection.
Delete	Deletes an object from the Parameters collection.
Item	Returns a specific member of a collection by name or ordinal number.
Refresh	Updates the objects in a collection to reflect objects available from and specific to the provider.
Properties
Count	The number of objects in a collection.

In this section:

ADO Errors
Collection

ADO Fields
Collection

ADO Parameters
Collection

ADO Properties
Collection

ADO Collections
Methods

ADO Collections
Properties

ADO Errors
Collection

---

The Errors collection contains all
stored ADO
Error Object objects created in response to a single failure
involving the provider.

In this section:

ADO Errors
Collection Remarks

ADO Errors
Collection Remarks

---

Any operation involving ADO objects can generate one or more provider
errors. As each error occurs, one or more ADO Error Object

objects may be placed in the Errors collection of the ADO Connection
Object. When another ADO
operation generates an error, the Errors collection is cleared, and the
new set of Error objects may be placed in the Errors collection.

Each Error object represents a
specific provider error, not an ADO
error. ADO
errors are exposed to the run-time exception-handling mechanism. For example,
in Microsoft Visual Basic, the occurrence of an ADO-specific error will trigger
an On Error event and appear in the Err object.

ADO
operations that don’t generate an error have no effect on the Errors
collection. Use the ADO Collections
Clear Method to manually clear the Errors collection.

The set of Error objects in the Errors

collection describes all errors that occurred in response to a single
statement. Enumerating the specific errors in the Errors collection
enables your error-handling routines to more precisely determine the cause and
origin of an error, and take appropriate steps to recover.

Some properties and methods return
warnings that appear as Error objects in the Errors collection
but do not halt a program’s execution. Before you call the ADO
Recordset Object Resync Method, ADO
Recordset Object UpdateBatch Method, or ADO
Recordset Object CancelBatch Method methods on an ADO Recordset
Object, or before you set the ADO
Recordset Object Filter Property on a Recordset object, call the Clear

method on the Errors collection so that you can read the Count
Property of the Errors collection to test for returned warnings.

Note	See the ADO Error Object for a more detailed explanation of the way a single ADO operation can generate multiple errors.

ADO Fields
Collection

---

The Fields collection contains all
the Field objects of a Recordset object.

In this section:

ADO Fields
Collection Remarks

ADO Fields
Collection Remarks

---

An ADO Recordset
Object has a Fields collection made up of ADO Field Object
objects. Each Field object corresponds to a column in the recordset.
You can populate the Fields collection before opening the recordset by
calling the ADO
Collections Refresh Method on the collection.

Note	See the ADO Field Object for a more detailed explanation of how to use Field objects.

ADO
Parameters Collection

---

The Parameters collection contains
all the Parameter objects of a Command object.

In this section:

ADO Parameters
Collection Remarks

ADO
Parameters Collection Remarks

---

An ADO Command
Object has a Parameters collection made up of ADO Parameter
Object objects. Using the ADO Collections
Refresh Method on a Command object’s Parameters collection
retrieves provider parameter information for the stored procedure or
parameterized query specified in the Command object. Some providers do
not support stored procedure calls or parameterized queries; calling the Refresh

method on the Parameters collection when using such a provider will
return an error.

If you have not defined your own Parameter
objects and you access the Parameters collection before calling the Refresh
method, ADO

will automatically call the method and populate the collection for you.

You can minimize calls to the provider to
improve performance if you know the properties of the parameters associated
with the stored procedure or parameterized query you wish to call. Use the CreateParameter
method to create Parameter objects with the appropriate property
settings and use the ADO Collections
Append Method to add them to the Parameters collection. This lets
you set and return parameter values without having to call the provider for the
parameter information. If you are writing to a provider that does not supply
parameter information, you must manually populate the Parameters

collection using this method to be able to use parameters at all. Use the ADO Collections
Delete Method to remove Parameter objects from the Parameters
collection if necessary.

ADO
Properties Collection

---

The Properties collection contains
all the Property objects for a specific instance of an object. The Property
collection is not currently supported on UNIX.

In this section:

ADO Properties
Collection Remarks

ADO
Properties Collection Remarks

---

Some ADO
objects have a Properties collection made up of ADO Property
Object objects. Each Property object corresponds to a
characteristic of the ADO
object specific to the provider.

Note	See the ADO Property Object topic for a more detailed explanation of how to use Property objects.

ADO
Collections Methods

---

This section discusses ADO collections methods.

In this section:

ADO Collections
Append Method

ADO Collections
Clear Method

ADO Collections
Delete Method

ADO Collections
Item Method

ADO Collections
Refresh Method

ADO
Collections Append Method

---

Appends an object to a collection.

In this section:

Append Method
Applies To

Append Method
Syntax

Append Method
Parameters

Append Method
Remarks

Append Method
Examples

Append Method Applies To

---

ADO Parameters
Collection

Append Method Syntax

---

collection.Append object

Append Method Parameters

---

object

An object variable representing the object
to be appended.

Append Method Remarks

---

Use the Append method on a
collection to add an object to that collection. This method is available only
on the Parameters collection of a ADO Command
Object. You must set the ADO
Parameter Object Type Property of an ADO Parameter
Object before appending it to the Parameters collection. If you
select a variable-length data type, you must also set the ADO
Parameter Object Size Property to a value greater than zero.

By describing the parameter yourself, you
can minimize calls to the provider and consequently improve performance when
using stored procedures or parameterized queries. However, you must know the
properties of the parameters associated with the stored procedure or
parameterized query you wish to call. Use the CreateParameter method to
create Parameter objects with the appropriate property settings and use
the Append method to add them to the Parameters collection. This
lets you set and return parameter values without having to call the provider
for the parameter information. If you are writing to a provider that does not supply
parameter information, you must manually populate the Parameters
collection using this method to be able to use parameters at all.

Append Method Examples

---

This Visual Basic example uses the Append
and CreateParameter methods to execute a stored procedure with an input
parameter.

Public Sub AppendX()

Dim cnn1 As ADODB.Connection

Dim cmdByRoyalty As ADODB.Command

Dim prmByRoyalty As ADODB.Parameter

Dim rstByRoyalty As ADODB.Recordset

Dim rstAuthors As ADODB.Recordset

Dim intRoyalty As Integer

Dim strAuthorID As String

Dim strCnn As String

` Open connection.

Set cnn1 = New ADODB.Connection

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

cnn1.Open strCnn

cnn1.CursorLocation = adUseClient

` Open command object with one parameter.

Set cmdByRoyalty = New ADODB.Command

cmdByRoyalty.CommandText = "byroyalty"

cmdByRoyalty.CommandType = adCmdStoredProc

` Get parameter value and append parameter.

intRoyalty = Trim(InputBox("Enter royalty:"))

Set prmByRoyalty = cmdByRoyalty.CreateParameter("percentage", _

adInteger, adParamInput)

cmdByRoyalty.Parameters.Append prmByRoyalty

prmByRoyalty.Value = intRoyalty

` Create recordset by executing the command.

Set cmdByRoyalty.ActiveConnection = cnn1

Set rstByRoyalty = cmdByRoyalty.Execute

` Open the Authors table to display author names.

Set rstAuthors = New ADODB.Recordset

rstAuthors.Open "authors", cnn1, , , adCmdTable

` Print current data in the recordset, adding

` author names from Authors table.

Debug.Print "Authors with " & intRoyalty & " percent royalty"

Do While Not rstByRoyalty.EOF

strAuthorID = rstByRoyalty!au_id

Debug.Print " " & rstByRoyalty!au_id & ", ";

rstAuthors.Filter = "au_id = '" & strAuthorID & "'"

Debug.Print rstAuthors!au_fname & " " & rstAuthors!au_lname

rstByRoyalty.MoveNext

Loop

rstByRoyalty.Close

rstAuthors.Close

cnn1.Close

End Sub

ADO
Collections Clear Method

---

Removes all of the objects in a
collection.

In this section:

Clear Method
Applies To

Clear Method
Syntax

Clear Method
Remarks

Clear Method
Examples

Clear Method Applies To

---

ADO Errors
Collection

Clear Method Syntax

---

Errors.Clear

Clear Method Remarks

---

Use the Clear method on the Errors

collection to remove all existing ADO Error Object
objects from the collection. When an error occurs, ADO automatically clears the Errors
collection and fills it with Error objects based on the new error.
However, some properties and methods return warnings that appear as Error
objects in the Errors collection but do not halt a program’s execution.
Before you call the ADO
Recordset Object Resync Method, ADO
Recordset Object UpdateBatch Method, or ADO
Recordset Object CancelBatch Method methods on an ADO Recordset
Object or before you set the ADO
Recordset Object Filter Property on a Recordset object, call the Clear

method on the Errors collection. Doing so enables you to read the ADO Collections
Count Property of the Errors collection to test for returned
warnings as a result of these specific calls.

Clear Method Examples

---

See the ADO
Command Object Execute Method.

ADO
Collections Delete Method

---

Deletes an object from the Parameters
collection.

In this section:

Delete Method
Applies To

Delete Method
Syntax

Delete Method
Parameters

Delete Method
Remarks

Delete Method Applies To

---

ADO Parameters
Collection

Delete Method Syntax

---

object.Parameters.Delete ( Index )

Delete Method Parameters

---

object

A Command object.

Index

A Variant that evaluates either
to the name or to the ordinal number of an object in a collection.

Delete Method Remarks

---

Using the Delete method on a Parameters
collection lets you remove one of the objects in the collection. This method is
available only on the Parameters collection of an ADO Command
Object. You must the use ADO Parameter
Object object’s ADO
Parameter Object Name Property or its collection index when calling the Delete

method; an object variable is not a valid argument.

ADO
Collections Item Method

---

Returns a specific member of a collection
by name or ordinal number.

In this section:

Item Method
Applies To

Item Method
Syntax

Item Method
Parameters

Item Method
Return Values

Item Method
Remarks

Item Method Applies To

---

ADO Errors
Collection, ADO Fields
Collection, ADO Parameters
Collection, ADO Properties
Collection

Item Method Syntax

---

Set object = collection.Item ( Index )

Item Method Parameters

---

object

Object reference created.

Index

A Variant that evaluates either
to the name or to the ordinal number of an object in a collection.

Item Method Return Values

---

Returns an object reference.

Item Method Remarks

---

Use the Item method to return a
specific object in a collection. If the method cannot find an object in the
collection corresponding to the Index argument, an error occurs. Also,
some collections don’t support named objects; for these collections, you must
use ordinal number references.

The Item method is the default
method for all collections; therefore, the following syntax forms are
interchangeable:

collection.Item (Index)

collection (Index)

ADO
Collections Refresh Method

---

Updates the objects in a collection to
reflect objects available from and specific to the provider.

In this section:

Refresh Method
Applies To

Refresh Method
Syntax

Refresh Method
Parameters Collection

Refresh Method
Fields Collection

Refresh Method
Properties Collection

Refresh Method
Example

Refresh Method Applies To

---

ADO Fields
Collection, ADO Parameters
Collection, ADO Properties
Collection

Refresh Method Syntax

---

collection.Refresh

Refresh Method Parameters Collection

---

Using the Refresh method on a ADO Command
Object object’s Parameters collection retrieves provider-side
parameter information for the stored procedure or parameterized query specified
in the Command object. The collection will be empty for providers that
do not support stored procedure calls or parameterized queries.

You should set the ActiveConnection property
of the Command object to a valid ADO Connection
Object, the ADO
Command Object CommandText Property to a valid command, and the ADO
Command Object CommandType Property to adCmdStoredProc before
calling the ADO
Collections Refresh Method.

If you access the Parameters
collection before calling the Refresh method, ADO will automatically call the method and
populate the collection for you.

Note

If you use the Refresh method to obtain parameter information from the provider and it returns one or more variable-length data type ADO Parameter Object objects, ADO may allocate memory for the parameters based on their maximum potential size, which will cause an error during execution. You should explicitly set the ADO Parameter Object Size Property for these parameters before calling the ADO Command Object Execute Method to prevent errors.

Refresh Method Fields Collection

---

Using the Refresh method on the Fields
collection has no visible effect. To retrieve changes from the underlying
database structure, you must use either the ADO
Recordset Object Requery Method or, if the Recordset object does not
support bookmarks, the ADO
Recordset Object MoveFirst, MoveLast, MoveNext, MovePrevious Methods method.

Refresh Method Properties Collection

---

Using the Refresh method on a Properties
collection of some objects populates the collection with the dynamic properties
the provider exposes. These properties provide information about functionality
specific to the provider beyond the built-in properties ADO supports.

The Refresh method accomplishes
different tasks depending on the collection from which you call it.

Refresh Method Example

---

This Visual Basic example demonstrates
using the Refresh method to refresh the Parameters collection for
a stored procedure Command object.

Public Sub RefreshX()

Dim cnn1 As ADODB.Connection

Dim cmdByRoyalty As ADODB.Command

Dim rstByRoyalty As ADODB.Recordset

Dim rstAuthors As ADODB.Recordset

Dim intRoyalty As Integer

Dim strAuthorID As String

Dim strCnn As String

' Open connection.

Set cnn1 = New ADODB.Connection

strCnn = "driver={SQL Server};server=srv;" & _

"uid=sa;pwd=;database=pubs"

cnn1.Open strCnn

' Open a command object for a stored procedure

' with one parameter.

Set cmdByRoyalty = New ADODB.Command

Set cmdByRoyalty.ActiveConnection = cnn1

cmdByRoyalty.CommandText = "byroyalty"

cmdByRoyalty.CommandType = adCmdStoredProc

cmdByRoyalty.Parameters.Refresh

' Get paramater value and execute the command,

' storing the results in a recordset.

intRoyalty = Trim(InputBox("Enter royalty:"))

cmdByRoyalty.Parameters(1) = intRoyalty

Set rstByRoyalty = cmdByRoyalty.Execute()

` Open the Authors table to get author names for display.

Set rstAuthors = New ADODB.Recordset

rstAuthors.Open "authors", cnn1, , , adCmdTable

' Print current data in the recordset, adding

' author names from Authors table.

Debug.Print "Authors with " & intRoyalty & " percent royalty"

Do While Not rstByRoyalty.EOF

strAuthorID = rstByRoyalty!au_id

Debug.Print " " & rstByRoyalty!au_id & ", ";

rstAuthors.Filter = "au_id = '" & strAuthorID & "'"

Debug.Print rstAuthors!au_fname & " " & _

rstAuthors!au_lname

rstByRoyalty.MoveNext

Loop

rstByRoyalty.Close

rstAuthors.Close

cnn1.Close

End Sub

ADO
Collections Properties

---

This section lists ADO collections properties.

In this section:

ADO Collections
Count Property

ADO
Collections Count Property

---

The number of objects in a collection.

In this section:

Count Property
Applies To

Count Property
Return Values

Count Property
Remarks

Count Property
Example

Count Property Applies To

---

ADO Errors
Collection, ADO Fields
Collection, ADO Parameters
Collection, ADO Properties
Collection

Folder Count method

Dim fs ‘ variable declared outside a procedure (this is a global variable)
‘ this is hold a reference to the file system object
‘ create an instance
Set fs = CreateObject(“scripting.filesystemobject”)
‘ count files in windows directory
MsgBox CountFiles (“C:\Program Files\ESL India Ltd\BillingSaaClient\SAA\output”)
‘ takes a string argument containing the name of the directory

‘ returns an integer contiang the nubmer of files in that direcrectory

‘ and all sub directories

Function CountFiles (ByVal StrFolder)
Dim ParentFld
Dim SubFld
Dim IntCount
‘ note the use of the fs global variable

Set ParentFld = fs.GetFolder (StrFolder)
‘ count the number of files in the current directory

IntCount = ParentFld.subfolders.Count

For Each SubFld In ParentFld.SubFolders

‘ count all files in each subfolder – recursion point

IntCount = IntCount + CountFiles(SubFld.Path)

Next

‘ return counted files

CountFiles = IntCount

End Function

Work With text box( Windows WinEdit box)

In most cases, double-clicking on a data input field is the preferable method of selection; furthermore, it is usually necessary versus a single mouse click on the object. Double –clicking selects the all of the data in that field so that it can be replaced with the new data. A single- click operation, may only place the cursor in the input control , selecting none of the existing data and normally activates the insert mode . Not good. The existing data is not replaced with the new value. In some applications where multiple words or value separated by spaces can lives in a single edit box. We have found the even double-clicking may not get all of the text selected and ready for replacement. In this case, the method that consistently ensures that all of the data is selected is this: navigate to control via keyboard control, such as tabbing, right arrow or mouse click. Once the cursor is placed into the control, record these keystrokes:

Ctrl + Home
Ctrl + Shift + End
Delete

Like
Dialog(“Login”).WinEdit(“Agent Name:”).Type micCtrlDwn + micHome + micCtrlUp
Dialog(“Login”).WinEdit(“Agent Name:”).Type micCtrlDwn + micShiftDwn +  micEnd + micShiftUp + micCtrlUp

Work With List Box

When we  use list box Object in QTP. QTP perform following operation on listbox . like

Browser(“Browser”).Page(“Page”)..WebList(“ListBox”).Select “ListBoxValue”

It select the value from List Box that value must be present in list box otherwise QTP give an error  like that

“Cannot identify the specified item of the ListBox object. Confirm that the specified item is included in the object’s item collection.”

So before selection any value in list box Always insure the value you are going to select in list box must be present by use this method/Function

dim objListBox, sValuematch ,bTrue
set objListBox =  Browser(“Browser”).Page(“Page”)..WebList(“ListBox”)
sValuematch =”Type Value that need to be check”
bTrue = fn_Valuematch_for_weblistbx(objListBox  ,sValuematch )
if  bTrue =True then
Browser(“Browser”).Page(“Page”)..WebList(“ListBox”).Select “ListBoxValue”
end if

REM*************************************************************************************************************
REM ” this function Check the value u define match With in WEBListbox or Not if match return true otherwise False
Public Function fn_Valuematch_for_weblistbx(byval m_listboxobject, byval m_valuematch)
”check is m_listboxobject  object  or not
If  IsObject(m_listboxobject ) = true and   IsNull(m_valuematch) = false Then
Dim m_Getallfield,m_arAllfield,m_val,m_actual
m_arAllfield = m_listboxobject.GetROProperty(“all items”)
m_arAllfield =Split (m_arAllfield,”;”)
For each element  in  m_arAllfield
If  m_valuematch = element  then ‘ compare the text
fn_Valuematch_for_weblistbx=true ”  if value match then  return true & exit thr loop
exit for
else
fn_Valuematch_for_weblistbx = false
end if
Next
else
print  “Error wrong Parameter for function fn_Valuematch_for_listbx”,”Check paramenter value”
End If
End Function
REM*************************************************************************************************************

Close All Browser

‘ This function Close All open Browser
REM *************************************************************************

REM this function close All open browser

Public Function   fn_CloseAllBrowser()
While Browser(“CreationTime:=0”).Exist
Browser(“CreationTime:=0”).Close
Wend
End Function

REM *************************************************************************

Rem another way to close browser here  this function terminate application ‘process

rem like internet explorer exe will close from system process

dim sApplicationExe

sApplicationExe = “iexplore.exe”

Rem call function to close application process

call fn_CloseApplication( sApplicationExe)

REM This function kill the given process exe  through task manager

Public Function  fn_CloseApplication( byval sApplicationExe)
Dim strComputer
Dim objWMIService
Dim colProcesses
Dim objProcess
strComputer = “.”
Set objWMIService = GetObject(“winmgmts:\\” & strComputer & “\root\cimv2”)

Set colProcesses = objWMIService.ExecQuery (“Select * from Win32_Process Where Name = ‘”&sApplicationExe&”‘”)
For Each objProcess in colProcesses
objProcess.Terminate()
Next
Set objWMIService = Nothing
Set colProcesses=Nothing
End Function

REM through this function you can close any application process