• Registered: 2014-12-02
  • Posts: 136

Topic: Example of loading tablegrid from sqlexecute

Can you provide an example of loading a tablegrid from a sqlexecute command via a script?

I don't see a way to directly assign the output of the sqlexecute to the tablegrid.

Thank you,
  Rob

  • Registered: 2014-02-14
  • Posts: 10,294

Re: Example of loading tablegrid from sqlexecute

I added this feature in the beta version 1.50
https://www.dropbox.com/s/0m799p8qxh5kb … 0.zip?dl=0


Project example:
http://myvisualdatabase.com/forum/misc. … download=1



Script:

procedure Form1_bScript_OnClick (Sender: string; var Cancel: boolean);
begin
    Form1.GridEmployees.dbSQL:='SELECT id, lastname, firstname, salary FROM employees'; // the id field, want to be able to edit or delete the entry from the table component
    Form1.GridEmployees.dbGeneralTable := 'employees'; // Optional (in the case of complex SQL queries with sub queries, you need to choose the main table of the database, also it need to be able to edit or delete the entry from the table component)
    Form1.GridEmployees.dbListFieldsNames :='delete_col,name2,name3,name4'; // If you do not want to see the value of the id in the component table, enter a name for the column delete_col
    Form1.GridEmployees.dbSQLExecute;
end;
Dmitry.
  • Registered: 2014-12-02
  • Posts: 136

Re: Example of loading tablegrid from sqlexecute

DriveSoft wrote:

I added this feature in the beta version 1.50
https://www.dropbox.com/s/0m799p8qxh5kb … 0.zip?dl=0


Project example:
http://myvisualdatabase.com/forum/misc. … download=1



Script:

procedure Form1_bScript_OnClick (Sender: string; var Cancel: boolean);
begin
    Form1.GridEmployees.dbSQL:='SELECT id, lastname, firstname, salary FROM employees'; // the id field, want to be able to edit or delete the entry from the table component
    Form1.GridEmployees.dbGeneralTable := 'employees'; // Optional (in the case of complex SQL queries with sub queries, you need to choose the main table of the database, also it need to be able to edit or delete the entry from the table component)
    Form1.GridEmployees.dbListFieldsNames :='delete_col,name2,name3,name4'; // If you do not want to see the value of the id in the component table, enter a name for the column delete_col
    Form1.GridEmployees.dbSQLExecute;
end;

I ran into an issue where I needed to have the explicit quotes(') in the actual generated SQL to get a successful match.  I ended up using 3 quotes on the left side and four on the right side (line of code below). Not sure I would have expected this many quotes - usually I have doubled to have a literal quote.  Is this expected?  It seems when I have dash(-) embedded in the string, you need quotes to set off the string or it does not match successfully.

frmOrdrDetails.TableGrid1.dbSQL:='select a.id, a.item, a.quantity, a.[Transaction Price] from supplies a, Orders b where  a.id_Orders = b.id and b.[Order Number]=''' + frmEditOrder.edOrderNumber.Text + '''';

  • Registered: 2014-02-14
  • Posts: 10,294

Re: Example of loading tablegrid from sqlexecute

Your code is correct:

frmOrdrDetails.TableGrid1.dbSQL:='select a.id, a.item, a.quantity, a.[Transaction Price] from supplies a, Orders b where  a.id_Orders = b.id and b.[Order Number]=''' + frmEditOrder.edOrderNumber.Text + '''';

Script uses single quotes for its string delimiter.  To include a single
quote character in a string literal, double up each single quote where
needed.

Dmitry.
  • Registered: 2014-12-02
  • Posts: 136

Re: Example of loading tablegrid from sqlexecute

How can I add Sum to the footer through script?

Rob

  • Registered: 2014-02-14
  • Posts: 10,294

Re: Example of loading tablegrid from sqlexecute

rjkantor wrote:

How can I add Sum to the footer through script?

Rob


procedure Form1_TableGrid1_OnChange (Sender: string);
begin
     Form1.TableGrid1.Columns[1].Footer.FormulaKind := fkSum; // for first column
     Form1.TableGrid1.CalculateFooter;
end;
Dmitry.
  • Registered: 2014-12-02
  • Posts: 136

Re: Example of loading tablegrid from sqlexecute

I am not seeing the footer.  Do I need to redownload a new copy of the beta?

Rob

  • Registered: 2014-02-14
  • Posts: 10,294

Re: Example of loading tablegrid from sqlexecute

Sorry, I forgot something.

procedure Form1_TableGrid1_OnChange (Sender: string);
begin
     Form1.TableGrid1.Columns[0].Footer.FormulaKind := fkSum; // for first column
     Form1.TableGrid1.CalculateFooter;
end;

begin
    Form1.TableGrid1.Options := Form1.TableGrid1.Options + goFooter;
end.
Dmitry.
  • Registered: 2014-12-02
  • Posts: 136

Re: Example of loading tablegrid from sqlexecute

Thank you.  That works by adding the footer option.

One note, the column value is zero base so the number needs to be one less than the column intended.

Rob