TestComplete samples (both built-in and additional) are located in the <Users>\Public\Public Documents\TestComplete 15 Samples folder.

Some file managers display the Public Documents folder as Documents.

The following example demonstrates how you can use the ADO Command object in your scripts. 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/testcomplete/downloads/samples and install it. After the installation is over, you can find the database in the <TestComplete 15 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.

TestComplete 32-bit executable is located in the <TestComplete>\Bin folder.

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 15 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 15 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 15 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 15 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"]();
}

Working With ADO Command Object