Alfredo C.
|
| Posted: 09/02/2002, 6:15 AM |
|
When I create a custom SQL string in Form Properties SQL tab, I get an SQL error when I clic the caption field in order to sort. CC 2.0.5 generates a new SQL instruction adding dots "." prior to the sorting fields, in this example is: order by .costo_bolivares ASC
CC 2.0.5 generates the following PHP code:
if ($iSort == 1) $sOrder = " order by .productoppg_codigo" . $sDirection;
if ($iSort == 2) $sOrder = " order by a.moneda_nombre" . $sDirection;
if ($iSort == 3) $sOrder = " order by .costo" . $sDirection;
if ($iSort == 4) $sOrder = " order by .costo_bolivares" . $sDirection;
if ($iSort == 5) $sOrder = " order by .proveedor_empresa_nombre" . $sDirection;
Please also note that CC is also adding a letter "a":
if ($iSort == 2) $sOrder = " order by a.moneda_nombre" . $sDirection;
I modified the generated code as follows and the problem was solved:
if ($iSort == 1) $sOrder = " order by productoppg_codigo" . $sDirection;
if ($iSort == 2) $sOrder = " order by moneda_nombre" . $sDirection;
if ($iSort == 3) $sOrder = " order by costo" . $sDirection;
if ($iSort == 4) $sOrder = " order by costo_bolivares" . $sDirection;
if ($iSort == 5) $sOrder = " order by proveedor_empresa_nombre" . $sDirection;
If I create a custom SQL string, I am not able to sort by a predefined field i.e. costo_bolivares I solved changing the generated code adding the following line:
$sOrder = " order by costo_bolivares" . $sDirection;
after the line:
$form_sorting = "";
Please see below:
//-------------------------------
// Build ORDER BY statement
//-------------------------------
$iSort = get_param("FormPrecios_Sorting");
$iSorted = get_param("FormPrecios_Sorted");
if(!$iSort)
{
$form_sorting = "";
$sOrder = " order by costo_bolivares" . $sDirection;
}
else
QUESTIONS:
If there is a way to do the above without modifying the generated code I would appreciate your advise. If it is a bug I would also suggest to fix it in order to give CC 2.0.5 more flexibility with custom SQL strings.
Thank you.
Attached the SQL error message:
Database error: Invalid SQL: select avinet_precios_proveedor.moneda_no, avinet_precios_proveedor.productoppg_codigo, avinet_cambio.moneda_no, avinet_cambio.moneda_nombre, avinet_precios_proveedor.costo, avinet_proveedores.proveedor_empresa_nombre, avinet_precios_proveedor.costo*avinet_cambio.bolivares as costo_bolivares, avinet_precios_proveedor.proveedor_no from avinet_precios_proveedor, avinet_proveedores, avinet_cambio where avinet_precios_proveedor.proveedor_no like '%%' and avinet_precios_proveedor.proveedor_no=avinet_proveedores.proveedor_no and avinet_precios_proveedor.moneda_no=avinet_cambio.moneda_no and avinet_precios_proveedor.productoppg_codigo like '%%' order by .costo_bolivares ASC
MySQL Error: 1064 (You have an error in your SQL syntax near 'ASC' at line 1)
Session halted.
|
|
|
 |
Alfredo C.
|
| Posted: 09/04/2002, 6:43 PM |
|
When I create a custom SQL string in Form Properties SQL tab, the SQL works fine until I try to sort the data on the page. I get an SQL error when I clic the caption field in order to sort the data displayed. CC2.0.5 is adding dots "." and "a" to the "order by" instruction and miss the original parameters passed to the form. Please see details above.
QUESTION:
If there is a way to do the above without modifying the generated code, I would appreciate your advise. If it is a bug I would also suggest to CC developers to fix it in order to give CC 2.0.5 more flexibility with custom SQL strings.
Thank you.
|
|
|
|
|
