[Gambas-user] Query for sorting not work

[gian]

Il 04/09/22 19:13, T Lee Davidson ha scritto:
> On 9/4/22 12:15, Benoit Minisini wrote:
>> I'm currently thinking, maybe I could add new substitution syntaxes to 
>> tell Gambas if we want to substitute a value, a column name or a table name. 
>>
>>
>> Something like that:
>>
>> DB.Exec("SELECT * FROM [&1] WHERE column = &2 SORT BY '&3'", 
>> sTableName, sValue, sSortColumn)
> 
> That would make for cleaner looking SQL query building as opposed to 
> using string concatenation with the ampersand. I agree with Gianluigi 
> that what we already have is sufficient, but it would be a nice 
> enhancement.
> 
> If you decide to add that enhancement, may I suggest the use of the 
> back-tick (`) to mark column names since the single-quote is sometimes 
> used for data values? Ie.:
> DB.Exec("SELECT * FROM [&1] WHERE column = &2 SORT BY `&3`", sTableName, 
> sValue, sSortColumn)
> 
> 

Hi Benoit,

I tried the last master with MySQL and if I write the code like this it 
works, but I always have to use a concatenation string for ASC and DESC.
I read the wiki several times but I didn't understand how I could add 
sSort with the substitution method.
'-------------------------------------------------------
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 [&1] ORDER BY `&2`"
   ' or
   ' sSQL = "SELECT * FROM [&1] ORDER BY '&2'"
   If Not GridView1.Columns.Ascending Then sSort = " DESC"
   $hResult = $hConn.Exec(sSQL & sSort, sTable, sColumn)
   GridView1.Rows.Count = $hResult.Count
   GridView1.Columns.Width = -1

Catch
   Print Error.Text

End
'-------------------------------------------------------

Regards
Gianluigi