Command by George Hernandez

The ADO Command object holds a database command.

Basic Usage

A "command" to a database in ADO comes in three major types: a SQL statement, a table pull, or a call to a stored procedure. Regardless of the command type, the command usually returns one of three things:

One or more result set. Via Command.Execute() or Recordset.Open().
One or more results. Via Command.Execute() and an output Parameter.
No result sets or results. Via Command.Execute() or Connection.Execute().

As you can see the Command object is the most versatile. However the biggest reason to use the command object with the parameter object is to provide an additional layer of security (say against SQL injection). The biggest reason to not use the command objects is for coding expediency, especially quick and dirty situations where you don't have to worry about security as much.

In this example, assume that the following zghTest stored procedure is used. Simply change the data type of the parameters as needed.:

CREATE PROCEDURE [com].[zghTest]
    @MyIn int =null,
    @MyOut int =null output
AS
BEGIN
SET NOCOUNT ON;
set @MyOut = 0; --Testing returning output param
select 78 as ID, @MyIn as MyIn, @MyOut as MyOut; --Testing returning rst
return 30; --Testing returning return
END

Here is a simple ASP page that uses the ADO Command object to pull different thing and different data types. Assume that the include file does things like open and closes the connection as well as parse a recordset for output as HTML.

<%@ language="javascript" %>
<!-- #INCLUDE VIRTUAL = "/ic/includes/ssJS.asp" -->
gOpenConnection();
var cmd = Server.CreateObject("ADODB.Command");
cmd.ActiveConnection = gcnn;
//cmd.CommandText = {sp name|sql|table name}
cmd.CommandText = "zghTest";
// cmd.CommandType = {-1:unspecified|1:cmd or sp|2:tbl name|4:sp|8:unk (default)}
cmd.CommandType = 4;
//cmd.CreateParameter(Name, DataType, ParameterDirection, Size, Value)
var parRet = cmd.CreateParameter("rc", 3, 4); //Always returns an integer
cmd.Parameters.Append(parRet);
var parIn = cmd.CreateParameter("MyIn", 3, 1, 4, 1000);
cmd.Parameters.Append(parIn);
var parOut = cmd.CreateParameter("MyOut", 3, 2, 4);
cmd.Parameters.Append(parOut);

//Testing receving rst
//var rst = Server.CreateObject("ADODB.Recordset");
//rst = cmd.Execute();
//gRstToHTML(rst, false);

//Testing receiving parOut
//cmd.Execute();
//Response.Write("<p>parOut: "+parOut.Value+"</p>");
//var ADate=new Date(parOut.Value);
//Response.Write(ADate.getMinutes());

//Testing receiving rc
//cmd.Execute();
//Response.Write("<p>parRet: "+parRet.Value+"</p>");

//Testing receiving rst, parOut
//NOTE: The rst must be accessed 1st
//var rst = Server.CreateObject("ADODB.Recordset");
//rst = cmd.Execute();
//gRstToHTML(rst, false);
//Response.Write("<p>parOut: "+parOut.Value+"</p>");

//Testing receiving rst, rc
//NOTE: The rst must be accessed 1st
//var rst = Server.CreateObject("ADODB.Recordset");
//rst = cmd.Execute();
//gRstToHTML(rst, false);
//Response.Write("<p>parRet: "+parRet.Value+"</p>");

//Testing receiving rc, parOut
//NOTE: The rst must be accessed 1st
//cmd.Execute();
//Response.Write("<p>parRet: "+parRet.Value+"</p>");
//Response.Write("<p>parOut: "+parOut.Value+"</p>");

//Testing receiving rc, rst, parOut
//NOTE: The rst must be accessed 1st
var rst = Server.CreateObject("ADODB.Recordset");
rst = cmd.Execute();
gRstToHTML(rst, false);
Response.Write("<p>parOut: "+parOut.Value+"</p>");
Response.Write("<p>parRet: "+parRet.Value+"</p>");

rst = null;
cmd = null;
gCloseConnection();
%>

Properties, Methods, and Events

Properties	Methods	Events
ActiveConnection CommandStream CommandText CommandTimeout CommandType Dialect Name NameParameters Paramters Collection Prepared State	Cancel CreateParameter(Name,Type,Direction,Size,Value) Execute	None

Page Modified: (Hand noted: 2008-04-29 21:22:21Z) (Auto noted: 2008-05-27 16:04:45Z)