Show/Hide Toolbars

Navigation: Using ASPRunnerPro > SQL query page

ASPrunnerPro32x32     ASPRunnerPro manual

Query Designer tab

Scroll Prev Next More


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.




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.


Limit data to first "N" rows.

This new cool feature allows you to limit the number of records to be displayed on List, Print, Export, Details pages.


It can be useful when you need to speed up the load of the webpage or your chart has too many items on it and you only need to show the most significant ones. It also works with list pages added to dashboard.


Search and filters will be applied first and then results will be limited to first "N" rows.

This option will work with all ASPRunnerPro project items except reports with that have group fields selected.

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.





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


SQL query:





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:



SQL query:











Price*0.1 AS Discount

FROM carscars


In the example above the alias Discount is assigned to the calculated field Price*0.1. Note that if the field was assigned an alias in the SQL query, then the values array will get the alias instead of field name from the database. So you should use values("Discount") instead of values("Price*0.1") in your events. For more information about events, see Events.


We do not recommend using aliases to give a field another name. If you have very long or complex field names, you can assign a label to the field on Choose fields page or in the Label editor instead of using aliases.


WHERE clause


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



SQL query:



FROM carscars

WHERE YearOfMake =2004


For more complicated queries wrap the condition by parentheses:



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.



SQL query:





AVG (YearOfMake)

FROM carscars

GROUP BY Make, Model


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




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.