|
The RecordsAffected and Options are as previously explained, although you can
set the command type by using the CommandType property:
Set cmdAuthors = Server.CreateObject("ADODB.Command")
cmdAuthors.CommandText = "Authors"
cmdAuthors.CommandType = adCmdTable
Set rsAuthors = cmdAuthors.Execute
You can also set this on the Execute line itself - if you're not setting the
other arguments though, you must still include the commas for them:
Set rsAuthors = cmdAuthors.Execute(, , adCmdTable)
We'll look at the use of the Parameters argument a little later in the
chapter, when we deal with stored procedures and parameters.
Changing the Cursor Type
One important thing to note about recordsets that are returned from the
Execute method is that they have the default cursor and lock types. This means
they are forward-only, read-only recordsets. There's no way to change this using
the Execute method, but there is a way around this problem.
If you need to use a command, and require a different cursor or lock type,
then you should use the Open method of the Recordset, but use the Command as the
source of the Recordset. For example:
cmdAuthors.ActiveConnection = strConn
cmdAutohrs.CommandText = "Authors"
cmdAuthors.CommandType = adCmdTable
rsAuthors.Open cmdAuthors, , adOpenDynamic, adLockOptimistic
Notice that the connection details on the Open line have been omitted,
because the connection is set in the Command. The connection details could also
have been set in the ActiveConnection property of the Command before it is
opened.
Action Commands
For action commands, such as those that update data without returning any
records, the procedure is similar - you just leave off the bits that set the
recordset:
Set cmdUpdate = Server.CreateObject("ADODB.Command")
strSQL = "UPDATE Titles SET Price = Price * 1.10" & _
" WHERE Type = 'Business'"
cmdUpdate.ActiveConnection = strConn
cmdUpdate.CommandText = sSQL
cmdUpdate.CommandType = adCmdText
cmdUpdate.Execute , , adExecuteNoRecords
Notice that we set the command type in one place and then add any extra
options in the Execute line. This runs the UPDATE command and ensures that no
recordset is created.
Stored Procedures
The use of stored procedures is the one area where the
Command object comes into its own. A stored procedure (or stored query as it's
sometimes called) is a predefined SQL query stored on the database.
So why should we create and use a stored procedure instead of just creating a
SQL string on the fly, as in the example shown above? Well, there are several
reasons:
- A stored procedure is compiled by the database. This produces an execution
plan, so the database knows exactly what it's going to do. This makes the
execution of the procedure faster.
- Stored procedures are often cached by the database, thus making them faster
to run, as they don't have to be read from disk. Not all databases support this
caching mechanism - Microsoft Access doesn't, but SQL Server does.
- You can make your data a little bit more secure by specifying that your
database tables can be modified only by stored procedures. This means that
potentially dangerous SQL operations generated on the fly may not be performed.
- You avoid cluttering your ASP code with lengthy SQL statements. This makes
the ASP code easier to maintain.
- You can keep all of the SQL code together, on the server.
- You can use output parameters in a stored procedure, which allows you to
return both a recordset and other values.
As a general rule, stored procedures will nearly always be quicker than
their equivalent SQL statements.
To use a stored procedure you just put the name of the stored procedure as
the command text, and set the type accordingly. For example, consider the
previous example of updating book prices. If we created a stored procedure on
SQL Server, it might look like this:
CREATE PROCEDURE usp_UpdatePrices
AS
UPDATE Titles
SET Price = Price * 1.10
WHERE Type = 'Business'
For a Microsoft Access database you can create a simple update query to do the same task:
usp_UpdatePrices
To run this stored procedure from an ASP page, you'd simply use the following
code:
Set cmdUpdate = Server.CreateObject("ADODB.Command")
cmdUpdate.ActiveConnection = strConn
cmdUpdate.CommandText = "usp_UpdatePrices"
cmdUpdate.CommandType = adCmdStoredProc
cmdUpdate.Execute , , adExecuteNoRecords
This simply runs the stored procedure. No recordset is returned, because we
are only updating data - remember, there's no point creating a recordset unless
one is needed.
As it stands though, this procedure isn't very flexible since it only deals
with one book type. What would be better would be a procedure that allows us to
select the book type so we don't have to create a procedure for each book type.
And while we're at it, we might as well remove the fixed 10% update, and allow
this to be flexible too. So, how do we achieve this - simple, with parameters.
Parameters
Parameters to stored procedures are just like parameters (or arguments,
depending on your preferred term) to procedures and functions. These allow
values to be passed into a function, and then the function can use the value.
Stored procedures in SQL Server (and other databases, including Access) both
have this facility.
To allow the procedure to cope with multiple book types, and even allow the
user to specify the price increase (or decrease), we need to add some
parameters:
CREATE PROCEDURE usp_UpdatePrices
@Type Char(12),
@Percent Money
AS
UPDATE Titles
SET Price = Price * (1 + @Percent / 100)
WHERE Type = @Type
The stored procedure usp_UpdatePrices now takes two parameters:
- One for the book type (@Type).
- One for the percentage change in price (@Percent).
Just like a VBScript function, these parameters are variables. However,
unlike VBScript and other scripting languages where all the variables are
variants, SQL variables all have specific types (Char, Money, etc). They must
also follow the naming convention for SQL variables, which means they must start
with an @ symbol.
Notice that we've allowed the percentage to be supplied as a whole number
(for example 10 for 10%), instead of it's fractional value. This just makes the
procedure more intuitive to use.
The Parameters Collection
So now we have a stored procedure with parameters, how do we actually call
this from ADO? You've already seen how to call stored procedures without
parameters using the Command object, and that doesn't change. What changes is
the use of the Parameters collection.
The Parameters collection contains a Parameter object for each parameter in
the stored procedure. However, ADO doesn't automatically know what these
parameters are, so you have to create them, using the CreateParameter method,
which takes the following form:
Set Parameter = Command.CreateParameter (Name, [Type], [Direction],
[Size], [Value])
The arguments are: The RecordsAffected and Options are as previously explained, although you can
set the command type by using the CommandType property:
Set cmdAuthors = Server.CreateObject("ADODB.Command")
cmdAuthors.CommandText = "Authors"
cmdAuthors.CommandType = adCmdTable
Set rsAuthors = cmdAuthors.Execute
You can also set this on the Execute line itself - if you're not setting the
other arguments though, you must still include the commas for them:
Set rsAuthors = cmdAuthors.Execute(, , adCmdTable)
We'll look at the use of the Parameters argument a little later in the
chapter, when we deal with stored procedures and parameters.
Changing the Cursor Type
One important thing to note about recordsets that are returned from the
Execute method is that they have the default cursor and lock types. This means
they are forward-only, read-only recordsets. There's no way to change this using
the Execute method, but there is a way around this problem.
If you need to use a command, and require a different cursor or lock type,
then you should use the Open method of the Recordset, but use the Command as the
source of the Recordset. For example:
cmdAuthors.ActiveConnection = strConn
cmdAutohrs.CommandText = "Authors"
cmdAuthors.CommandType = adCmdTable
rsAuthors.Open cmdAuthors, , adOpenDynamic, adLockOptimistic
Notice that the connection details on the Open line have been omitted,
because the connection is set in the Command. The connection details could also
have been set in the ActiveConnection property of the Command before it is
opened.
Action Commands
For action commands, such as those that update data without returning any
records, the procedure is similar - you just leave off the bits that set the
recordset:
Set cmdUpdate = Server.CreateObject("ADODB.Command")
strSQL = "UPDATE Titles SET Price = Price * 1.10" & _
" WHERE Type = 'Business'"
cmdUpdate.ActiveConnection = strConn
cmdUpdate.CommandText = sSQL
cmdUpdate.CommandType = adCmdText
cmdUpdate.Execute , , adExecuteNoRecords
Notice that we set the command type in one place and then add any extra
options in the Execute line. This runs the UPDATE command and ensures that no
recordset is created.
Stored Procedures
The use of stored procedures is the one area where the
Command object comes into its own. A stored procedure (or stored query as it's
sometimes called) is a predefined SQL query stored on the database.
So why should we create and use a stored procedure instead of just creating a
SQL string on the fly, as in the example shown above? Well, there are several
reasons:
- A stored procedure is compiled by the database. This produces an execution
plan, so the database knows exactly what it's going to do. This makes the
execution of the procedure faster.
- Stored procedures are often cached by the database, thus making them faster
to run, as they don't have to be read from disk. Not all databases support this
caching mechanism - Microsoft Access doesn't, but SQL Server does.
- You can make your data a little bit more secure by specifying that your
database tables can be modified only by stored procedures. This means that
potentially dangerous SQL operations generated on the fly may not be performed.
- You avoid cluttering your ASP code with lengthy SQL statements. This makes
the ASP code easier to maintain.
- You can keep all of the SQL code together, on the server.
- You can use output parameters in a stored procedure, which allows you to
return both a recordset and other values.
As a general rule, stored procedures will nearly always be quicker than
their equivalent SQL statements.
To use a stored procedure you just put the name of the stored procedure as
the command text, and set the type accordingly. For example, consider the
previous example of updating book prices. If we created a stored procedure on
SQL Server, it might look like this:
CREATE PROCEDURE usp_UpdatePrices
AS
UPDATE Titles
SET Price = Price * 1.10
WHERE Type = 'Business'
For a Microsoft Access database you can create a simple update query to do the same task:
usp_UpdatePrices
To run this stored procedure from an ASP page, you'd simply use the following
code:
Set cmdUpdate = Server.CreateObject("ADODB.Command")
cmdUpdate.ActiveConnection = strConn
cmdUpdate.CommandText = "usp_UpdatePrices"
cmdUpdate.CommandType = adCmdStoredProc
cmdUpdate.Execute , , adExecuteNoRecords
This simply runs the stored procedure. No recordset is returned, because we
are only updating data - remember, there's no point creating a recordset unless
one is needed.
As it stands though, this procedure isn't very flexible since it only deals
with one book type. What would be better would be a procedure that allows us to
select the book type so we don't have to create a procedure for each book type.
And while we're at it, we might as well remove the fixed 10% update, and allow
this to be flexible too. So, how do we achieve this - simple, with parameters.
Parameters
Parameters to stored procedures are just like parameters (or arguments,
depending on your preferred term) to procedures and functions. These allow
values to be passed into a function, and then the function can use the value.
Stored procedures in SQL Server (and other databases, including Access) both
have this facility.
To allow the procedure to cope with multiple book types, and even allow the
user to specify the price increase (or decrease), we need to add some
parameters:
CREATE PROCEDURE usp_UpdatePrices
@Type Char(12),
@Percent Money
AS
UPDATE Titles
SET Price = Price * (1 + @Percent / 100)
WHERE Type = @Type
The stored procedure usp_UpdatePrices now takes two parameters:
- One for the book type (@Type).
- One for the percentage change in price (@Percent).
Just like a VBScript function, these parameters are variables. However,
unlike VBScript and other scripting languages where all the variables are
variants, SQL variables all have specific types (Char, Money, etc). They must
also follow the naming convention for SQL variables, which means they must start
with an @ symbol.
Notice that we've allowed the percentage to be supplied as a whole number
(for example 10 for 10%), instead of it's fractional value. This just makes the
procedure more intuitive to use.
The Parameters Collection
So now we have a stored procedure with parameters, how do we actually call
this from ADO? You've already seen how to call stored procedures without
parameters using the Command object, and that doesn't change. What changes is
the use of the Parameters collection.
The Parameters collection contains a Parameter object for each parameter in
the stored procedure. However, ADO doesn't automatically know what these
parameters are, so you have to create them, using the CreateParameter method,
which takes the following form:
Set Parameter = Command.CreateParameter (Name, [Type], [Direction],
[Size], [Value])
The arguments are: | 
.NET
ASP (16) 
Contains 1 or more replies by the Author of this Article.
Contains 1 or more replies by 
