Applies to TestComplete 14.30, last modified on November 21, 2019

The following example illustrates how you can use the CreateADOCommand method in your scripts. The TestProc routine uses the IAQADOCommand object, based on the TADOCommand class, to execute a query with parameters. Query results are then posted to 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

function TestProc()
{
  var RecSet, Cmd;
  // Create a new object
  Cmd = ADO.CreateADOCommand();
  // Specify the connection string
  Cmd.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
  "Data Source=C:\\Users\\Public\\Documents\\TestComplete 14 Samples\\Desktop\\Checkpoints\\XML\\DataGridViewSample\\OrdersDB.mdb";
  // Specify the command text (the SQL expression)
  Cmd.CommandText = "SELECT * FROM orders WHERE orders.[state] = :MyParam";
  // Specify the command type
  Cmd.CommandType = cmdText;
  // Specify the parameter of the query
  Cmd.Parameters.Items(0).Value = "Canada";
  // Execute the command
  RecSet = Cmd.Execute();
  // Process the table records
  Log.AppendFolder("Customers from Canada");
  RecSet.MoveFirst();
  while (! RecSet.EOF)
  {
    Log.Message(RecSet.Fields.Item("name").Value);
    RecSet.MoveNext();
  };
}

Python

def TestProc():
  # Create a new object
  Cmd = ADO.CreateADOCommand()
  # Specify the connection string
  Cmd.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + \
  "Data Source=C:\\Users\\Public\\Documents\\TestComplete 14 Samples\\Desktop\\Checkpoints\\XML\\DataGridViewSample\\OrdersDB.mdb"
  # Specify the command text (the SQL expression)
  Cmd.CommandText = "SELECT * FROM orders WHERE orders.[state] = :MyParam"
  # Specify the command type
  Cmd.CommandType = cmdText
  # Specify the parameter of the query
  Cmd.Parameters.Items[0].Value = "Canada"
  # Execute the command
  RecSet = Cmd.Execute()
  # Process the table records
  Log.AppendFolder("Customers from Canada")
  RecSet.MoveFirst()
  while not RecSet.EOF:
    Log.Message(RecSet.Fields.Item["name"].Value)
    RecSet.MoveNext()

VBScript

Sub TestProc
  ' Create a new object
  Set Cmd = ADO.CreateADOCommand
  ' Specify the connection string
  Cmd.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + _
  "Data Source=C:\Users\Public\Documents\TestComplete 14 Samples\Desktop\Checkpoints\XML\DataGridViewSample\OrdersDB.mdb"
  ' Specify the command text (the SQL expression)
  Cmd.CommandText = "SELECT * FROM orders WHERE orders.[state] = :MyParam"
  ' Specify the command type
  Cmd.CommandType = cmdText
  ' Specify the parameter of the query
  Cmd.Parameters.Items(0).Value = "Canada"
  ' Execute the command
  Set RecSet = Cmd.Execute
  ' Process the table records
  Log.AppendFolder("Customers from Canada")
  RecSet.MoveFirst
  While Not RecSet.EOF
    Log.Message RecSet.Fields("name").Value
    RecSet.MoveNext
  Wend
End Sub

DelphiScript

procedure TestProc;
var
  RecSet, Cmd: OleVariant;
begin
  // Create a new object
  Cmd := ADO.CreateADOCommand;
  // Specify the connection string
  Cmd.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;'+
  'Data Source=C:\Users\Public\Documents\TestComplete 14 Samples\Desktop\Checkpoints\XML\DataGridViewSample\OrdersDB.mdb';
  // Specify the command text (the SQL expression)
  Cmd.CommandText := 'SELECT * FROM orders WHERE orders.[state] = :MyParam';
  // Specify the command type
  Cmd.CommandType := cmdText;
  // Specify the parameter of the query
  Cmd.Parameters.Items(0).Value := 'Canada';
  // Execute the command
  RecSet := Cmd.Execute;
  // Process the table records
  Log.AppendFolder('Customers from Canada');
  RecSet.MoveFirst;
  while not aqConvert.VarToBool(RecSet.EOF) do
  begin
    Log.Message(RecSet.Fields('name').Value);
    RecSet.MoveNext;
  end;
end;

C++Script, C#Script

function TestProc()
{
  var RecSet, Cmd;
  // Create a new object
  Cmd = ADO["CreateADOCommand"]();
  // Specify the connection string
  Cmd["ConnectionString"] = "Provider=Microsoft.Jet.OLEDB.4.0;" +
  "Data Source=C:\\Users\\Public\\Documents\\TestComplete 14 Samples\\Desktop\\Checkpoints\\XML\\DataGridViewSample\\OrdersDB.mdb";
  // Specify the command text (the SQL expression)
  Cmd["CommandText"] = "SELECT * FROM orders WHERE orders.[state] = :MyParam";
  // Specify the command type
  Cmd["CommandType"] = cmdText;
  // Specify the parameter of the query
  Cmd["Parameters"]["Items"](0)["Value"] = "Canada";
  // Execute the command
  RecSet = Cmd["Execute"]();
  // Process the table records
  Log["AppendFolder"]("Customers from Canada");
  RecSet["MoveFirst"]();
  while (!RecSet["EOF"])
  {
    Log["Message"](RecSet["Fields"]("name")["Value"]);
    RecSet["MoveNext"]();
  };
}

Highlight search results