ADO.CreateADOQuery

Applies to TestComplete 15.70, last modified on December 17, 2024

The following code illustrates how you can use the IAQAADOQuery object, based on the TADOQuery class, to retrieve data from the databases. The TestProc routine executes the query and inserts 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.

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

JavaScript, JScript

function TestProc()
{
  var Qry;
  // Create a query
  Qry = ADO.CreateADOQuery();
  // Specify the connection string
  Qry.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
  "Data Source=C:\\Users\\Public\\Documents\\TestComplete 15 Samples\\Desktop\\Checkpoints\\XML\\DataGridViewSample\\OrdersDB.mdb";
  // Specify the SQL expression
  Qry.SQL = "SELECT * FROM orders WHERE orders.[state] = :Param_State";
  // Specify the parameter value
  Qry.Parameters.ParamByName("Param_State").Value = "Canada";
  // Execute the query
  Qry.Open();
  // Process results and insert data into the test log
  Log.AppendFolder("Customers from Canada");
  Qry.First();
  while (! Qry.EOF)
  {
    Log.Message(Qry.FieldByName("name").Value);
    Qry.Next();
  };
  // Closes the query
  Qry.Close();
}

Python

def TestProc():
  # Create a query
  Qry = ADO.CreateADOQuery()
  # Specify the connection string
  Qry.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + \
  "Data Source=C:\\Users\\Public\\Documents\\TestComplete 14 Samples\\Desktop\\Checkpoints\\XML\\DataGridViewSample\\OrdersDB.mdb"
  # Specify the SQL expression
  Qry.SQL = "SELECT * FROM orders WHERE orders.[state] = :Param_State"
  # Specify the parameter value
  Qry.Parameters.ParamByName("Param_State").Value = "Canada"
  # Execute the query
  Qry.Open()
  # Process results and insert data into the test log
  Log.AppendFolder("Customers from Canada")
  Qry.First()
  while not Qry.EOF:
    Log.Message(Qry.FieldByName("name").Value)
    Qry.Next()
  # Closes the query
  Qry.Close()

VBScript

Sub TestProc
  ' Create a query
  Set Qry = ADO.CreateADOQuery
  ' Specify the connection string
  Qry.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + _
  "Data Source=C:\Users\Public\Documents\TestComplete 15 Samples\Desktop\Checkpoints\XML\DataGridViewSample\OrdersDB.mdb"
  ' Specify the SQL expression
  Qry.SQL = "SELECT * FROM orders WHERE orders.[state] = :Param_State"
  ' Specify the parameter value
  Qry.Parameters.ParamByName("Param_State").Value = "Canada"
  ' Execute the query
  Qry.Open
  ' Process results and insert data into the test log
  Log.AppendFolder("Customers from Canada")
  Qry.First
  While Not Qry.EOF
    Log.Message Qry.FieldByName("name").Value
    Qry.Next
  Wend
  ' Closes the query
  Qry.Close
End Sub

DelphiScript

procedure TestProc;
var
  Qry : OleVariant;
begin
  // Create a query
  Qry := ADO.CreateADOQuery();
  // Specify the connection string
  Qry.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;' +
  'Data Source=C:\Users\Public\Documents\TestComplete 15 Samples\Desktop\Checkpoints\XML\DataGridViewSample\OrdersDB.mdb';
  // Specify the SQL expression
  Qry.SQL := 'SELECT * FROM orders WHERE orders.[state] = :Param_State';
  // Specify the parameter value
  Qry.Parameters.ParamByName['Param_State'].Value := 'Canada';
  // Execute the query
  Qry.Open;
  // Process results and insert data into the test log
  Log.AppendFolder('Customers from Canada');
  Qry.First;
  while not aqConvert.VarToBool(Qry.EOF) do
  begin
    Log.Message(Qry.FieldByName('name').Value);
    Qry.Next;
  end;
  // Closes the query
  Qry.Close
end;

C++Script, C#Script

function TestProc()
{
  var Qry;
  // Create a query
  Qry = ADO["CreateADOQuery"]();
  // Specify the connection string
  Qry["ConnectionString"] = "Provider=Microsoft.Jet.OLEDB.4.0;" +
  "Data Source=C:\\Users\\Public\\Documents\\TestComplete 15 Samples\\Desktop\\Checkpoints\\XML\\DataGridViewSample\\OrdersDB.mdb";
  // Specify the SQL expression
  Qry["SQL"] = "SELECT * FROM orders WHERE orders.[state] = :Param_State";
  // Specify the parameter value
  Qry["Parameters"]["ParamByName"]("Param_State")["Value"] = 'Canada';
  // Execute the query
  Qry["Open"]();
  // Process results and insert data into the test log
  Log["AppendFolder"]("Customers from Canada");
  Qry["First"]();
  while (!Qry["EOF"])
  {
    Log["Message"](Qry["FieldByName"]("name")["Value"]);
    Qry["Next"]();
  }
  // Closes the query
  Qry["Close"]();
}

Highlight search results