Contents

 
Home
ASPRunner Professional 7.0 manual
Prev Page Next Page
 
 

Query Designer tab

 

Query Designer gives you the ability to use the simple graphical interface to construct SQL statements.

yellowbulbNote: if you modify a default SQL query, make sure that key column(s) are included into the fields list. This is required to provide  the edit/delete/view functionality.

qd_first

To switch between tables use Tables list panel on the left. Note that all fields marked with a tick in the Output column are added to the SELECT clause.

What is supported

Inner joins, outer joins

To add join, click the Add Table button, select table and then drag and drop any field from first table to the joined table. To setup join type click the line between tables, select foreign keys on the Table link properties dialog in both tables and choose join type.

qd_join1

qd_join2

For more information about using JOIN SQL queries, see Using JOIN SQL queries.

SQL query:

SELECT

carsmodels.id,

carsmodels.model,

carsmodels.make

FROM carsmodels

INNER JOIN carsmake ON carsmodels.make = carsmake.make

yellowbulbNote: It's recommended to use aliases for fields from joined tables to avoid confusion when two fields from different tables have the same name.

Calculated fields

To add calculated fields use empty grid below all field names:

qd_calculated

SQL query:

SELECT

Make,

Model,

YearOfMake,

Picture,

Price,

color,

zipcode,

features,

Price*0.1 AS Discount

FROM carscars

WHERE clause

You can add where clause in the Filter column. If you need to add two or more conditions use Or... columns.

qd_where

SQL query:

SELECT *

FROM carscars

WHERE YearOfMake =2004

For more complicated queries wrap the condition by parentheses:

SELECT *

FROM carscars

WHERE ( YearOfMake =2004 OR YearOfMake =2005 )

ORDER BY clause

If you'd like to specify default sorting order on the list page (ascending or descending) select Sort Type in the corresponding column for necessary fields.

GROUP BY clause

To add GROUP BY clause, click the Group By button and select one of grouping function in Group By column.

qd_orderby

SQL query:

SELECT

Make,

Model,

AVG (YearOfMake)

FROM carscars

GROUP BY Make, Model

ORDER BY Make

What is not supported

· Stored procedure calls (Workaround: create a view that calls stored procedure and use this view as a datasource)
· Update/Delete/Insert/Create queries
· Unions
· DISTINCT keyword

yellowbulbNote: If your query for some reason doesn't work create view/query in your database and use this query as a datasource in ASPRunnerPro.

1. Run MS Access and create new query. Switch to SQL view.
2. Type your SQL query there.
3. Save this query as qryNewQuery.
4. Run ASPRunnerPro and use qryNewQuery as datasource table.
Converted from CHM to HTML with chm2web Standard 2.85 (unicode)