Home

ASP ADVANCED

This final chapter on the ActiveX Data Objects covers how to use the Command object. First You learn how to use the command object to execute SQL stored procedures with parameters. The remainder of this chapter is devoted to two sample applications of the ADO. First you learn how to create an advanced feedback page for your Web site. Then you learn how to create a custom password-protection system.

The Command object represents a command (for example, a SQL query or a SQL stored procedure). Chapter "ActiveX Data Objects," and Chapter "Working with Recordsets," respectively, show how to use the Execute method of the Connection object and the Open method of the Recordset object to execute a command string. Consider these two examples:

RS.Open "SELECT * FROM MyTable", MyConn

MyConn.Execute "UPDATE MyTable SET MyColumn 'Hello' "

Both of these examples use a SQL command string. In the first example, the command string is used to open the Recordset. In the second example, the command string is executed.

Instead of using a command string, you can use the command object. The Command object can be used to explicitly represent a command. You can use an instance of the Command object to return a Recordset or to execute a SQL command that doesn't return a Recordset. Here's an example:

<%
Set MyConn=Server.CreateObject("ADODB.Connection")
Set MyCommand=Server.CreateObject("ADODB.Command")
MyConn.Open "FILEDSN=d:\Program Files\common Files\ODBC\Data Sources\MyData.dsn"
Set MyCommand.ActiveConnection=MyConn
MyCommand.CommandText="UPDATE MyTable SET MyColumn='Hello' "
MyCommand.CommandType=adCMDText
MyCommand.Execute
MyConn.Close
%>

In this example, an instance of the command object is created. Next, the ActiveConnection property associates the Command with an open connection. (This is accomplished with the set statement, because you're assigning an object.) The CommandText property specifies which SQL statement will be executed. The CommandType property indicates that the command is a textual definition of a command. Finally, the Execute method is called to execute the command.

In this example, the command object isn't used to return a Recordset. However, there are two ways in which you can use the Command object to return a Recordset. This is the first way:

<%
Set MyConn=Server.CreateObject("ADODB.Connection")
Set MyCommand=Server.CreateObject("ADODB.Command")
MyConn.Open "FILEDSN=d:\Program Files\Common Files\ODBC\Data Sources\MyData.dsn"
Set MyCommand.ActiveConnection=MyConn
MyCommand.CommandType=adCMDText
MyCommand.CommandText="SELECT * FROM MyTable"
Set RS=MyCommand.Execute( )
RS.Close
MyConn.Close
%>

In this script, the Execute( ) method of the Command object is used to return a Recordset. Notice that parentheses are used because the method is being used to return results. After you have created a new instance of a Recordset with the Command object, you can manipulate it in the normal ways.

You can also use the Command object with a preexisting Recordset, like this:

<%
Set MyConn=Server.CreateObject("ADODB.Connection")
Set MyCommand=Server.CreateObject("ADODB.Command")
Set RS=Server.CreateObject("ADODB.RecordSet")
MyConn.Open "FILEDSN=d:\Program Files\Common Files\ODBC\Data Sources\MyData.dsn"
Set MyCommand.ActiveConnection=MyConn
MyCommand.CommandText="SELECT * FROM MyTable"
MyCommand.CommandType=adCMDText
RS.Open MyCommand,,adOpenStatic,adLockOptimistic
RS.Close
MyConn.Close
%>

The advantage of passing a Command object to a preexisting Recordset is that you can specify the Recordset's cursor and locking type. In this example, the Command object is used to open a Recordset that uses a static cursor and optimistic locking. Notice that you don't include a reference to the Connection object when using the open method with a Command object. The connection is determined by the Command object instead.

These examples illustrate how you can use the Command object. However, they don't illustrate why you should use a Command object. Why should you explicitly create a Command object rather than use a command string?

Using a Command object has one main advantage. You can use the Command object with a SQL stored procedure.

Using the Command Object to Call a Stored Procedure

Suppose you want to retrieve all the records in a table named MyTable and display them in an Active Server Page. Furthermore, suppose that you want to retrieve the records from this table in the most efficient way possible. In that case, you should use a stored procedure.

To create a new stored procedure, launch ISQL/w from the Microsoft SQL Server program group. Next, enter the following text in the query window:

CREATE PROCEDURE sp_myproc AS
SELECT * FROM MyTable

Click the Execute Query button (it shows a green triangle) and the stored procedure will be created. The new stored procedure is named sp_myproc.

To call sp_myproc from an Active Server Page; you can use an instance of the command object. Here's an example:

This script displays all the records in the table named MyTable. The records are retrieved by calling the sp_myproc stored procedure. When you use a Command object to call a stored procedure, you should set its CommandType property to adCMDStoredProc. You use the CommandText property to specify the stored procedure to call.

Using Return Status Values With the Command Oblect

You can use the Command object to retrieve the return status value from a stored procedure. For example, suppose you want a count of the number of records in a table. The absolutely most efficient way to do this is to create a stored procedure, as in the following example:

CREATE PROCEDURE sp_CountMyTable AS
RETURN(SELECT COUNT( * ) FROM MyTable)

This stored procedure returns the number of records in the table named MyTable. The SQL COUNT( ) aggregate function counts the number of records in the table. The RETURN statement returns this count.

To retrieve the return status value of a stored procedure, you must create a parameter for the Command object. The Command object has a collection named Parameters. The Parameters collection is a collection of Parameter objects.

You create a parameter by using the CreateParameter( ) method of the command object. Next, you use the Append method of the Command object to append the parameter to the Command object's parameters collection. Here's an example:

<%
Set MyConn=Server.CreateObject("ADODB.Connection")
Set MyCommand=Server.CreateObject("ADODB.Command")
MyConn.Open "FILEDSN=d:\Program Files\Common Files\ODBC\Data Sources\MyData.dsn"
Set MyCommand.ActiveConnection=MyConn
MyCommand.CommandType=adCMDStoredProc
MyCommand.CommandText="sp_CountMyTable"
Set MyParam=MyCommand.CreateParameter("RetVal",adlnteger,adParamReturnValue)
MyCommand.Parameters.Append MyParam
MyCommand.Execute
%>
There are <%=MyCommand("RetVal")%> records in MyTable.
<%
MyConn.Close
%>

In this script, a new Parameter object is created by using the CreateParameter( ) method. The CreateParameter( ) method has three arguments in this example:

The first argument specifies a name for the new parameter.
The second argument indicates the datatype.
Finally, the last argument specifies the type of the parameter. In this example, the constant adParamReturnValue indicates that the parameter is a return parameter.

After any new parameter is created, it must be appended to the Parameters collection of the Command object. The Append method is used to add a new parameter to this collection.

After the Command executes, the value of the parameter can be retrieved. Because the parameter is a member of the Command object's Parameters collection, the value of the parameter can be returned by using MyCommand ("RetVal"). Actually, you can also retrieve this value by using any of the following expressions:

MyCommand("RetVal")

MyCommand(0)

MyCommand.Parameters("RetVal")

MyCommand.Parameters(0)

MyCommand.Parameters.Item("RetVal")

MyCommand.Parameters.Item(0)

All these methods of retrieving the value of a parameter work because a parameter is part of a Command object's Parameters collection. Notice that, as with all collections, you can specify a parameter either by name or by ordinal number.

Using Output Parameters with the Command Object

The example in the preceding section illustrates how you can retrieve a return status value. The procedure for retrieving output parameters from a stored procedure is very similar. The advantage of using output parameters is that there can be more than one of them. Also, an output parameter can be of any datatype.

Imagine that you have a table named WebUsers that contains the list of registered user names for your Web site. This table has a single column named UserName. Now suppose you want to retrieve both the alphabetically highest and lowest name from this table. You can use the following stored procedure to do this:

CREATE PROCEDURE sp_HighAndLow
(@HighUser VARCHAR(30) OUTPUT, @LowUser VARCHAR(30) OUTPUT)
AS
SELECT @HighUser=MAX(UserName) FROM WebUsers
SELECT @LowUser=MIN(UserName) FROM WebUsers

This stored procedure has two output parameters named @Highuser and @LowUser. @Highuser contains the name of the user with the alphabetically highest name (for example, Zeek Zimmerman). @LOWUser contains the name of the user with the alphabetically lowest name (for example, Anne Arnold).

To call this stored procedure in an Active Server Page, you could use the following script:

<%
Set MyConn=Server.CreateObject("ADODB.Connection")
Set MyCommand=Server.CreateObject("ADODB.Command")
MyConn.Open "FILEDSN=d:\Program Files\Common Files\ODBC\Data Sources\MyData.dsn"
Set MyCommand.ActiveConnection=MyConn
MyCommand.CommandType=adCMDStoredProc
MyCommand.CommandText="sp_HighandLow"
Set MyFirstParam=MyCommand.CreateParameter("HighUser",adVarChar,adParamOutput, 30)
MyCommand.Parameters.Append MyFirstParam
Set MySecondParam=MyCommand.CreateParameter("LowUser",adVarChar,adParamOutput, 30)
MyCommand.Parameters.Append MySecondParam
MyCommand.Execute
%>
<P>The person with the alphabetically highest name is
<%=MyCommand("HighUser")%>
<P>The person with the alphabetically lowest name is
<%=MyCommand("LowUser")%>
<%
MyConn.Close
%>

The structure of this Script is very similar to the preceding one. In this script, two Parameter objects are created with the CreateParameter( ) method. They're both created with VARCHAR data types. To indicate that they're output parameters, the constant adParamOutput is used. Finally, the maximum size of each Parameter, 30, is included in the CreateParameter( ) method. When you create parameters with variable-size data such as VARCHAR or CHAR, you must supply a maximum-size argument.

Using Input Parameters with the Command Object

One more type of parameter has yet to be discussed. A SQL stored procedure can accept input parameters. Input parameters enable you to pass data to a stored procedure.

For example, imagine you have a table containing user names and passwords. Now suppose you want to create a stored procedure that checks passwords. You could check whether a user has supplied a valid password with the following stored procedure:

CREATE PROCEDURE sp_CheckPass
(@CHKName VARCHAR(30),@CHKPass VARCHAR(30),@ISValid CHAR(4) OUTPUT)
AS
IF EXISTS (SELECT UserName FROM WebUsers
WHERE UserName=@CHKName AND UserPass=@CHKPass)
SELECT @ISValid="Good"
ELSE
SELECT @ISValid="Bad"

This stored procedure accepts two input parameters. The @CHKName input parameter passes a user name to the procedure. The @CHKpass input parameter inputs a password to the procedure. If a user with the specified password exists, the output parameter returns the Value Good. Otherwise, the value Bad is returned.

The method for using an input parameter is very similar to the method for using output parameters. The crucial difference is that an input parameter must be assigned a value before the Command is executed. Here's an example:

<%
Set MyConn=Server.CreateObject("ADODB.Connection")
Set MyCommand=Server.CreateObject("ADOOB.Command")
MyConn.Open "FILEDSN=d:\Program Files\Common Files\ODBC\Data Sources\MyData.dsn"
Set MyCommand.ActiveConnection=MyConn
MyCommand.CommandType=adCMDStoredProc
MyCommand.CommandText="sp_CheckPass"
Set MyFirstParam=MyCommand.CreateParameter("UserName", adVarChar, adParamInput,30)
MyCommand.Parameters.Append MyFirstParam
Set MySecondParam=MyCommand.CreateParameter("UserPass", adVarChar, adParamInput,30)
MyCommand.Parameters.Append MySecondParam
Set MyThirdParam=MyCommand.CreateParameter("RetValue", adChar, adParamOutPut,4)
MyCommand.Parameters.Append MyThirdParam
MyCommand("UserName")="Bill Gates"
MyCommand("UserPass")="Billions"
MyCommand.Execute
%>
The Password is <%=MyCommand("RetValue")%>.
<%
MyConn.Close
%>

In this example, the name Bill Gates with the password Billions is passed to the stored procedure. If this name-and-password combination exists in the WebUsers table, the password is reported as Good. Otherwise, the password is reported as Bad.

In this script, the two input parameters are indicated by using the constant adParamInput. Notice that both of these input parameters are assigned a value before the Command is executed.