AxpDataGrid is optimized for using SQL commands, including queries that returns a large number of rows. Data editing is SQL based, and the grid automatically constructs parameterized INSERT and UPDATE commands.
This section explains the different SQL command properties supplied by AxpDataGrid
The AxpDataGrid.SQL property is required when a SQL Query or Stored Procedure is used as the primary data source for the grid.
Any valid SELECT command can be used.
Examples
SQL="SELECT * FROM Products"
SQL="SELECT ProductID, ProductName FROM Products"
SQL="SELECT P.ProductID, P.ProductName, S.CompanyName FROM Products P, Suppliers S WHERE P.SupplierID=S.SupplierID"Using Sql Server syntax:
SQL="SELECT P.ProductID, P.ProductName, S.CompanyName FROM Products P LEFT OUTER JOIN Suppliers S ON P.SupplierID=S.SupplierID"
SQL="SELECT * FROM [Products Above Average Price]"
Note If the query contains an ORDER BY clause, the grid will disable the user interface to order columns. To set an initial column to order by, and then let the user have the ability to order the grid by himself, set the property OrderByFields.
AxpDataGrid can be display data in Form View and Form Edit mode.
If the FormSQL is not set (or EditSQL), the grid will use the main AxpDataGrid.SQL command to display data in Form View and Form Edit mode.
If FormSQL is specified, the command will be used to create the datasource for the Form View and Form Edit mode (unless a different EditSQL is also specified)
If FormSQL is used, it is important to include the data columns containing Primary Keys in both the main SQL and in the FormSQL property.
Other than that, the FormSQL can be any valid SELECT command (see the SQL examples above).
Example
SQL="SELECT P.ProductID, P.ProductName, S.CompanyName FROM Products P, Suppliers S WHERE P.SupplierID=S.SupplierID" FormSQL="SELECT * FROM Products"
The primary key columns can be hidden, but they will need to be included in the queries. To hide a column in grid mode, the property GridFieldsHide can be used, e.g. GridFieldsHide="ProductID". The corresponding FormFieldsHide can be used to hide columns in Form mode.
The EditSQL must be set to a simple table select query if the main SQL (or FormSQL) property is not a simple query. The EditSQL query must select from a single table and the column list must be either * (all columns) or a list of columns from the single table. All primary key columns must be part of the EditSQL.
Examples
EditSQL="SELECT * FROM Products"
EditSQL="SELECT ProductID, ProductName FROM Products"
If EditSQL is used, it is important to include the data columns containing Primary Keys in both the main SQL and in the EditSQL property (see the FormSQL Notes above).
The DeleteSQL must be set to DELETE FROM tablename. No where clause should be included.
Example
DeleteSQL="DELETE FROM Products"
AxpDataGrid will count the rows in the main SQL by enclosing it in a subquery, something like, SELECT COUNT(*) FROM SQL, and issue that command against the database.
This will work sufficiently well in most circumstances. If the automatically generated subquery count performs badly, or if the database engine does not support the query, CountSQL can be set.
Example
SQL="SELECT P.ProductID, P.ProductName, S.CompanyName FROM Products P, Suppliers S WHERE P.SupplierID=S.SupplierID" CountSQL="SELECT COUNT(*) FROM Products P, Suppliers S WHERE P.SupplierID=S.SupplierID"