[Gambas-user] Query for sorting not work

[gian]

Il 05/09/22 12:25, gian via User ha scritto:
> 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
> 
> ----[ http://gambaswiki.org/wiki/doc/netiquette ]----

Hi Benoit,

Right, the dumb questions must be answered on their own:

Public Sub GridView1_Sort()

   Dim aArg As String[] = ["uskey", "usnam", "ussur"]
   Dim sTable, sColumn As String
   Dim sSort As String = "SELECT * FROM [&1] ORDER BY '&2' ASC"

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

Catch
   Print Error.Text

End

Sorry :-(

Gianluigi