ComponentOne True DataControl 8.0
Tutorial 7 - Parameters and Dynamic SQL
Tutorials - True DataControl > Tutorial 7 - Parameters and Dynamic SQL

Tutorial 5 explained that field values are undetermined at the recordset level, since fields only receive their values at the record level. This tutorial introduces another kind of variable, a parameter, which belongs to the recordset level, so its value can be used at both the recordset and record levels.

In order to illustrate the general concept of parameters, we will show how a TData control can be used to create SQL statements with parameters that change dynamically in the course of your program execution. You will create an application that can retrieve and display orders with ItemsTotal exceeding the value that the end user enters in a text box. This application will contain only one line of code, a simple call to the Refresh method.

You may have already discovered a limitation of the standard ADO data control forbidding the use of parameterized queries for its RecordSource at design time. With the TData control, you can use parameters in SQL statements. This capability can be used to create a master-detail relationships (if parameters depend on the master field’s values), or it can be used for any other purpose where it is necessary to modify a SQL statement dynamically.

  1. Start a new project.

  2. Place the following controls on the form (Form1) as shown in the figure: two TData controls (TData1, TData2), a MaskEdBox control (MaskEdBox1; include Microsoft Masked Edit Control into the project’s list of components), a DataGrid control (DataGrid1), a button (Command1) and a label (Label1).

  3. Open the True DataControl property pages for TData2. On the DataSource property page select 1 - Memory Array in the DataMode combo box.

  4. Select the Fields property page. Create a new field by pressing the right mouse button and choosing New Field from the menu. Change the default FIELD_0 name to ItemsTotal by typing it in the Name text box. Select Currency in the Data Type combo box. Press OK to close the dialog and save changes.

  5. Open the True DataControl property pages for TData1. On the DataSource property page, enter the following in the ConnectionString edit box:

    Example Title
    Copy Code 
    Provider=Microsoft.Jet.OLEDB.3.51;Persist Security

Info=False;Data Source=C:\Program Files\ComponentOne Studio\Common\TDDEMO.MDB

    or use the Build button to specify the ConnectionString, as shown in Tutorial 2, Step 4.

  6. Set properties of other controls as follows:

    Example Title
    Copy Code 
    DataGrid1.DataSource TData1

MaskEdBox1.DataSource      TData2

MaskEdBox1.DataField ItemsTotal

MaskEdBox1.Format    $#,##0.00

Label1.Caption       Items Total >=

Command1.Caption     Query

  7. Open the True DataControl property pages for TData1. On the General page select TData2 in the Master combo box. Select 0 - None in the AutoRefresh combo box found on the same page.

  8. For each parameter referenced in an SQL statement (such as ItemsTotalPar), you must define a corresponding True DataControl parameter (that is, a recordset-level variable). To create a new parameter, select the Parameters property page for TData1. Note that the parameter list on the left is initially empty. Click the right mouse button over the parameter list and select New from the menu (alternatively, you can press the New button). A new parameter with the default name PARAM_0 will appear. Change the name to ItemsTotal by typing it into the Name text box. Select Currency in the Data Type combo box. You have now created an ItemsTotal parameter. Click the OK button to close the dialog.

  9. Open the TData1 Property Pages, and go to the Parameters page. Specify a Value Expression for the ItemsTotal parameter to maintain the parameter’s value in sync with the ItemsTotal field of the master TData control, TData2.ItemsTotal. Press the ellipsis button located on the right edge of the Value Expression text box to access the expression editor. The parameter, ItemsTotal, now appears in the Parameters list, but the Fields list is empty. This is because the From combo box is set to self: TData1; as explained in Tutorial 3, fields are inaccessible for recordset-level expressions. Select master: TData2 in the From combo box and double-click the ItemsTotal field that now appears in the Fields list. Press the OK button. You have now specified TData2.ItemsTotal for the parameter’s Value Expression. You can also type expressions directly into the text box without using the expression editor.

  10. To complete the parameter definition, associate the ItemsTotal parameter with the ItemsTotalPar used in the SQL text. Type ItemsTotalPar in the SQL Name text box. This will tell the TData1 control that this parameter is used in conjunction with the ItemsTotalPar parameter of the SQL statement.

    Note: If you have multiple parameters in the SQL statement, the order of True DataControl parameters defined in the Parameters property page must be synchronized with the order of parameters in the SQL statement. Use drag-and-drop in the Parameters page to change the order of parameters.

  11. Select 1 – adCmdText in the CommandType combo box (on the TData1 DataSource property page). This will enable the Command Text (SQL) edit box. Type the following parameterized SQL statement there:

    Example Title
    Copy Code 
    PARAMETERS ItemsTotalPar Value;

SELECT * FROM Orders

WHERE -ItemsTotalPar > (

  SELECT -SUM(UnitPrice*Quantity)

  FROM [Order Details]

  WHERE

    [Order Details].OrderID = Orders.OrderID

 )

    You will see this SQL text in the RecordSource property of TData1 in the Visual Basic Properties window.

    Note: We used “-ItemsTotalPar >…” instead of “ItemsTotalPar <…” to overcome a bug in the Microsoft Jet SQL engine.

  12. Add the following code to Command1:

    Example Title
    Copy Code 
    Private Sub Command1_Click()

    TData1.Refresh

End Sub

Run the Program and Observe the Following:

Type 2000 in the ItemsTotal >= masked edit box. Press the Query button to display the grid, which shows orders satisfying the ItemsTotal >= $2,000.00 condition. This result was achieved as follows:

  1. Shifting focus from the edit control to the Query button (after typing 2000 in the edit box) sets the ItemsTotal field of TData2 to 2000.

  2. The Refresh method causes the TData1 recordset to be re-populated with new data. Before doing that, TData1 sets its parameter, ItemsTotal to the result of its ValueExpression TData2.ItemsTotal, which is 2000.

  3. When TData1 recordset is rebuilt, the value of the ItemsTotalPar SQL parameter is set to the value of the ItemsTotal parameter, which has already been set to 2000.

Type 4000 in the ItemsTotal >= masked edit box, and press Tab. Focus will leave the masked edit box control, but grid refresh will not occur. This is because we specified AutoRefresh as 0 - None for TData1. If AutoRefresh were set to either 1- Immediate or 2- When Idle (the default), then changing the contents of the masked edit box and moving focus to another control would automatically refresh TData1.

Close the program. You have successfully completed Tutorial 7.

 

 

Copyright (c) GrapeCity, inc. All rights reserved.

Product Support Forum  |  Documentation Feedback