[Gambas-user] Query for sorting not work

[gian]

Il 03/09/22 19:19, Benoit Minisini ha scritto:
> Oops, don't do that.
> 
> The Exec() method substitutes using the Connection.Subst() method, that 
> works for SQL values only.
> 
> For SQL column names, you must use the Connection.Quote() function. 
> There is even a boolean argument for quoting table names, because on 
> some SQLs, you don't quote table names and column names the same way!
> 
> <sarcasm>SQL... I love you!</sarcasm>
> 
> Regards,
> 
> -- 
> Benoît Minisini.


Hi Benoit,

If I understand your suggestion correctly the sort routine:
'---------------------------------------------------------------------
'' Works correctly on any database (MySQL, PostgreSQL, SQLite, ODBC)
Public Sub GridView1_Sort()

   Dim aArg As String[] = ["uskey", "usnam", "ussur"]
   Dim sTable, sColumn, sSQL As String
   Dim sSort As String = " ASC"

   db.Debug = True
   sTable = "tuser"
   sColumn = aArg[$iCurrentColumn]
   sSQL = "SELECT * FROM " & DB.Quote(sTable, True) & " ORDER BY " & 
DB.Quote(sColumn, False)
   If Not GridView1.Columns.Ascending Then sSort = " DESC"
   $hResult = $hConn.Exec(sSQL & sSort)
   GridView1.Rows.Count = $hResult.Count
   GridView1.Columns.Width = -1

Catch
   Print Error.Text

End
'---------------------------------
works correctly on all databases.

While the routine:
'---------------------------------
'' Is this routine incorrect?
Public Sub GridView1_Sort()

Dim sSort As String

db.Debug = True
   If GridView1.Columns.Ascending Then
     sSort = "ORDER BY &1 ASC"
   Else
     sSort = "ORDER BY &1 DESC"
   Endif
   $hResult = $hConn.Find("tuser", sSort, $iCurrentColumn + 1)
   GridView1.Rows.Count = $hResult.Count
   GridView1.Columns.Width = -1

Catch
   Print Error.Text

End
'---------------------------------
does not work correctly, right?
Project attached

Thank you and regards

Gianluigi