If a SQL query requires parameters then it can be supplied
by adding them to the Command objects' parameters collection or
by creating them on the fly as we execute the SQL query.
myCommand.Execute RecordsAffected, Parameters
An array of parameters can be passed to the Parameters object
while the query is being executed.
myCommand.Execute Array (parameter1, parameter2 etc...)
Instead of passing parameters on the fly we can hold the
parameters in the parameters collection. For this the parameter
collection has methods like Append, Delete, Refresh, and
properties like Count, Item etc. To add a new parameter an
instance of the Parameter is made and its property values are
set and finally the Append method of the Command object is
called to append the parameters to it like it is done in line
5.
The parameters are not limited to stored procedures, they
can also be used in SQL statements in Access where parameters
are indicated by being placed in square brackets within the
statement. This can be used as an alternate for SQL statements
in which the 'where' clause changes dynamically.
We need to specify Type and Size for the parameters used in
the Parameter object. For String values it is the size and for
numeric values the precision needs to be specified.
There are two kinds of properties: Built-in properties
and Dynamic properties. The Built-in properties are those
defined by ADO for objects as they are created. These are part
of the object itself and can be used to set or get some property
to the object. These are available irrespective of the provider
being used.
The Dynamic properties are additional properties specifically
provided by the provider.
The Errors collection holds all of the Error objects for a
specific Connection. Generally, if an ADO operation encounters
errors, one or more Error objects are automatically added to
the Errors collection. More detailed error messages can be
obtained from these error objects instead of vague Runtime
errors. The Errors collection has a clear method to clear all
the errors. It also has two properties. The count property
returns the number of error objects in the collection and the
item property is used to retrieve the contents of the error
objects in the collection. Two types of error objects are
thrown depending on the type of error. Errors are returned
by the data source whereas Err objects are created at compile
time or runtime by VBScript or by an automation object like
ADO.
Apart from critical errors that stop execution some properties
and methods return warnings to the Errors collection . Warnings
don't stop execution, but that could be due to small problems
like data conversion, which could affect the precision of the
result.
The Error object has properties like Description for detailed
description , Source for the source of the error, Number for
the ADO error code number etc.
Click here for code example 6.
Bottomline:
Ado is the easiest way to provide dynamic content on
the Internet. It is best suited for small and medium
sized web sites as the development time and costs are
minimum compared to other languages. Moreover its
default support on Windows NT makes it an ideal choice
for companies intending to do business on the net. ASP
and ADO seamlessly combine with other Microsoft tools
like the Siteserver, MTS and Commerce server making it
scaleable and robust.