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

Topic: Question on Query

I have a few questions:

Here is my query screen configuration

http://www.backdoortechnology.com/public/queryscreen.png

Here is the resultant SQL - notice It is missing a where clause - how do I fix this?

http://www.backdoortechnology.com/public/generatesql.png

Also, when I add a field in my query which creates an left outer join, the filter is applied to the left outer join rather than on the initial sql query.

What I am trying to achieve is to exclude the current record ($id) from the selected records.  I can't seem to get this to work w/ the sql query.

Let me know what I may be doing wrong.

Rob

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

Re: Question on Query

I thought I corrected this error, was used the latest version? if yes, could you send me your the project for test?
support@drive-software.com



You can't use $id in the Filter.
But you can do it using a script:

procedure Form1_Button1_OnClick (Sender: string; var Cancel: boolean);
begin
    Form1.TableGrid1.dbFilter := 'Transactions.id <> ' + IntToStr(Form1.TableGrid1.dbItemID);
    Form1.TableGrid1.dbUpdate;
end;
Dmitry.

3 Reply by rjkantor (edited by rjkantor 2015-02-26 02:58:20)

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

Re: Question on Query

http://www.backdoortechnology.com/public/GeneratedSQL150.png

I retested w/ the latest 1.50beta seems to be the same issue.

You have my prior zipped project - frmSupplies is the form in question.  You can add a filter and the generated code does not seem to be adding the where clause.

Rob

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

Re: Question on Query

DriveSoft wrote:

I thought I corrected this error, was used the latest version? if yes, could you send me your the project for test?
support@drive-software.com



You can't use $id in the Filter.
But you can do it using a script:

procedure Form1_Button1_OnClick (Sender: string; var Cancel: boolean);
begin
    Form1.TableGrid1.dbFilter := 'Transactions.id <> ' + IntToStr(Form1.TableGrid1.dbItemID);
    Form1.TableGrid1.dbUpdate;
end;

Is there a way to get other fields of the current record?  I would like to show other records from the same table that match the field "id_Orders".
I was thinking something like:

Form1.TableGrid1.dbFilter := 'Transactions.id_Orders =' +  form1.tablegrid1.Cells[dbItem, FieldByName('id_Orders')]

Anything like this possible?

Rob

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

Re: Question on Query

rjkantor wrote:

http://www.backdoortechnology.com/public/GeneratedSQL150.png

I retested w/ the latest 1.50beta seems to be the same issue.

You have my prior zipped project - frmSupplies is the form in question.  You can add a filter and the generated code does not seem to be adding the where clause.

Rob

Thank you, fixed, please downoad 1.50 beta again:
https://www.dropbox.com/s/0m799p8qxh5kb … 0.zip?dl=0

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

Re: Question on Query

rjkantor
Try it:

procedure Form1_Button1_OnClick (Sender: string; var Cancel: boolean);
var
    id_Orders: integer;
begin
    id_Orders := SQLExecute ('SELECT id_Orders FROM Transactions WHERE id=' + IntToStr(Form1.TableGrid1.dbItemID));
    Form1.TableGrid1.dbFilter := 'Transactions.id_Orders=' +  IntToStr(id_Orders);
    Form1.TableGrid1.dbUpdate;
end;
Dmitry.

7 Reply by rjkantor (edited by rjkantor 2015-02-27 02:41:55)

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

Re: Question on Query

I am receiving an access violation http://www.backdoortechnology.com/public/AccessViolation.png - How do I fix this condition?

I have a buttton that has a sql query action of:

select Transactions.[year], coin.[Description], Transactions.[Mint], Transactions.condition,
Transactions.[Quantity]  from Transactions left
outer join [coin] on transactions.[id_coin]=coin.[id]

and on onclick event for the same button

procedure frmTransaction_Button4_OnClick (Sender: string; var Cancel: boolean);
var
    id_Orders: integer;
begin
    id_Orders := SQLExecute ('SELECT id_Orders FROM Transactions WHERE id=' + IntToStr(frmTransaction.TableGrid1.dbItemID));
    frmTransaction.TableGrid1.dbFilter := 'Transactions.id <> ' + IntToStr(frmTransaction.TableGrid1.dbItemID)+ ' and Transactions.id_Orders= ' +  IntToStr(id_Orders);
    frmTransaction.TableGrid1.dbUpdate;
end;

If I comment out the dbfilter assignment in the onclick event  the access violation goes away.  The av seems to be related to setting the dbfilter.

Please advise - I am using the latest 1.50 beta.

I've attached a demo to recreate the issue.

Rob

Post's attachments

Attachment icon Demo.zip 331.43 kb, 443 downloads since 2015-02-27 

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

Re: Question on Query

In light of the access violation, I tried putting all the sql into the onclick event, although the dbItemId is returning -1.

Ideas?

Thank you,
  Rob

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

Re: Question on Query

rjkantor
Sorry for delay.
Please, send me your project, which receiving an access violation.
In your attached file Demo.zip no script.



and you must escape keyword Transaction in SQL query, like [Transaction] (is a reserved word)

select [Transaction].[year], coin.[desc] from [Transaction]
left outer join [coin] on [transaction].[id_coin]=coin.[id]
Dmitry.

10 Reply by rjkantor (edited by rjkantor 2015-03-03 23:35:30)

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

Re: Question on Query

Here is the demo w/ script.

Post's attachments

Attachment icon Demo.zip 332.85 kb, 449 downloads since 2015-03-04 

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

Re: Question on Query

The reason is that your try Update data (Form1.TableGrid1.dbUpdate) in TableGrid that has no data.
unfortunately I can not figure out what exactly you want to implement?

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

Re: Question on Query

Dmitry -

It does not seem that I can have a [SQL Query] Action with an onclick event.  It raises an access violation.  It seems I need to use a script.

Rob