Tutorials - True DataControl > Tutorial 8 - Ad-hoc Queries Using True DataControl Range Conditions |
When implementing query forms, True DataControl eliminates the need to write tedious statement building code such as this:
Example Title |
Copy Code
|
---|---|
If RequiredDateFrom.Text <> "" Then SQL = SQL & _ " RequiredDate >= #" & RequiredDateFrom.Text & "#" If RequiredDateTo.Text <> "" Then SQL = SQL & _ " RequiredDate <=" & RequiredDateTo.Text & "#" |
True DataControl enables you to easily create ad-hoc query forms that gather information from the end user and issue a dynamic SQL query according to that information. A powerful method of creating ad-hoc query forms is the True DataControl query mode feature that will be presented in Tutorials 15 and 16.
Sometimes, however, the ad-hoc query is not the main purpose of a form, but rather an optional restriction that the user wants to be able to impose on the data. In this case, you can use True DataControl range conditions. We have selected such an example for this tutorial, because it illustrates several central True DataControl concepts, such as master-detail relationships and range conditions. By now, you must have noticed that in most cases there are several different ways to achieve application goals with True DataControl. This is true, and it proves that different True DataControl features are general, flexible and interdependent enough to create infinite possibilities in application development.
Start a new project.
Place the following controls on the form (Form1) as shown in the figure: two TData controls (TData1, TData2), five TextBox controls (Text1 to 5), a DataGrid control (DataGrid1), a button (Command1), two frames (Frame1, Frame2) and five labels (Label1 to 5).
Set the DataMode property of TData2 to 1 - MemoryArray and define five fields: RequiredDate1, RequiredDate2, ShippedDate1, ShippedDate2 (all four with Data Type set to 7 - Date) and ShipName (8 - String). Follow these steps:
Open the True DataControl property pages for TData2. On the DataSource page select 1 - Memory Array in the DataMode combo box. Select the Fields page. For each of the five fields, create a new field by clicking the right mouse button and choosing New Field from the context menu. Change the default FIELD_0 name to the appropriate field name by typing it in the Name text box. Select the appropriate type in the Data Type combo box.
Set the properties of the other controls as follows (you can use the DataSource property page to set TData data source properties, as described in Tutorial 3):
Example Title |
Copy Code
|
---|---|
TData1.ConnectionString Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=C:\Program Files\ComponentOne Studio\Common\TDDEMO.MDB TData1.CommandType 2 – adCmdTable TData1.RecordSource Orders DataGrid1.DataSource TData1 Text1.DataSource TData2 Text1.DataField RequiredDate1 Text2.DataSource TData2 Text2.DataField RequiredDate2 Text3.DataSource TData2 Text3.DataField ShippedDate1 Text4.DataSource TData2 Text4.DataField ShippedDate2 Text5.DataSource TData2 Text5.DataField ShipName |
Set the captions of the five labels, two frames, and the button according to the picture.
Open the True DataControl property pages for TData1. On the General property page select TData2 in the Master combo box, and select 0 - None in the AutoRefresh combo box.
Go to the Fields property page of TData1. Select the RequiredDate field. Click the right mouse button over the RequiredDate field and select New Range Condition from the menu (alternatively, you can use the New button). An empty range condition for the RequiredDate field appears with controls for entering the condition now visible in the right part of the page. Select >= in the Comparison Operator combo box. Go to the Value Expression text box and type the following expression: TData2.RequiredDate1. This will create a RequiredDate <= TData2.RequiredDate1 range condition for the RequiredDate field.
Now create four more range conditions following the procedure from the previous step. For RequiredDate1:
Example Title |
Copy Code
|
---|---|
RequiredDate <= TData2.RequiredDate2 |
(select <= from the Comparison Operator combo box).
For ShippedDate1:
Example Title |
Copy Code
|
---|---|
ShippedDate >= TData2.ShippedDate1 ShippedDate <= TData2.ShippedDate2 |
For ShipName:
Example Title |
Copy Code
|
---|---|
ShipName starts with TData2.ShipName |
(select StartsWith from the Comparison Operator combo box).
Press OK to close the property pages dialog.
Add the following code to Command1:
Example Title |
Copy Code
|
---|---|
Private Sub Command1_Click() TData1.Refresh End Sub |
Type 10/1/94 in the RequiredDate From text box and 10/10/94 in the RequiredDate To text box. Press the Apply button. The grid now displays orders satisfying the condition imposed on RequiredDate.
Since the Skip If Empty box was left checked (its default state) for all fields, the applicable range conditions were ignored whenever one of their dependent variables was empty. This is how TData1 knew to apply only one of five range conditions, namely RequiredDate >= 10/1/94. Otherwise, all five would have been applied and the result would appear empty.
Type M in the ShipName starts with text box and press Apply. The grid narrows orders further to show only those where the ShipName field starts with M.
Clear the ShipName starts with text box and press Apply. The grid returns to its previous state (before you entered M).
Experiment with all five values (including ShippedDate) and discover a fully functional ad-hoc query application created with just one line of code.
Note that when you modify the value in any of the five text box controls and press Tab, focus leaves the control, but the grid does not change its contents. This is because AutoRefresh was set to 0 - None. You need to click the Apply button to perform an explicit Refresh. See Tutorial 7 for a discussion of the AutoRefresh property.
Close the program. You have successfully completed Tutorial 8.