getDataSetByQuery problem

Classic Servoy
1

I am using getDataSetByQuery to identify a set of records that I want to appear on a form.

When I run the code, the form loads but shows ALL of the records in the table on which the form is based.

I put an application.output(query) statement in the code so I could grab the actual SQL being used, and when I run just that SQL it returns the right values.

Here is a copy of my code:

var myPanel = forms.panels_one_member.id_panels
var myProj = forms.panels_one_member.id_projects
var query = 'SELECT id_panels, id_projects, id_people FROM panel_members WHERE id_panels = ' +  myPanel + ' and id_projects = ' + myProj;
application.output(query)
var pkdataset = databaseManager.getDataSetByQuery(controller.getServerName(), query,null,200);
forms.panel_members.controller.loadRecords(pkdataset)
application.showFormInDialog(forms.panel_members,5,130, 510, 500,  " Panel Members ")

The primary key of the panel_members table is id_panels, id_projects, id_people.

Can you tell me what I am doing wrong?

2

are those 3 column you select the pks of the panel_members table?
If not try selecting the pk only nothing more.

3

Yes. As I said in my previous post, the three fields are the primary key.

"The primary key of the panel_members table is id_panels, id_projects, id_people. "

4

sorry overlooked.
Then i need an example demonstrating this…

5

Here is a sample. To see the problem, click on the Show Panel Members button on the Panel tab. The form that pops up should only show the members of the selected panel, but all panel members are displayed.

The application.output(query) line of the ShowPanelMembers method displays the correct Sql – but the form doesn’t show the records that Sql returns.

Thanks for your help!

6

ahh i forgot..
Because we have to generate a IN query for such a thing you can’t set a multy key dataset in loadRecords.

this is the script you need that works:

var myPanel = forms.panels_one_member.id_panels
var myProj = forms.panels_one_member.id_projects
var query = 'SELECT panel_members.id_panels, panel_members.id_projects, panel_members.id_people FROM panel_members WHERE panel_members.id_panels = ' +  myPanel + ' and panel_members.id_projects = ' + myProj + ' order by panel_members.id_panels, panel_members.id_projects, panel_members.id_people';
forms.panel_members.controller.loadRecords(query)
application.showFormInDialog(forms.panel_members,5,130, 510, 500,  " Panel Members ")

So set directly the sql instead of first load the data youreself through a dataset.

7

OK. Thats so much simpler! Thanks!

8

Looks like I may have spoken too soon.

When I use your revised code I do indeed get a smaller recordset with the right number of records, but the values that are displayed on the form are not the right values although if I run the SQL that the form generates against the Sybase DB I do get the right values.

For example, if I click on the fourth tab_panel record on the Paula Thompson people record and then click on the Show Panel Members button, I get three records displayed in the popup form – but all with the same name – when it should be three different names.

9

yes i overlooked one thing.
The pk columns needed to be sorted or else the mapping goes wrong.

var myPanel = forms.panels_one_member.id_panels
var myProj = forms.panels_one_member.id_projects
var query = 'SELECT panel_members.id_panels, panel_members.id_people, panel_members.id_projects FROM panel_members WHERE panel_members.id_panels = ' +  myPanel + ' and panel_members.id_projects = ' + myProj + ' order by panel_members.id_panels, panel_members.id_projects, panel_members.id_people';
forms.panel_members.controller.loadRecords(query)
application.showFormInDialog(forms.panel_members,5,130, 510, 500,  " Panel Members ")
10

That did it.

Thanks!