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.
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