Working With ADO Command Object

Applies to TestComplete 12.60, last modified on September 17, 2018

The following code demonstrates the use of the ADO Command object. The TestProc routine executes a query with parameters and posts query results into the test log.

Notes:

  • The syntax of the command’s query depends on the SQL provider you use. For instance, if you work through the Microsoft.Jet.OLEDB.4.0 provider, you can specify parameters by their names. Some other providers may use question marks (?) as parameter placeholders (the first question mark corresponds to the first parameter, the second - to the second and so on). Some providers may support both techniques, or use their own syntax. For more information on the command syntax, see the database provider's documentation.

  • Using the Microsoft.Jet.OLEDB.4.0 provider requires that you run your script in the 32-bit version of TestComplete.

JavaScript
JScript
Python
VBScript
DelphiScript
C++Script, C#Script

JavaScript

function TestProc()
{
  var AConnection, RecSet, Cmd, Prm;
  // Create a new Connection object
  AConnection = ADO.CreateConnection();
  // Note that you can also create an ADO connection using the following code:
  // AConnection = getActiveXObject("ADODB.Connection");

  // Specify the connection string
  AConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
  "Data Source=C:\\Microsoft Visual Studio\\VB98\\Biblio.mdb";
  // Activate the connection
  AConnection.Open();
  // Create a new Coomnad object
  Cmd = ADO.CreateCommand();
  // To create an ADO command you can also use the following code:
  // Cmd = getActiveXObject("ADODB.Command");
  // Specify the connection
  Cmd.ActiveConnection = AConnection;
  // Specify command type and text
  Cmd.CommandType = adCmdText;
  Cmd.CommandText = "SELECT * FROM Authors WHERE Authors.[Year Born] < :MyParam";
  // Create a new parameter
  Prm = Cmd.CreateParameter("MyParam", adInteger, adParamInput);
  Cmd.Parameters.Append(Prm);
  // Specify the parameter value
  Prm.Value = 1940;
  // Execute the command
  RecSet = Cmd.Execute();
  // Process the results
  RecSet.MoveFirst();
  while (! RecSet.EOF )
  {
    Log.Message(RecSet.Fields.Item("Author").Value, RecSet.Fields.Item("Year Born").Value);
    RecSet.MoveNext();
  }
  AConnection.Close();
}

JScript

function TestProc()
{
  var AConnection, RecSet, Cmd, Prm;
  // Create a new Connection object
  AConnection = ADO.CreateConnection();
  // Note that you can also create an ADO connection using the following code:
  // AConnection = new ActiveXObject("ADODB.Connection");

  // Specify the connection string
  AConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
  "Data Source=C:\\Microsoft Visual Studio\\VB98\\Biblio.mdb";
  // Activate the connection
  AConnection.Open();
  // Create a new Coomnad object
  Cmd = ADO.CreateCommand();
  // To create an ADO command you can also use the following code:
  // Cmd = new ActiveXObject("ADODB.Command");
  // Specify the connection
  Cmd.ActiveConnection = AConnection;
  // Specify command type and text
  Cmd.CommandType = adCmdText;
  Cmd.CommandText = "SELECT * FROM Authors WHERE Authors.[Year Born] < :MyParam";
  // Create a new parameter
  Prm = Cmd.CreateParameter("MyParam", adInteger, adParamInput);
  Cmd.Parameters.Append(Prm);
  // Specify the parameter value
  Prm.Value = 1940;
  // Execute the command
  RecSet = Cmd.Execute();
  // Process the results
  RecSet.MoveFirst();
  while (! RecSet.EOF )
  {
    Log.Message(RecSet.Fields("Author").Value, RecSet.Fields("Year Born").Value);
    RecSet.MoveNext();
  }
  AConnection.Close();
}

Python

def TestProc():
  # Create a new Connection object
  AConnection = ADO.CreateConnection()

  # Specify the connection string
  AConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + \
  "Data Source=C:\\Microsoft Visual Studio\\VB98\\Biblio.mdb"
  # Activate the connection
  AConnection.Open()
  # Create a new Coomnad object
  Cmd = ADO.CreateCommand()
  
  # Specify the connection
  Cmd.ActiveConnection = AConnection
  # Specify command type and text
  Cmd.CommandType = adCmdText
  Cmd.CommandText = "SELECT * FROM Authors WHERE Authors.[Year Born] < :MyParam"
  # Create a new parameter
  Prm = Cmd.CreateParameter("MyParam", adInteger, adParamInput)
  Cmd.Parameters.Append(Prm)
  # Specify the parameter value
  Prm.Value = 1940
  # Execute the command
  RecSet = Cmd.Execute()
  # Process the results
  RecSet.MoveFirst()
  while not RecSet.EOF:
    Log.Message(RecSet.Fields.Item("Author").Value, RecSet.Fields.Item("Year Born").Value)
    RecSet.MoveNext()
  AConnection.Close()

VBScript

Sub TestProc
  ' Create a new Connection object
  Set AConnection = ADO.CreateConnection
  ' Note that you can also create an ADO connection using the following code:
  ' Set AConnection = CreateObject("ADODB.Connection")

  ' Specify the connection string
  AConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + _
  "Data Source=C:\Microsoft Visual Studio\VB98\biblio.mdb"
  ' Activate the connection
  AConnection.Open
  ' Create a new Command object
  Set Cmd = ADO.CreateCommand
  ' To create an ADO command you can also use the following code:
  ' Set Cmd = CreateObject("ADODB.Command")
  ' Specify the connection
  Cmd.ActiveConnection = AConnection
  ' Specify command type and text
  Cmd.CommandText = "Select * From Authors WHERE Authors.[Year Born] < :MyParam"
  Cmd.CommandType = adCmdText
  ' Create a new parameter
  Set Prm = Cmd.CreateParameter("MyParam", adInteger, adParamInput)
  Cmd.Parameters.Append Prm
  ' Specify the parameter value
  Prm.Value = 1940
  ' Execute the command
  Set RecSet = Cmd.Execute
  ' Execute the command
  RecSet.MoveFirst
  While Not RecSet.EOF
    Log.Message RecSet("Author").Value, RecSet("Year Born").Value
    RecSet.MoveNext
  WEnd
  AConnection.Close
End Sub

DelphiScript

procedure TestProc;
var
  AConnection, RecSet, Cmd, Prm : OleVariant;
begin
  // Create a new Connection object
  AConnection := ADO.CreateConnection;
  // Note that you can also create an ADO connection using the following code:
  // AConnection := Sys.OleObject('ADODB.Connection');

  // Specify the connection string
  AConnection.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;' +
  'Data Source=C:\Microsoft Visual Studio\VB98\biblio.mdb';
  // Activate the connection
  AConnection.Open;
  // Create a new Command object
  Cmd := ADO.CreateCommand;
  // To create an ADO command you can also use the following code:
  // Cmd := Sys.OleObject('ADODB.Command');

  // Specify the connection
  Cmd.ActiveConnection := AConnection;
  // Specify command type and text
  Cmd.CommandText := 'Select * From Authors WHERE Authors.[Year Born] < :MyParam';
  Cmd.CommandType := adCmdText;
  // Create a new parameter
  Prm := Cmd.CreateParameter('MyParam', adInteger, adParamInput);
  Cmd.Parameters.Append(Prm);
  // Specify the parameter value
  Prm.Value := 1940;
  // Execute the command
  RecSet := Cmd.Execute;
  // Process the results
  RecSet.MoveFirst;
  while not aqConvert.VarToBool(RecSet.EOF) do
  begin
    Log.Message(RecSet.Fields('Author').Value, RecSet.Fields('Year Born').Value);
    RecSet.MoveNext;
  end;
  AConnection.Close;
end;

C++Script, C#Script

function TestProc()
{
  var AConnection, RecSet, Cmd, Prm;
  // Create a new Connection object
  AConnection = ADO["CreateConnection"]();
  // Note that you can also create an ADO connection using the following code:
  // AConnection = new ActiveXObject("ADODB.Connection");

  // Specify the connection string
  AConnection["ConnectionString"] = "Provider=Microsoft.Jet.OLEDB.4.0;" +
  "Data Source=C:\\Microsoft Visual Studio\\VB98\\Biblio.mdb";
  // Activate the connection
  AConnection["Open"]();
  // Create a new Command object
  Cmd = ADO["CreateCommand"]();
  // To create an ADO command you can also use the following code:
  // Cmd = new ActiveXObject("ADODB.Command");

  // Specify the connection
  Cmd["ActiveConnection"] = AConnection;
  // Specify command type and text
  Cmd["CommandType"] = adCmdText;
  Cmd["CommandText"] = "SELECT * FROM Authors WHERE Authors.[Year Born] < :MyParam";
  // Create a new parameter
  Prm = Cmd["CreateParameter"]("MyParam", adInteger, adParamInput);
  Cmd["Parameters"]["Append"](Prm);
  // Specify the parameter value
  Prm["Value"] = 1940;
  // Execute the command
  RecSet = Cmd["Execute"]();
  // Process the results
  RecSet["MoveFirst"]();
  while (!RecSet["EOF"])
  {
    Log["Message"](RecSet["Fields"]("Author")["Value"], RecSet["Fields"]("Year Born")["Value"]);
    RecSet["MoveNext"]();
  }
  AConnection["Close"]();
}

Highlight search results