[Gambas-user] Query for sorting not work

Benoit Minisini benoit.minisini at gambas-basic.org
Sat Sep 3 19:19:07 CEST 2022

Le 03/09/2022 à 18:26, T Lee Davidson a écrit :
> On 9/3/22 08:26, gian via User wrote:
>> Hi List,
>> Is anyone able to explain to me why the most correct query (I think) 
>> for sorting the columns is the one that is not working?
>> See attached, to see the malfunction you need to comment out the first 
>> sort routine and uncomment the second one.
>> Thank you
>> Gianluigi
> It does not work because SQLite does not understand a single-quoted 
> column name. Data values should be single-quoted but not column names. I 
> think the code misuses the Connection.Find method. Try using the .Exec 
> method with the standard Subst function, eg.:
>    If GridView1.Columns.Ascending Then
>      $hResult = $hConn.Exec(Subst("select * from tuser ORDER BY &1 ASC", 
> aArg[$iCurrentColumn]))
>    Else
>      $hResult = $hConn.Exec(Subst("select * from tuser ORDER BY &1 
> DESC", aArg[$iCurrentColumn]))
>    Endif

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>


Benoît Minisini.

More information about the User mailing list