Topic: SQLQuery and grid formatting

Good Afternoon Dmitry,
A bit of help please.
I have 2 currency fields, both defined to 2 decimal places and with a ',' as a thousands separator.
In my tablegrid when all rows are retrieved, this is displayed correctly, for example 1,234.00 (dmitry 1.jpg).
After doing an SQLQuery search, the formatting is lost and displays as 1234 (dmitry 2.jpg).
Can the correct format be specified as part of the SQLQuery?
If not, what is the syntax for specifying it in the script?
Thanks,
Derek.

Post's attachments

Attachment icon companycars one last go.zip 874.52 kb, 822 downloads since 2015-11-11 

Re: SQLQuery and grid formatting

Hello,


When you use SQL, you will get raw data from database, so you should formatting this raw data manually, in your case:

printf('%.2lf', fieldname)

but without the thousands.



I can suggest another way, using this script:

procedure Form1_Edit9_OnChange (Sender: string);
var
    s: string;
begin
    s := '''%'+Form1.Edit9.Text+'%''';
    if s <>'' then
    Form1.TableGrid1.dbFilter :=
        '((cars.regno like '+s+') or'+
        '(maker.maker like '+s+') or'+
        '(model.model like '+s+') or'+
        '(cars.driver like '+s+') or'+
        '(cars.colour like '+s+') or'+
        '(cars.cc like '+s+') or'+
        '(cars.engineno like '+s+') or'+
        '(cars.purchaseprice like '+s+') or'+
        '(cars.disposalprice like '+s+'))'
    else Form1.TableGrid1.dbFilter := '';
    Form1.TableGrid1.dbUpdate;
end;


Also fixed project attached.

Post's attachments

Attachment icon companycars simplified_fixed2.zip 52.49 kb, 882 downloads since 2015-11-12 

Dmitry.

Re: SQLQuery and grid formatting

Hello Dmitry,
I have tried both your solutions and they work well.
I prefer your idea of putting the SQLQuery code into the script so that all of the grid formatting is properly maintained - looks much better.
As always, thank you for your help.
Have a good day,
Derek.

Re: SQLQuery and grid formatting

hello, I tried to use this script to perform a dynamic search in tablegrid, filtering the data with dbfilter. The problem I can not solve is  when I insert the character  '  in the textbox(for example if I have to try D'orogold) when I insert the ' character generate an error. How can I fix? Thank you.

Re: SQLQuery and grid formatting

Hello madbit71

The (') is a reserved character to Delphi (therefore to MVD).
I'm French and in my native language we use many words includin this character (i.e. fot today we use aujourd'hui).

The solution consist to double this character (') so it will get ('')..
Pay attention, don't use double quote, enter twice (') without parenthesis.
It will make job.

JB

Re: SQLQuery and grid formatting

thanks JB...i know. My problem is when i digit the char '  in the textbox an error, of course, is generate. How can i, using a script, double the ' char when i press on the keyboard the char '? Tks and sorry for bad english.

Re: SQLQuery and grid formatting

Ok, i solved in this way:

procedure.....
var
    s,st1: string;
begin
    s := '%'+Form1.Edit1.Text+'%';

//doppio l'apice

st1:=ReplaceStr(s,'''','''''') ;

//assegno gli apici prima e dopo il testo

s := '''%'+st1+'%''';

    if s <>''  then........

Tks.

Re: SQLQuery and grid formatting

Hello madbit71

By the bye, when I use a double ('), I've noticed if I edit  a record first entered with those '', I can change this text with only one (')
and it is saved without error message and with correct spelling.

I'm looking for a script to correct it and I come back to you.

Perhaps Dmitry has an explanation.

JB

Re: SQLQuery and grid formatting

Instead

Form1.Edit1.Text

try to use

Form1.Edit1.sqlValue
Dmitry.

Re: SQLQuery and grid formatting

Derek, Just to interject a thought here. I know you moved your SqlQuery into a script, but if you ever want to use SqlQuery in the future, you can still format your currency fields. I used to use a For loop through the tablegrid to format currencies, but Dimitry introduced in 3.3 the following component for formatting currencies in a tablegrid. I had to change my way of doing it because it stopped working in 3.3. There is a recent post in the forum regarding this subject.

if form1.TableGrid1.Columns[7] is TNxNumberColumn then
      begin
        TNxNumberColumn(form1.TableGrid1.Columns[7]).FormatMask := '#,###,##0.00';
      end;

Re: SQLQuery and grid formatting

Hi EHW,
Thanks for that - I try to keep up to date with posts in the forum as the best way of finding out new ways to do things, new features etc, but I think I must have missed this one.
I was on 3.1 but have just upgraded to 3.4 beta so I'll find the project that my original post related to and give it a shot.
Regards,
Derek.