ADO.CreateCommand Method

Applies to TestComplete 14.40, last modified on April 22, 2021

Description

This function creates a new ADO Command object and returns a reference to it. This object will allow you to execute queries with parameters from your scripts.

For detailed information on the Commandobject, see documentation on Microsoft ADO objects in the MSDN Library.

Declaration

ADO.CreateCommand()

Result A Command object (Microsoft ADO)

Applies To

The method is applied to the following object:

ADO

Result Value

A Microsoft ADO Command object.

Remarks

  • You can also create a new ADO Command object by its ProgID, ADODB.Command, using the Sys.OleObject routine or the getActiveXObject method (JavaScript only):

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

    JavaScript

    rs = getActiveXObject("ADODB.Command");

    JScript

    rs = Sys.OleObject("ADODB.Command");
    // -- or --
    rs = new ActiveXObject("ADODB.Command");

    Python

    rs = Sys.OleObject["ADODB.Command"]

    VBScript

    Set rs = Sys.OleObject("ADODB.Command")
    ' -- or --
    Set rs = CreateObject("ADODB.Command")

    DelphiScript

    rs := Sys.OleObject['ADODB.Command'];

    C++Script, C#Script

    rs = Sys["OleObject"]("ADODB.Command");
    // -- or --
    rs = new ActiveXObject("ADODB.Command");

Example

The following example demonstrates how you can use the ADO Command object. The TestProc routine executes a query with parameters and posts query results into the test log.

Notes:

  • This example uses the OrdersDB.mdb file that is part of the additional sample package. To use it, download this package from support.smartbear.com/downloads/testcomplete/samples/ and install it. After the installation is over, you can find the database in the <TestComplete 14 Samples>\Desktop\Checkpoints\XML\DataGridViewSample folder.

  • 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, JScript

function TestProc()
 {
  var AConnection, RecSet, Cmd, Prm;
   // Create a new Connection object
  AConnection = ADO.CreateConnection();
  // Specify the connection string
  AConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
  "Data Source=C:\\Users\\Public\\Documents\\TestComplete 14 Samples\\Desktop\\Checkpoints\\XML\\DataGridViewSample\\OrdersDB.mdb";
  // Activate the connection
  AConnection.Open();
  // Create a new Command object
  Cmd = ADO.CreateCommand();
  // Specify the connection
  Cmd.ActiveConnection = AConnection;
  // Specify command type and text
  Cmd.CommandType = adCmdText;
  Cmd.CommandText = "SELECT * FROM orders WHERE orders.[state] = :MyParam";
  // Create a new parameter
   Prm = Cmd.CreateParameter("MyParam", adChar, adParamInput, 20, "Canada");
  Cmd.Parameters.Append(Prm);
   // Execute the command
  RecSet = Cmd.Execute();
  // Process the results
  Log.AppendFolder("Customers from Canada");
  RecSet.MoveFirst();
  while (! RecSet.EOF )
  {
    Log.Message(RecSet.Fields.Item("name").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:\\Users\\Public\\Documents\\TestComplete 14 Samples\\Desktop\\Checkpoints\\XML\\DataGridViewSample\\OrdersDB.mdb"
  # Activate the connection 
  AConnection.Open()
  # Create a new Command object 
  Cmd = ADO.CreateCommand()
  # Specify the connection 
  Cmd.ActiveConnection = AConnection
  # Specify command type and text 
  Cmd.CommandType = adCmdText
  Cmd.CommandText = "SELECT * FROM orders WHERE orders.[state] = :MyParam"
  # Create a new parameter 
  Prm = Cmd.CreateParameter("MyParam", adChar, adParamInput, 20, "Canada")
  Cmd.Parameters.Append(Prm)
  # Execute the command 
  RecSet = Cmd.Execute()
  # Process the results 
  Log.AppendFolder('Customers from Canada')
  RecSet.MoveFirst()
  while not RecSet.EOF:
    Log.Message(RecSet.Fields.Item["name"].Value)
    RecSet.MoveNext()
  AConnection.Close()

VBScript

Sub TestProc
  ' Create a new Connection object
  Set AConnection = ADO.CreateConnection
  ' Specify the connection string
  AConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  "Data Source=C:\Users\Public\Documents\TestComplete 14 Samples\Desktop\Checkpoints\XML\DataGridViewSample\OrdersDB.mdb"
  ' Activate the connection
  AConnection.Open
  ' Create a new Command object
  Set Cmd = ADO.CreateCommand
  ' Specify the connection
  Cmd.ActiveConnection = AConnection
  ' Specify command type and text
  Cmd.CommandText = "SELECT * FROM orders WHERE orders.[state] = :MyParam"
  Cmd.CommandType = adCmdText
  ' Create a new parameter
  Set Prm = Cmd.CreateParameter("MyParam", adChar, adParamInput, 20, "Canada")
  Cmd.Parameters.Append Prm
  ' Execute the command
  Set RecSet = Cmd.Execute
  ' Process the results
  Log.AppendFolder("Customers from Canada")
  RecSet.MoveFirst
  While Not RecSet.EOF
    Log.Message RecSet("name").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;
  // Specify the connection string
  AConnection.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;' +
  'Data Source=C:\Users\Public\Documents\TestComplete 14 Samples\Desktop\Checkpoints\XML\DataGridViewSample\OrdersDB.mdb';
  // Activate the connection
  AConnection.Open;
  // Create a new Command object
  Cmd := ADO.CreateCommand;
   // Specify the connection
  Cmd.ActiveConnection := AConnection;
  // Specify command type and text
  Cmd.CommandText := 'SELECT * FROM orders WHERE orders.[state] = :MyParam';
  Cmd.CommandType := adCmdText;
  // Create a new parameter
  Prm := Cmd.CreateParameter('MyParam', adChar, adParamInput, 20, 'Canada');
  Cmd.Parameters.Append(Prm);
  // Execute the command
  RecSet := Cmd.Execute;
  // Process the results
  Log.AppendFolder('Customers from Canada');
  RecSet.MoveFirst;
  while not aqConvert.VarToBool(RecSet.EOF) do
  begin
    Log.Message(RecSet.Fields('name').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"]();
  // Specify the connection string
  AConnection["ConnectionString"] = "Provider=Microsoft.Jet.OLEDB.4.0;" +
  "Data Source=C:\\Users\\Public\\Documents\\TestComplete 14 Samples\\Desktop\\Checkpoints\\XML\\DataGridViewSample\\OrdersDB.mdb";
  // Activate the connection
  AConnection["Open"]();
  // Create a new Command object
  Cmd = ADO["CreateCommand"]();
  // Specify the connection
  Cmd["ActiveConnection"] = AConnection;
  // Specify command type and text
  Cmd["CommandType"] = adCmdText;
  Cmd["CommandText"] = "SELECT * FROM orders WHERE orders.[state] = :MyParam";
  // Create a new parameter
  Prm = Cmd["CreateParameter"]("MyParam", adChar, adParamInput, 20, "Canada");
  Cmd["Parameters"]["Append"](Prm);
  // Execute the command
  RecSet = Cmd["Execute"]();
  // Process the results
  Log["AppendFolder"]("Customers from Canada");
  RecSet["MoveFirst"]();
  while (!RecSet["EOF"])
  {
    Log["Message"](RecSet["Fields"]("name")["Value"]);
    RecSet["MoveNext"]();
  }
  AConnection["Close"]();
 }

See Also

CreateADOCommand Method
CreateRecordset Method
CreateConnection Method

Highlight search results