setValueListItem - displays only 1 column

Veges · October 31, 2009, 8:20am

Hi,
I have the following code but the combo box only displays the fnameArray data, the snameArray is always blank.

Am not sure what the problem is ?

var query = 'select child_id, child_sname, child_fname from child where parent_id > 100 ';

var dataset = databaseManager.getDataSetByQuery(‘canteen’, query, null, maxReturedRows);
var idArray = dataset.getColumnAsArray(1);
var snameArray = dataset.getColumnAsArray(2);
var fnameArray = dataset.getColumnAsArray(3);

application.setValueListItems(‘vallist_child’, fnameArray, snameArray);

I also tried the following also but nothing was written to the combobox at all:

var outname = idArray + " " + fnameArray + " " + snameArray
application.setValueListItems(‘vallist_child’, outname);

cheers.

Hans_Nieuwenhuis · October 31, 2009, 9:45am

Hi,

As You can see in the doc and sample you must pass two array’s

application.setValueListItems(‘my_en_types’,new Array(‘Item 1’, ‘Item 2’, ‘Item 3’),new Array(10000,10010,10456));

so what if you change your query to :

select child_id, child_sname|| ’ ’ || child_fname colname from child where parent_id > 100 (return 2 columns)

var dataset = databaseManager.getDataSetByQuery(‘canteen’, query, null, maxReturedRows);
var idArray = dataset.getColumnAsArray(1);
var colnameArray = dataset.getColumnAsArray(2);

application.setValueListItems(‘vallist_child’, colnameArray, idArray); (first display values, second real values)

Veges · October 31, 2009, 1:04pm

Thanks for the suggestion, but it didn’t work, just got zeros returned (same applied when I ran the query in MYSQL).

I don’t understabd why my code doesn’t work as it looks to me to be exactly what the Devlopers manual see page 427 does, I’ve copied and pasted the following example code from the manual:

var maxReturedRows = 3;
var query = ‘select company_name, id from companies’;
var dataset =
databaseManager.getDataSetByQuery(controller.getServerName(),
query, null, maxReturedRows);
//put 1st column in array
var nameArray = dataset.getColumnAsArray(1);
//put 2nd column in array
var idArray = dataset.getColumnAsArray(2);
application.setValueListItems(valuelistName,nameArray, idArray);

patrick · October 31, 2009, 1:47pm

Setting two arrays in a value list does not have the purpose of showing both values, but showing one and return the other in your field.

Imagine you want to present a list of products in an order record. The user wants to see “Product abc”, but the order record wants the product ID. That is the situation described above and stated in the documentation:

var query = 'select company_name, id from companies';
var dataset =
databaseManager.getDataSetByQuery(controller.getServerName(),
query, null, maxReturedRows);
//put 1st column in array
var nameArray = dataset.getColumnAsArray(1);
//put 2nd column in array
var idArray = dataset.getColumnAsArray(2);

This will create a value list that displays the names and returns the ID.

So what is wrong with your approach. If you do this:

var dataset = databaseManager.getDataSetByQuery('canteen', query, null, maxReturedRows);
var idArray = dataset.getColumnAsArray(1);
var snameArray = dataset.getColumnAsArray(2);
var fnameArray = dataset.getColumnAsArray(3);

application.setValueListItems('vallist_child', fnameArray, snameArray);

your value list will show all child_fname and return child_sname. From what I understand that’s not what you want. You want to see fname AND sname in one and have child_id returned. This is what Hans tried to show. String concatenation differs between databases, but if || is the concatenation char in mySQL, it should work just as Hans wrote.

Another remark. What should this produce?

var outname = idArray + " " + fnameArray + " " + snameArray

all three variables are Arrays. You can’t just put two arrays together to get a string…

Hans_Nieuwenhuis · October 31, 2009, 3:34pm

Hi,

I made a small sample and used :

	var query = 'select id, concat(concat(fname,\' - \'), sname) from testpers'
	
	var dataset = databaseManager.getDataSetByQuery('user_data', query, null, -1);

	var idArray = dataset.getColumnAsArray(1);
	var nameArray = dataset.getColumnAsArray(2);

	application.setValueListItems('test1', nameArray, idArray);

This works fine.
Also see attached sample solution.

Regards,

Hans

test1.servoy (3.71 KB)

Veges · November 1, 2009, 2:09am

Thanks for your help guys, much appreciated.

Another thing how do I get the actual id value back into a variable ? I tried:

globals.cmbo_child_id = forms.frm_order_add.elements.lbl_child

and globals.cmbo_child_id contains:

“DataComboBox[lbl_child:Get_child:1]”

How do a get only the number in this case the number 1 or a null if nothing selected? Get_child is the dataprovider method used to create the combox.

lwjwillemsen · November 1, 2009, 10:03am

Hello,

I thought || was ansi sql for string concat ?

Hans_Nieuwenhuis · November 1, 2009, 10:09am

Hi,

I am an Oracle user (and always use ||), but did’nt know if MySql also uses || as concat.

Therefore I used Concat in the example.

Regards,

Hans

lwjwillemsen · November 1, 2009, 10:29am

Hi Hans,

I was only a bit afraid that Mysql does not comply with the ansi sql standards…

ROCLASI · November 1, 2009, 11:28am

lwjwillemsen:
I was only a bit afraid that Mysql does not comply with the ansi sql standards…

I just tested it on a MySQL 5.0.x install and || is not supported as a string concat, like Vegas already posted you get zeros. I also can’t find any reference of it in the MySQL documentation(s) (all the way to version 6.0). So your fear is real.

Hans_Nieuwenhuis · November 1, 2009, 12:06pm

Hi,

To get the data from a field you can use two methods :
(based on my sample solution)

I showed how to output it to console, you can just as easy put it in a global.
The first method will suffice almost always.
Just use the solution explorer : form - selected record and then select the appropiate field from the lower window. (see attached jpg)
The second one gives you the record, from the foundset, not just the field.

application.output('value= ’ + id)

2)

var current = foundset.getSelectedIndex();
var rec = foundset.getRecord(current)
application.output('val= ' + rec.id );


Regards,

Hans

![Untitled.jpg|1000x800](upload://6WSEcIoXGFQyN7k9ttNSniKWqv9.jpeg)

lwjwillemsen · November 1, 2009, 12:18pm

ROCLASI:

lwjwillemsen:
I was only a bit afraid that Mysql does not comply with the ansi sql standards…

I just tested it on a MySQL 5.0.x install and || is not supported as a string concat, like Vegas already posted you get zeros. I also can’t find any reference of it in the MySQL documentation(s) (all the way to version 6.0). So your fear is real.

Hi, Robert.

I saw after some Google that you can set the sql mode in mysql :

mysql> SET sql_mode = ‘ANSI’;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE VIEW test.v AS SELECT ‘a’ || ‘b’ as col1;
Query OK, 0 rows affected (0.01 sec)

We use Sybase ourselves…

ROCLASI · November 1, 2009, 12:38pm

Hi Lambert,

I almost forgot about the sqlmode setting. Of course it’s not enabled by default (same as strict SQL).
Still odd that their documentation don’t mention the double-pipe since they support it when in ANSI mode.

I use mostly PostgreSQL but also MySQL and also have to deal with MSSQL, Oracle and Sybase. PostgreSQL and Oracle are very compatible to each other and MSSQL and Sybase are compatible to each other too (since MSSQL is based on Sybase code that’s no wonder). But MySQL has always been the odd one out. Having different storage engines that support (or lack) different features doesn’t make it easier either.
It would be nice to have a comprehensive SQL standard that everyone supports (out of the box)…but I guess that will never happen.

Veges · November 1, 2009, 1:13pm

Thanks Hans

lwjwillemsen · November 1, 2009, 1:30pm

ROCLASI:
Hi Lambert,

I almost forgot about the sqlmode setting. Of course it’s not enabled by default (same as strict SQL).
Still odd that their documentation don’t mention the double-pipe since they support it when in ANSI mode.

I use mostly PostgreSQL but also MySQL and also have to deal with MSSQL, Oracle and Sybase. PostgreSQL and Oracle are very compatible to each other and MSSQL and Sybase are compatible to each other too (since MSSQL is based on Sybase code that’s no wonder). But MySQL has always been the odd one out. Having different storage engines that support (or lack) different features doesn’t make it easier either.
It would be nice to have a comprehensive SQL standard that everyone supports (out of the box)…but I guess that will never happen.

Thanks for your extra SQL info, Robert !

It’s a new (sql) world for us from the foxpro community and I of course totally agree with your last remark…

patrick · November 1, 2009, 8:31pm

As far as I know there is no standard for string concatenation. I have done some research one day and found no common ground among sybase, mysql, postgresql, oracle and sql server. Since then, I prefer to do that in code.

ROCLASI · November 1, 2009, 10:07pm

It’s true.
The fact that it’s ANSI SQL (since 1999 I believe) doesn’t mean it’s supported.
MSSQL doesn’t support it, in fact they say the support for “+” is sufficient enough (I suggest you register and start voting for this feature).
As far as I know Sybase iAnywhere also supports the double-pipe syntax and I know for sure Oracle and PostgreSQL support it.
So the odd ones out are MySQL (need to use SQLmode ANSI, which is non-default) and MSSQL which doesn’t support it at all.
But please, by all means, prove me wrong on this one.

Veges · November 3, 2009, 2:43am

Hi,

I’ve added combobox etc and all works fine on a Vista pc. I just installed servoy on an XP pc copied the servoy_workspace to the XP PC and ran the solution from Developer.

What I see is whenever I click on the combobox and change the value from blank to a value it corrupts the form being displayed by tiling bits of the form, see the attached display.

As said works fine on the Vista pc. Any ideas as all other components in the solution work fine, its just the combobox corrupting the form ? The form has a header (with the combobox), body (with a tabl panel) and footer with a tabpanel, its corrupting half the screen from half of the tabpanel display in the body.

Just edited the post as can now see there are other ways which can corrupt the form not just the combobox …

Topic		Replies	Views
HOW TO GET CUSTOM VALUELIST IN COMBOBOX Classic Servoy	8	14114	July 11, 2012
Problems with setValueListItems Classic Servoy	13	6819	May 12, 2006
array.concat Classic Servoy	2	1638	November 19, 2004
Valuelist of text returning integer Classic Servoy	24	34253	November 23, 2004
Dynamic Value List Classic Servoy	20	13205	August 25, 2005

setValueListItem - displays only 1 column

Related topics