Use the command object
Command object represents an command (for example, a SQL query or a SQL stored procedure). The OPEN method for the EXECUTE method and record set object of the connection object can be used to perform a command string. Consider the following two examples:
Rs.open "Select * from myTable", MyConn
MyConn.execute "Update MyTable Set MyColumn = 'Hello'"
Both examples use the SQL command string. In the first example, open the recordset with a command string. In the second example, the command string is executed to update the data. Instead of the command string, you can use the command object. Command objects can be used to represent a specialized command. You can return a recordset with an instance of the command object or perform a SQL command that does not return record set. Here is 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 links the command and an open connection. (Complete this task with a SET statement because you are assigning an object.) CommandText property Specifies what SQL statement to do. CommandType property indicates that the command is a text definition of a command. Finally, call the Execute method to execute this command. In this example, the command object is used to return a recordset. However, using the command object, you can return to the record set through two channels. 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. Note the use of quotation marks because the method is used to return the result. After you create an instance of the record set object with a command object, you can operate it in a standard way. You can also use a command object with an already existing 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.commandtype = adcmdtext
Mycommand.commandtext = "SELECT * from myTable"
Rs.Open MyCommand, AdopenStatic, AdlockOptimstic Rs.close MyConn.close%>
The advantage of opening an existing record set object with a command object is that you can specify the cursors and lock types of records. In this example, the command object is used to open a recordset using a static cursor and an AdlockOptimistic lock. Note that if you use the command object, you don't need to specify the connection object, and the command object determines which connection. The above example tells how to use the command object. But not tell why the connection object is used. Why do you want to create a command object, instead of using the command string? There is a major advantage to use the command object. You can use the SQL stored procedure with the command object. Is there any advantage when using the SQL stored procedure When you build a site, convert as many SQL commands to the stored procedure is a good idea. Instead of performing SQL queries inside the ASP page, it is better to call the stored procedure containing these queries. There are many reasons for using the SQL stored procedure: ■ SQL stored procedure is much more fast than SQL command text. When a SQL statement is included in the stored procedure, the server does not have to analyze and compile it every time it executes it. ■ You can call the same stored procedure in multiple web pages. This makes your site easy to maintain. If a SQL statement needs to be some changes, you can do it once. ■ You can use Transact-SQL's powerful features during storage. A SQL stored procedure can contain multiple SQL statements. You can use variables and conditions. This means you can build a very complex query with a stored procedure to update the database in a very complex manner. ■ Finally, this may be the most important, and parameters can be used during the stored procedure. You can transfer and return parameters. You can also get a return value (from the SQL RETURN statement). In short, the stored procedure can be used in the stored procedure. The stored procedure has great advantages
Use the command object to call the stored procedure
Suppose you want to remove all the records in the table MyTable and display them in an ASP page. Moreover, suppose you want to take records from the table as efficiently as possible. In this case, you should use the stored procedure. To establish a new stored procedure, start ISQL / W from the Microsoft SQL Sever program group. Then enter the following text in the query window:
Create Procedure SP_MYPORC AS
Select * from myTable
Click the Execute Query button (look like a green triangle) to create this stored procedure. The name of this stored procedure is sp_myproc. To call SP_MYPROC in an ASP page, you can use an instance of the command object. Here is 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_myproc"
SET RS = mycommand.execute () while not rs.eof
Response.write ("
" & rs ("mycolumn")))
Rs.movenext wend rs.close myconn.close%>
This script is removed by calling the stored procedure sp_myproc, which shows all records in the table myTable. When you call the stored procedure with a command object, you should set the CommandType property of the command object to AdcmdStoredProc. The CommandText property is used to specify the stored procedure to be called.
Use the return status value
You can use a command object from a stored procedure to return status values. For example, suppose you want to count the total number of records in a table. The highest efficiency is to create a stored procedure, as shown in the following example:
Create Procedure SP_COUNTMYTABE AS
Return (Select Count (*) from myTable)
This stored procedure returns the total number of records in Table MyTable. SQL set function count () calculates the number of records in the table. Return statement returns this number. To get a return status value of a stored procedure, you must establish a parameter for the command object. The command object has a collection called Parameters and is a collection of parameter objects. You can create a parameter with the createParameter () method of the command object. Next, add this parameter into the parameters collection of the command object with the APPEND method. Here is 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", Adinteger, AdparamReturnValue) MyCommand.Parameters.Append myparam mycommand.execute%>
There Are <% = MyCommand ("RetVal")%> Records in MyTable.
<% Myconn.close%>
In this script, a parameter object is established with the CreateParameter () method. There are three parameters in the createparameter () method in this example:
The first parameter specifies a name for the new parameter. The second parameter specifies the data type. Finally, the third parameter specifies the type of new parameter. In this example, constant ADPARAMRETURNVALUE indicates that the parameter is a return parameter. After establishing any new parameters, it must be added to the parameters collection of the command object. The Append method is used to add new parameters to this collection.
After the command is executed, the value of the parameter can be taken out. Because this parameter is one of the parameters collection of the command object, the value of this parameter can be returned with MyCommand ("RetVal"). In fact, this value can be obtained by any of the following expressions:
MyCommand ("RetVal")
Mycommand (0)
MyCommand.Parameters ("RetVal")
Mycommand.parameters (0)
Mycommand.Parameters.Item ("RetVal")
Mycommand.parameters.Item (0)
These methods can be used to remove a value of a parameter because a parameter is part of the parameter set of command objects. Note that you can specify a parameter through your name or sequence number.
Use output parameters
The example of the previous section demonstrates how to get the return status value. Removing the output parameter value from a stored procedure is very similar. The advantage of using the output parameters is that the output parameter can have one or more. Moreover, the output parameter can be any data type. For example, there is a table WebUsers saved the name of the user registered on your site. This table has only one field username. Now suppose you want to take out the first and final username in alphabetical order. You can use the following stored procedures:
Create Procedure SP_highandlow
(@Highuser varchar (30) Output, @ @ inguser varchar (30) OUTPUT
AS
SELECT @ highuser = max (username) from WebUsers
SELECT @ Lowuser = min (username) from WebUsers
This stored procedure has two output parameters, @ highuser and @lowusers. @Highuseer contains the last username (for example, ZEEK Zimmerman) by alphabetical order. @Lowuser contains the username (for example, Anne Arnold) in alphabetical sequence. To call this stored procedure in the ASP page, you can 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%>
the person with the alphabetically highest name is
<% = MyCommand ("HIGHUSER")%>
The person with the alphabetically lowest name is
<% = MyCommand ("Lowuser")%>
<% Myconn.close%>
The structure of this script is very similar to the previous. In this script, two parameter objects are created with the CreateParameter () method, both of which are defined as a VARCHAR. In order to indicate that they are output parameters, constant ADPARAMOUTPUT is used. Finally, the maximum length of each parameter is specified in the CreateParameter () method, 30. When your established parameters are variable length data types, such as VARCHAR, you must specify a maximum length.
Use input parameters
Another parameter is now discussed. The SQL stored procedure can receive input parameters. Enter parameters allow you to pass the data to the stored procedure. For example, suppose there is a table to save the username and password. Suppose you want to build a stored procedure for checking your password. With this stored procedure below, you can check if a user has entered a legal password.
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 @ isvaid = "good"
Else
SELECT @ isvalid = "bad"
This stored procedure receives two input parameters. Enter parameter @chkaname passed a username to the storage process. @CHKPASS passes a password to the storage process. If there is a user owns the specified password, the output parameter will return "good", otherwise, return "Bad".
The method using the input parameters is very similar to the method of using the output parameters. The key difference is that the command must assign a value to the input parameter before execution. Here is 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_checkpass"
Set myfirstparam = mycommand.createParameter ("UserName", Advarchar, Adparamintput, 30)
MyCommand.Parameters.Append MyfirstParam
Set mysecondparam = myCommand.createParameter ("Userpass", Advarchar, Adpaaminput, 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 and password Billions are passed to the stored procedure. If this name-password combination is present in the table, the password is reported to good, otherwise the password is BAD. In this script, specify two input parameters with constant adpaaminput. Note that two input parameters are assigned a value before the command is executed.
