Using ADO Components

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

You can connect to databases using the ADO DB functionality included in Microsoft Windows. You can instantiate connections, record sets and commands as COM objects and then use their methods and properties in scripts. To specify values of properties or method parameters that accept constants, you may need to use the integer equivalents of the constants, since the constants may be undefined in TestComplete.

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.

The following example demonstrates how you can instantiate Microsoft ADO DB objects in your scripts and use them to iterate through table records:

Note: 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 12 Samples>\Desktop\Checkpoints\XML\DataGridViewSample folder.
JavaScript
JScript
Python
VBScript
DelphiScript
C++Script, C#Script

JavaScript

function TestADO()
{
  Log.AppendFolder("Customer names");
  // Create and open a connection to the OrdersDB.mdb database
  let Conn = getActiveXObject("ADODB.Connection");
  Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                         "Data Source=C:\\Users\\Public\\Documents\\TestComplete 12 Samples\\Desktop\\Checkpoints\\XML\\DataGridViewSample\\OrdersDB.mdb";
  Conn.Open();

  // Create a recordset
  let Rs = getActiveXObject("ADODB.Recordset");
  // Open the recordset and get the Orders table from the OrdersDB database
  Rs.Open("orders", Conn, 3 /* adOpenStatic */,
                           1 /* adLockReadOnly */, 2 /* adCmdTable */);

  // Read customer names from the Names column of the Orders table
  Rs.MoveFirst();
  while(! Rs.EOF)
  {
    Log.Message(Rs.Fields.Item("name").Value);
    Rs.MoveNext();
  }

  // Close the recordset and connection
  Rs.Close();
  Conn.Close();
}

JScript

function TestADO()
{
  Log.AppendFolder("Customer names");
  // Create and open a connection to the OrdersDB.mdb database
  var Conn = new ActiveXObject("ADODB.Connection");
  Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                         "Data Source=C:\\Users\\Public\\Documents\\TestComplete 12 Samples\\Desktop\\Checkpoints\\XML\\DataGridViewSample\\OrdersDB.mdb";
  Conn.Open();
 
  // Create a recordset
  var Rs = new ActiveXObject("ADODB.Recordset");
  // Open the recordset and get the Orders table from the OrdersDB database
  Rs.Open("orders", Conn, 3 /* adOpenStatic */,
                           1 /* adLockReadOnly */, 2 /* adCmdTable */);

  // Read customer names from the Names column of the Orders table
  Rs.MoveFirst();
  while(! Rs.EOF)
  {
    Log.Message(Rs.Fields.Item("name").Value);
    Rs.MoveNext();
  }
  
 // Close the recordset and connection
  Rs.Close();
  Conn.Close();
}

Python

def TestADO(): 
  Log.AppendFolder("Customer names")
  # Create and open a connection to the OrdersDB.mdb database
  Conn = Sys.OleObject["ADODB.Connection"]
  Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=C:\\Users\\Public\\Documents\\TestComplete 12 Samples\\Desktop\\Checkpoints\\XML\\DataGridViewSample\\OrdersDB.mdb"
  Conn.Open()
  
  # Create a recordset
  Rs = Sys.OleObject["ADODB.Recordset"]
  # Open the recordset and get the Orders table from the OrdersDB database
  Rs.Open("orders", Conn, 3, 1 , 2 )
  
  # Read customer names from the Names column of the Orders table
  Rs.MoveFirst()
  while not Rs.EOF:
    Log.Message(Rs.Fields.Item["name"].Value)
    Rs.MoveNext()    
    
  # Close the recordset and connection
  Rs.Close()
  Conn.Close()

VBScript

Sub TestADO
  Dim Conn, Rs

  Log.AppendFolder "Customer names"

  ' Create and open a connection to the OrdersDB.mdb database
  Set Conn = CreateObject("ADODB.Connection")
  Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + _
                          "Data Source=C:\Users\Public\Documents\TestComplete 12 Samples\Desktop\Checkpoints\XML\DataGridViewSample\OrdersDB.mdb"
  Conn.Open

  ' Create a recordset
  Set Rs = CreateObject("ADODB.Recordset")
  ' Open the recordset and get the Orders table from the OrdersDB database
  Rs.Open "orders", Conn, 3, 1, 2 ' adOpenStatic, adLockReadOnly, adCmdTable

  ' Read customer names from the Names column of the Orders table
  Rs.MoveFirst
  While Not Rs.EOF
    Log.Message Rs.Fields.Item("name").Value
    Rs.MoveNext
  WEnd

  ' Close the recordset and connection
  Rs.Close
  Conn.Close

  Set Rs = Nothing
  Set Conn = Nothing
End Sub

DelphiScript

procedure TestADO;
var
  Conn, Rs : OleVariant;
begin
  Log.AppendFolder('Customer names');

  // Create and open a connection to the OrdersDB.mdb database
  Conn := Sys.OleObject['ADODB.Connection'];
  Conn.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;' +
                           'Data Source=C:\Users\Public\Documents\TestComplete 12 Samples\Desktop\Checkpoints\XML\DataGridViewSample\OrdersDB.mdb';
  Conn.Open;

  // Create a recordset
  Rs := Sys.OleObject['ADODB.Recordset'];
  // Open the recordset and get the Orders table from the OrdersDB database
  Rs.Open('orders', Conn, 3 {adOpenStatic},
                            1 {adLockReadOnly}, 2 {adCmdTable});
  // Read customer names from the Names column of the Orders table
  Rs.MoveFirst;
  while not Rs.EOF do
  begin
    Log.Message(Rs.Fields.Item('name').Value);
    Rs.MoveNext;
  end;

  // Close the recordset and connection
  Rs.Close;
  Conn.Close;
end;

C++Script, C#Script

function TestADO()
{
  Log["AppendFolder"]("Customer names");
  // Create and open a connection to the OrdersDB.mdb database
  var Conn = Sys["OleObject"]("ADODB.Connection");
  Conn["ConnectionString"] = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                          "Data Source=C:\\Users\\Public\\Documents\\TestComplete 12 Samples\\Desktop\\Checkpoints\\XML\\DataGridViewSample\\OrdersDB.mdb";
  Conn["Open"]();
  
  // Create a recordset
  var Rs = Sys["OleObject"]("ADODB.Recordset");
  // Open the recordset and get the Orders table from the OrdersDB database
  Rs["Open"]("orders", Conn, 3 /* adOpenStatic */,
                              1 /* adLockReadOnly */, 2 /*adCmdTable*/);
 
  // Read customer names from the Names column of the Orders table
  Rs["MoveFirst"]();
  while(! Rs["EOF"])
  {
    Log["Message"](Rs["Fields"]["Item"]("name")["Value"]);
    Rs["MoveNext"]();
  }
  
 // Close the recordset and connection
  Rs["Close"]();
  Conn["Close"]();
}

To learn more about other ways of working with databases, see Working With Databases

See Also

Working With Databases

Highlight search results