GreatManuals.com
Huge Collections of Software Manuals and Knowledgebase

Custom View Setup

Custom View allows you to save your SELECT statements in a project and use the results like normal tables. If your database, for example, MySQL 4, does not support views, you'll find this feature extremely useful. Even if your database support views, you'll also find it handy as you can create different versions of ASP pages with Custom Views for the same table/view in a single project.

A Custom View is basically a SELECT statement. Custom Views give you an option to save your SELECT statements right within the ASP XMLMaker interface, they are, however, not to replace views provided by the database. Views provided by database allow you to use them like regular tables, though views are usually not updatable. Also, query builder provided by database itself, for example, that provided by Access or SQL Server, ensures that the generated SQL is 100% compatible with the database. You should always use views provided by your database whenever available.

After loading the database in ASP XMLMaker, the database objects (tables, views and custom views) will be shown in the left pane (the database pane). To create a custom view, right click the database pane and select [Add Custom View]. Alternatively, you can click [Edit] in the main menu and then select [Add Custom View].

The Custom View Setup window will show up:

ASP XMLMaker will give the new Custom View a temporary name, CustomView<n>, where n is an integer. If you want to change the name, enter a new name in the [Custom View name] edit box in the main toolbar.

It is recommended that the built-in visual query builder be used to build your SQL. The query builder interface is intuitive and fully supports drag-n-drop, in most cases you don't need to type the table or field names.

On the left hand side, the available tables are displayed in the table pane. On the right hand side, there are three tabs: [Builder], [SQL] and [Result]. (The [Result] tab will only be visible after you execute the SQL.)

Builder

To build your SQL, drag your tables from the table pane on the left to the builder area and check the required fields.

Note: You should select your fields explicitly, not using the * symbol.

To create joins, drag a field from one table to its linked field in an other table.

Note: Always drag from the main table to its detail table or lookup table. Visually, it usually means you should drag from a table with more links to an other table with less or no links. Reversing the dragging direction may result in a different SQL not compatible with your database.

If you need to add criteria (WHERE clauses), click the small [...] button on the [Criteria] tab and select [Add condition]. A blank condition will appear, you can enter a field to the left side by any of the following ways:

• Drag a field from its table in the builder area, or
• Click the left side of the condition, right click and select [Insert field], or
• Click the left side of the condition, press Shift+Enter

Then you can enter your condition to the right side. Repeat for more conditions. You can make composite conditions (using AND, OR, etc. for conditions) by clicking the number button of an existing condition and then [Make composite condition] to add another condition.

In the [Selection] tab, you can choose to use DISTINCT, change aliases, add aggregates (and hence a GROUP BY clause) for fields.

In the [Sorting], you can build your ORDER BY clause easily.

When necessary, you can click [View] and then [Query Builder Options...] to enable the [Grouping Criteria] tab which allowing you to build a HAVING clause for your SQL. There are many other options for the query builder in the options dialog box, but usually you don't need to change them.

SQL

You can always click the [SQL] tab to check the SQL generated by the query builder, the SQL editor is also syntax-highlighted to enhance the readability of the SQL.

»