Using ADO Components

Applies to TestComplete 14.20, last modified on September 11, 2019

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.

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

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

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

  // Create a recordset
  var 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
  Log.AppendFolder("Customer names");
  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()
{
  // 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 14 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
  Log.AppendFolder("Customer names");
  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(): 
  # 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 14 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, adOpenStatic, adLockReadOnly, adCmdTable);
                              
  # Read customer names from the Names column of the Orders table
  Log.AppendFolder("Customer names")
  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
  
  ' 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 14 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
  Log.AppendFolder "Customer names"
  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

End Sub

DelphiScript

procedure TestADO;
var
  Conn, Rs : OleVariant;
begin
  
  // 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 14 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
  Log.AppendFolder('Customer names');
  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()
{
  // 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 14 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
  Log["AppendFolder"]("Customer names");
  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