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 sets. 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" -->
// This is a function that opened a connection and returns the instance as gcnn:
gOpenConnection();
// var cnn = Server.CreateObject('ADODB.Connection');
// cnn.Open(SomeConnectionString);

var cmd = Server.CreateObject("ADODB.Command");
cmd.ActiveConnection = gcnn;

// cmd.CommandText = {sp name|sql|table name}
cmd.CommandText = "zghTest";

// If true, then you can append parameters in any order but must Name must be prefixed with "@"
// cmd.NamedParameters = true;
// If false, then the parameters must be appended in the order listed in a stored procedure
cmd.NamedParameters = true;

// 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 get to EOF 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 get to EOF 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 get to EOF 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
  1. ActiveConnection
  2. CommandStream
  3. CommandText
  4. CommandTimeout
  5. CommandType
  6. Dialect
  7. Name
  8. NamedParameters
  9. Parameters Collection
  10. Prepared
  11. State
  1. Cancel
  2. CreateParameter(Name,Type,Direction,Size,Value)
  3. Execute
None


GeorgeHernandez.comSome rights reserved