|
27. 8. 1 ADO Command Object
Command Object
The Command object is used to execute a query against a data
source. The query can perform actions like creating, adding,
retrieving, deleting or updating records. The Command object
can also use stored queries and procedures with parameters.
Syntax
objectname.method
objectname.property
Methods
Method Description
Cancel
Cancels an execution of a method
CreateParameter
Creates a new Parameter object
Execute
Executes the query, SQL statement or procedure in the
CommandText
property
Properties
Property Description
ActiveConnection
Sets or returns a definition for a connection if the connection
is closed,
or the current Connection object if the connection is open
CommandText
Sets or returns a provider command
CommandTimeout
Sets or returns the number of seconds to wait while attempting
to
execute a command
CommandType
Sets or returns the type of a Command object
Name
Sets or returns the name of a Command object
Prepared
Sets or returns a Boolean value that, if set to True, indicates
that the
command should save a prepared version of the query before the
first
execution
State
Returns a value that describes if the Command object is open,
closed,
connecting, executing or retrieving data
Collections
Collection Description
Parameters Contains all the Parameter objects of a Command
Object
Properties Contains all the Property objects of a Command Object
Methods
The Cancel Method
The Cancel method cancels the execution of a method call.
Syntax
object.Cancel
The Cancel method terminates different tasks for each object.
The table below shows what task is terminated when the Cancel
method is called:
Object Terminated method
Command Execute
Connection Execute or Open
Record CopyRecord, DeleteRecord, MoveRecord, or Open
Recordset Open
Stream Open
The CreateParameter Method
The CreateParameter method creates a new Parameter object with
specified properties like name, type, direction, size, and
value.
Syntax
Set parameter = commobjectname.CreateParameter (name,
type, direction, size, value)
Part Description
name Optional. The name of the Parameter object
type Optional. The
data type
of the Parameter object
direction Optional. The
type
of Parameter object
size Optional. The max length for the parameter value (in
characters or bytes)
value Optional. The value of the Parameter object
The Execute Method
The Execute method executes the query, SQL statement or
procedure in the CommandText property.
Syntax
Set rs = command.Execute(recaffected,
parameters, options)
Part Description
recaffected Optional. Returns the number of records affected
parameters Optional. Parameter values passed with an SQL
statement
options Optional. Sets how the provider should evaluate the
CommandText property.
Can be one or more
CommandTypeEnum
or ExecuteOptionEnum
values
The ActiveConnection Property
The ActiveConnection property tells which Connection object
the Command object belongs to.
If the connection is closed, it sets or returns a definition for
a connection. If the connection is open it sets or returns the
current Connection object.
Syntax
commandobj.ActiveConnection
The CommandText Property
The CommandText property sets or returns a string that
contains a provider command, like a SQL statement, a table
name, a relative URL, or a stored procedure call.
Syntax
commandobj.CommandText
The CommandTimeout Property
The CommandTimeout property sets or returns the number of
seconds to wait while attempting to execute a command, before
canceling the attempt and generate an error. Default is 30.
Syntax
object.CommandTimeout
The CommandType Property
The CommandType property sets or returns the type of a Command
object. Can be one or more
CommandTypeEnum
values.
This property can be used to optimize evaluation of the
CommandText property.
Syntax
commandobj.CommandType
The Name Property
The Name property sets or returns the name of a Command,
Property, Field, or Parameter object.
Syntax
object.Name
The Prepared Property
The Prepared property sets or returns a Boolean value that, if
set to True, indicates that the command should save a prepared
version of the query specified in the CommandText property
before execution. This could slow down the command's first
execution, but after the first execution the provider will use
the compiled version, which results in a faster execution.
Syntax
commandobj.Prepared = true_or_false
The State Property
The State property returns a value that describes if the
object is open, closed, connecting, executing or retrieving
data. The value returns an
ObjectStateEnum
value. Default is adStateClosed.
The State property can have a combination of values. If a
statement is executing, this property will have a combined
value of adStateOpen and adStateExecuting.
Syntax
object.State
27. 8. 2 ADO Connection Object
ref to 3. ADO Database Connection
27. 8. 3 ADO Error Object
Error Object
The Error object contains details about data access errors that
have been generated during a single operation.
ADO generates one Error object for each error. Each Error
object contains details of the specific error, and are stored
in the Errors collection. To access the errors, you must refer
to a specific connection.
To loop through the Errors collection:
For Each ErrObj in ConnObj
Response.Write(ErrObj.Description & "<br />")
Next
Syntax
objectname.property
Properties
Property Description
Description
Returns an error description
HelpContext
Returns a context ID from a help file
HelpFile
Returns the full path to a help file
NativeError
Returns an error code
Number
Returns a unique number that identifies an Error object
Source
Returns the name of an object or application that generated the
error
SQLState
Returns a SQL error code
The Description Property
The Description property returns an error description.
Syntax
errobj.Description
Example
<%
For Each err in Conn.Errors
Response.Write(err.Description & "<br />")
Next
%>
The HelpContext Property
The HelpContext property returns a context ID from a Help
file.
If a help file is specified in the HelpFile property, the
HelpContext property is used to automatically display the Help
topic it identifies.
Syntax
errobj.HelpContext
Example
<%
For each err in conn.Errors
Response.Write("<p>HelpContext= ")
Response.Write(err.HelpContext & "</p>")
next
%>
The HelpFile Property
The HelpFile property returns the full path to a help file.
If a help file is specified in the HelpFile property, the
HelpContext property is used to automatically display the Help
topic it identifies.
Syntax
errobj.HelpFile
Example
<%
For each err in conn.Errors
Response.Write("<p>HelpFile= ")
Response.Write(err.HelpFile & "</p>")
next
%>
The NativeError Property
The NativeError property returns an error code.
Syntax
errobj.NativeError
Example
<%
For each err in conn.Errors
Response.Write("<p>NativeError= ")
Response.Write(err.NativeError & "</p>")
next
%>
The Number Property
The Number property returns a unique number that identifies an
Error object. The number may be one of these
values.
Syntax
errobj.Number
Example
<%
For each err in conn.Errors
Response.Write("<p>Error number: ")
Response.Write(err.Number & "</p>")
next
%>
The Source Property
The Source property returns the name of the object or
application that generated the error.
Syntax
errobj.Source
Example
<%
For each err in conn.Errors
Response.Write("<p>Source: ")
Response.Write(err.Source & "</p>")
next
%>
The SQLState Property
The SQLState property returns a 5 character SQL error code.
Syntax
errobj.SQLState
Example
<%
For each err in conn.Errors
Response.Write("<p>SQL State: ")
Response.Write(err.SQLState & "</p>")
next
%>
27. 8. 4 ADO Field Object
Field Object
The Field object contains information about a column in a
Recordset. There is one Field object for each column in the
Recordset.
Syntax
objectname.method
objectname.property
Methods
Method Description
AppendChunk
Appends data to a Field object
GetChunk
Returns a part or all of a Field object's long binary or
character data
Properties
Property Description
ActualSize
Returns the actual length of a field's value
Attributes
Sets or returns the attributes of a Field object
DefinedSize
Returns the defined size of a field
Name
Sets or returns a the name of a Field object
NumericScale
Sets or returns the number of decimal places allowed for numeric
values in a
Field object
OriginalValue
Returns the original value of a field
Precision
Sets or returns the maximum number of digits allowed when
representing values
in a numeric Field object
Status
Returns the status of a Field object
Type
Sets or returns the type of a Field object
UnderlyingValue
Returns the database value of a field
Value
Sets or returns the value of a Field object
Methods
The AppendChunk Method
The AppendChunk method is used to append data to an object to
fill it with long binary or character data.
This method can divide long values into small portions, so if
the system memory is limited and can't deal with long values,
this method can be used.
Field object
You can use the AppendChunk method for a field if the Attributes
property of a Field object is set to adFldLong.
Note that this method does not work on Field objects of a
Record object.
Parameter object
You can use the AppendChunk method for a parameter if the
Attributes property of a Parameter object is set to adFldLong.
Syntax
object.AppendChunk data
Part Description
object A Field or Parameter object
data The data to append
The GetChunk Method
The GetChunk method returns a part or all of a Field object's
long binary or character data. The data returned is assigned
to a variable.
This method can divide long values into small portions, so if
the system memory is limited and can't deal with long values,
this method can be used.
When the GetChunk method is called, it starts to retrieve data
from where the previous GetChunk call ended, as long as you
stay on the same field in the current record.
You can use the GetChunk method for a field if the Attributes
property of a Field object is set to adFldLong.
Note: This method does not work
on Field objects of a Record object.
Syntax
variable = field.GetChunk(size)
Part Description
size The number of bytes or characters to be returned
Properties
The ActualSize Property
The ActualSize property returns the actual length of a field's
value.
The Attributes Property
The Attributes property sets or returns the attributes of an
object.
Note: When setting multiple attributes, it is possible to
sum the values.
Syntax
object.Attributes
Object Description of the Attributes Property
Connection The Attributes property is read/write. The value can
be one or more
XactAttributeEnum
values. Default is 0
Parameter The Attributes property is read/write. The value can
be one or more
ParameterAttributesEnum
values. Default is adParamSigned
Field The Attributes property is normally read-only. The value
can be one or more
FieldAttributeEnum
values
Property The Attributes property is read-only. The value can be
one or more
PropertyAttributesEnum
values
The DefinedSize Property
The DefinedSize property returns the defined size of a field
(in bytes).
The Name Property
The Name property sets or returns the name of a Command,
Property, Field, or Parameter object.
Syntax
object.Name
The NumericScale Property
The NumericScale property sets or returns how many digits to
the right of the decimal point that will be used to represent
numeric values in an object.
Syntax
object.NumericScale
The OriginalValue Property
The OriginalValue property returns the original value of a
field, before any changes were made.
There are two different update modes:
· Immediate update mode - returns the field value that
existed before the last Update method call
· Batch update mode - returns the field value that existed
before the last UpdateBatch method call
Syntax
fieldobj.OriginalValue
The Precision Property
The Precision property sets or returns the maximum number of
digits allowed when representing values in a numeric Parameter
or Field object.
Syntax
objectname.Precision
The Status Property
The Status property returns the status of a Field object.
It can be a
FieldStatusEnum
value. Default is adFieldOK.
This property always returns adFieldOK for fields of a Recordset
object.
If any updates fail then an error is returned and the Status
property indicates the combined values of the operation and
error status code. The Status property for each Field can be
used to determine why the Field was not added, modified, or
deleted. Status is only meaningfully exposed on the
Record.Fields collection and not the Recordset.Fields
collection.
Syntax
fieldobj.Status
The Type Property
The Type property sets or returns the type of an object.
Can be one of the
DataTypeEnum
values.
· Parameter object - This property is read/write
· Field object - This property is read/write, with one
exception; for new Field objects that are added to the Fields
collection of a Record, this property is read-only before the
Value property has been set and before the provider has called
the Update method of the Fields collection
· Property object - This property is read-only
Syntax
objectname.Type
The UnderlyingValue Property
The UnderlyingValue property returns the database value of a
field.
Tip: This property can be used with the OriginalValue
property to prevent update conflicts.
Syntax
fieldobj.UnderlyingValue
The Value Property
The Value property sets or returns the value of an object.
· Parameter object - This property sets or returns
parameter values
· Field object - This property sets or returns data. Note
that for new Field objects that are added to the Fields
collection of a Record, this property must be set before other
Field properties
· Property object - This property sets or returns property
settings
Syntax
objectname.Value
27. 8. 5 ADO Parameter Object
Parameter Object
The Parameter object contains information about a parameter used
in a stored procedure or query.
Syntax
objectname.method
objectname.property
Methods
Method Description
AppendChunk
Appends data to a Parameter object
Delete
Deletes an object from the Parameters Collection
Properties
Property Description
Attributes
Sets or returns the attributes of a Parameter object
Direction
Sets or returns how a parameter is passed to or from a procedure
Name
Sets or returns a the name of a Parameter object
NumericScale
Sets or returns the number of decimal places allowed for numeric
values in a
Parameter object
Precision
Sets or returns the maximum number of digits allowed when
representing values
in a Parameter object
Size
Sets or returns the maximum size of a value in a Parameter
object
Type
Sets or returns the type of a Parameter object
Value
Sets or returns the value of a Parameter object
Methods
The AppendChunk Method
The AppendChunk method is used to append data to an object to
fill it with long binary or character data.
This method can divide long values into small portions, so if
the system memory is limited and can't deal with long values,
this method can be used.
Field object
You can use the AppendChunk method for a field if the Attributes
property of a Field object is set to adFldLong.
Note that this method does not work on Field objects of a
Record object.
Parameter object
You can use the AppendChunk method for a parameter if the
Attributes property of a Parameter object is set to adFldLong.
Syntax
object.AppendChunk data
Part Description
object A Field or Parameter object
data The data to append
The Delete Method
The Delete method removes an object from the Parameters
collection.
Note: This method works only with the Parameters collection
of a Command object.
Syntax
parameterobj.Delete index
Part Description
index The name or index of the object you want to delete
Properties
The Attributes Property
The Attributes property sets or returns the attributes of an
object.
Note: When setting multiple attributes, it is possible to
sum the values.
Syntax
object.Attributes
Object Description of the Attributes Property
Connection The Attributes property is read/write. The value can
be one or more
XactAttributeEnum
values. Default is 0
Parameter The Attributes property is read/write. The value can
be one or more
ParameterAttributesEnum
values. Default is adParamSigned
Field The Attributes property is normally read-only. The value
can be one or more
FieldAttributeEnum
values
Property The Attributes property is read-only. The value can be
one or more
PropertyAttributesEnum
values
The Direction Property
The Direction property sets or returns how a parameter is
passed to or from a procedure. Is it an input parameter, an
output parameter, an input and an output parameter, or a
return value from a procedure?
Can take a
ParameterDirectionEnum
value.
Syntax
parameterobj.Direction
The Name Property
The Name property sets or returns the name of a Command,
Property, Field, or Parameter object.
Syntax
object.Name
The NumericScale Property
The NumericScale property sets or returns how many digits to
the right of the decimal point that will be used to represent
numeric values in an object.
Syntax
object.NumericScale
The Precision Property
The Precision property sets or returns the maximum number of
digits allowed when representing values in a numeric Parameter
or Field object.
Syntax
objectname.Precision
The Size Property
The Size property sets or returns the maximum size, in bytes
or characters, of a value in a Parameter object.
Syntax
parameterobj.Size
The Type Property
The Type property sets or returns the type of an object.
Can be one of the
DataTypeEnum
values.
· Parameter object - This property is read/write
|