ADO Browse Box Control Template

Top  Previous  Next

 

  

Click on a TAB above to jump to that area

 

The ADO Browse Box Control template provides the extensive features of the standard Browse Box control, and managed through an ADO connection layer.

In addition, there are new features that extend the functionality of the standard Browse Box, including:

 

Ability to change column position at runtime, by dragging the appropriate column to its new position.

Ability to click on a column header and sort by that column at runtime. You can also SHIFT click on an additional header in order to add another column to the sort order sequence. SHIFT click again to change the order from ascending to descending.

Ability to HIDE/UNHIDE columns by pressing CTRL+RIGHTCLICK in the data area of the ADO Browse Box at runtime.

More powerful template-based filtering options.

 

The following prompts are available:

 

General

 

Loading Method

Select Page or Table from the drop list provided. Page tells the Browse Box to return a page of records to the client when requested. Table returns all records of the primary table used in the Browse Box, unless limited below.

 

Limit the result set rows number

Check this box to limit the number of rows returned by the client’s request.

 

Maximum rows

Specify a maximum number of rows to return. This prompt is only available if the Limit the result set rows number check box is checked.

 

Generate initial call to Refresh

Check this box to force the browse to immediately refresh all data elements when the browse is first loaded. This will allow any secondary elements to be updated properly.

Connection Group:

Connection Object

Choose a Connection object name from the drop list provided. This list should contain the connection object you created in the ADO Global support template.

Use a New Connection

If you do not wish to use any of the Global Connections that are available, check this box to create a new connection specifically for this browse box.

New Connection

Press this button to call the Connection Builder. On the subsequent dialog, enter a new name to use for the new Connection Object, and press the Connection String Settings button to access the Connection String Settings dialog.

 

DB Interface Group:

 

DB Interface object

The Database Interface object name is generated by the ADO template. If you need to override the default object name, enter a new name here.

CRecordset Attributes

Press the Crecordset Attributes button to access special properties that affects the ADO recordset. Each attribute is described in detail in the ADO Reference provided by Microsoft.

Cursor Location

The CursorLocation property on a Connection object or Recordset object indicates the location of the cursor engine.

Cursor Type

The CursorType property on a Recordset object indicates the type of the cursor engine.

LockType

The LockType property is used to set the type of locks placed on records that the provider should use when opening the Recordset.

Command Type

The CommandType property on a Recordset object Indicates the type of a Command object.

Execute Options

The Execute method on a Connection object executes the statement specified in the CommandText property

 

Table Mapper – BASETABLENAME is not supported

Check this box if your ADO data source does not support BASETABLENAME capability.

 

In ADO, when you have created a SELECT statement through your template design that contains the same column name, but coming from a different table, the fields’ collection will have field objects with the same name.

For example,

SELECT Customer.SysID, order.SysId etc…

The Fields collection above will have 2 Field objects with the name SysID. In order to make sure that the values from those 2 fields will go into the right application variable, we need to know which table the field belongs to. There is a property in the Field properties collection called BASETABLENAME that provide this value.

 

 

Data Columns/Hot Fields

The Data Columns and Hot Fields tab control provide prompts that allow control of the data elements that will appear in the Browse Box, in the scrollable (Data Columns) and non-scrollable (Hot Fields) areas.

 

The following prompts are presented:

 

Automatically manage repeated columns name

Check this box to allow the ADO template to handle duplicate column names automatically. If you are writing custom SQL statements, and wish to name your own repeated columns, turn off this check box.

 

Data Columns

The Data Columns list box displays data elements that have been populated via the List Box Formatter. Press the Properties button to access the following prompts:

 

Query Field

Identifies a column as a field that can be queried (searched).

 

Column is a

Identifies the data element as one that is read directly from the ADO data source (Table Column), or one that is a variable defined within the application, like a computed or conditional field (Expression).

 

Use AS

Check this box if the column name is long and verbose, and you need to rename the column to a more descriptive and compacted name. This is useful if you are constructing long or complex SQL statements. This is an option for Table Columns, but required for all Expressions.

 

Unique Field ID/AS

Enter a unique Field ID to use with the Use AS option for Table Columns, or the AS option for an Expression..

 

Expression

Enter a valid expression to use for the defined Data Column

 

This column cannot be hidden

Check this box to restrict this Data Column from being hidden as set in the Default Behavior tab.

 

This column cannot be sorted

Check this box to restrict this Data Column from being a sort field as defined in the Default Behavior tab.

This Column is the Default sort column

Check this box to indicate that this column is to be used as the default sort. This will generate a call to the cBrowse method ApplySort with the corresponding column number in order to perform the sort and update of the list box header accordingly.

Use the This Column is the Default sort column check box to set the column to use as the default locator.

 

Hot Fields

The Hot Fields list box displays data elements that have been populated outside of the List Box Formatter, or data elements that may not be populated, but need to be referenced or updated in the procedure source. Hot fields are normally data elements that are related to the contents of the list box (i.e., Address information, text based descriptions, etc.). Press the Properties button to access the following prompts:

 

Hot Field

Press the ellipsis button to select a field to use as the hot field.

 

Column is a

Identifies the data element as one that is read directly from the ADO data source (Table Column), or one that is a variable defined within the application, like a computed or conditional field (Expression).

 

Use AS

Check this box if the column name is long and verbose, and you need to rename the column to a more descriptive and compacted name. This is useful if you are constructing long or complex SQL statements. This is an option for Table Columns, but required for all Expressions.

 

Unique Field ID/AS

Enter a unique Field ID to use with the Use AS option for Table Columns, or the AS option for an Expression..

 

Expression

Enter a valid expression to use for the defined Data Column

 

Expression Data Type

Select a valid data type from the drop list that the ADO layer will translate.

 

Default SQL

Because the SQL to access the data is vitally important, the ADO templates allow you to view the generated SELECT statement and customize it if necessary. This also provides a convenient way to customize the browse ordering, should you wish.

Regenerate SQL

Press this button to reset the original template-constructed SQL statements. This is useful should you need to start from scratch again before customizing your statements. Only enabled when the next prompt is active (checked).

 

Override SELECT SQL

Check this box to bypass the auto generation of the SELECT statement by the templates. Your custom statement will be substituted in its place. This box also enables the Regenerate SQL button, should you wish to reset the statement back to its original value.

 

Default SQL Select

This text box provides the base SELECT statement. Note that if your browse contain fields from more than a single table, it will automatically provide for a JOIN.

Important Note:

If you've populated a field in the Data Columns tab for which the ADO templates can't resolve the proper syntax (for example, if you've populated a data variable in the list box control for your browse) and the resulting page doesn't display properly, examine the SQL statement here. Should you find a statement with a blank for the field name (look for an extra comma in the order that the suspect field appears in the data columns list, as in Select fieldname,fieldname,,fieldname...), you may edit the statement here or delete the suspect column from the list. Such a problem should be very rare.

Unique Key

As stated on the tab control, ADO/SQL requires a unique key to identify a record. Should you have more than one unique key defined in your table, press the ellipsis button to select an alternate key to use.

 

Default Behavior

The default behavior of the ADO Browse Box has a much different interface than the standard browse box. You will also notice that the filter capabilities are expanded in the ADO template. The following prompts are available:

 

Fields Tab:

The Fields tab control provides control of your browse box filtering and sorting features.

 

Range or Filter Columns

Press the update buttons to add (Insert), modify (Properties), or remove (Delete) a range or filter column. These are the data elements used to limit the records displayed in your Browse Box. The Range or Filter dialog provides the following prompts:

 

Column

Select a column name from the drop list provided to use as criteria for the browse filter or range. The columns displayed are those columns you have defined in the Data Columns/Hot Fields dialog.

 

Use Static Value?

Check this box to use the static value of the column selected. This is the value of the column when the browse is first initialized

 

Negate the Range or Filter(NOT)

Check this box to apply the NOT clause to the selected column. This will have the effect of "reversing" the filter expression (Example: MyCol = MyValue will be generated as WHERE NOT (MyCol = MyValue).

 

Range Limit Type

Specifies the type of range limit to apply. Choose one of the following from the drop-down list.

Single Value

Lets you limit the filter criteria to a single value. Specify the variable containing that value in the Range Limit Value box which appears.

In the case where you are using static values, you are responsible to enter the values with the necessary single quotes. For example, if you are using the IN filter criteria, and need to check several parameters enter 'Test', 'Test2',’TestN’ in the Single Value line. For static values, you need to enter single quotes where needed.

 

Range of Values

Lets you specify upper and lower limits. Specify the variables containing the limits in the Low Limit Value and High Limit Value boxes.

 

Less Than

Lets you limit the records read to all records less than a single value. Specify the variable containing that value in the Range Limit Value box which appears.

 

Great Than

Lets you limit the records read to all records greater than a single value. Specify the variable containing that value in the Range Limit Value box which appears.

 

IN

Lets you limit the records read to all records that match the contents of a single value.

 

Begins with

Lets you limit the records read to all records that begin with the contents of a single value.

 

Ends with

Lets you limit the records read to all records that end with the contents of a single value.

 

Contains

Lets you limit the records read to all records that begin with the contents of a single value.

 

Sort Columns

Press the update buttons to add (Insert), modify (Properties), or remove (Delete) the browse sort column(s). These are the data elements used to sort the records displayed in your Browse Box. Clicking on the column header activate the column sort.

 

Add Primary Key Fields

Press this button to automatically include all fields defined in the primary key as sort columns.

 

Add a Key Field

Press this button to select any key from the primary table, and apply it to the sort columns.

 

The Sort Columns dialog also provides the following prompts:

 

Column

Select a column name from the drop list provided to use as criteria for the browse filter or range. The columns displayed are those columns you have defined in the Data Columns/Hot Fields dialog.

 

Direction

Choose ASC from the drop list to designate an ascending sort, or DESC to specify a descending sort.

 

SQL Tab Prompts

The SQL tab control displays SQL statements that are generated, based on the settings displayed on the Fields tab.

 

Essentially, the settings of the Range or Filter are used to generate the SQL WHERE clause, and the Sort Columns setting are used to generate the ORDER BY clause.

 

Regenerate WHERE SQL

Press this button to regenerate the template SQL WHERE statement that is constructed based on your default settings.

 

Override WHERE SQL

Check this box to override the default template generated WHERE clause.

 

Enter a WHERE clause to filter this list

The text in this box provides the Where clause, which is concatenated to the Select statement.

 

Regenerate ORDER BY SQL

Press this button to regenerate the template SQL ORDER BY statement that is constructed based on your default settings.

 

Override ORDER BY SQL

Check this box to override the default template generated ORDER BY clause.

 

Enter the ORDER BY clause

The text in this box provides the Order clause, which is concatenated to the other parts of the statement.

 

Test SQL Query

Press this button to test the SQL statement. If a statement clause is not overridden, the statement will be regenerated based on the template’s current settings.

 

 

Conditional Behavior

The Conditional Behavior tab duplicates the prompts and resultant functions found on the Default Behavior tab, with the addition of a Condition prompt:

 

Condition

Enter any valid Clarion expression.

 

Colors

The Colors dialog of the ADO Browse template presents the exact functionality as the standard browse box.

 

Browse Box Colors

 

Icons

The Icons dialog of the ADO Browse template presents the exact functionality as the standard browse box.

 

Browse Box Icons

 

Styles

The Styles dialog of the ADO Browse template presents the exact functionality as the standard browse box.

 

Browse Box Styles

 

Tooltips

The Tooltips dialog of the ADO Browse template presents the exact functionality as the standard browse box.

 

Browse Box Tooltips

 

Totaling

The Totaling dialog of the ADO Browse template presents the exact functionality as the standard browse box.

 

Browse Box Tooltips

 

Classes

Use the Classes tab to override the global settings for the Class. See Classes Tab.