Select_Single - type field - error

A

Ap.Muthu

Guest
When a field is made to be a single select combo box derived from a SQL statement, the field doesn't retrieve the existing value into an edit field - the edit field is blank. Incidentally the "Select_Single" SQL statement refers to a table that has a character field as the primary key.

Sample dumps are given below for re-creating the said error.

# Primary Table

CREATE TABLE `routes` (
`RCODE` int(10) unsigned NOT NULL auto_increment,
`ORG` char(3) NOT NULL default '',
`DST` char(3) NOT NULL default '',
`VIAPATH` text,
PRIMARY KEY (`RCODE`)
) TYPE=MyISAM;

INSERT INTO `routes` VALUES("1","MAA","KKD","TRY MDU");
INSERT INTO `routes` VALUES("3","KKD","MAA","MDU TRY");
INSERT INTO `routes` VALUES("4","MAA","CBE","TRY SLM");
INSERT INTO `routes` VALUES("5","CBE","MAA","SLM TRY");
INSERT INTO `routes` VALUES("6","CBE","KKD","PLN DGL MDU");
INSERT INTO `routes` VALUES("7","KKD","CBE","MDU DGL PLN");


# Reference Table for Select Single Lookup

CREATE TABLE `places` (
`ABR` char(3) NOT NULL default '',
`PLACE` varchar(20) NOT NULL default '',
`PABR` char(3) NOT NULL default '',
PRIMARY KEY (`ABR`)
) TYPE=MyISAM;

INSERT INTO `places` VALUES("MAA","Madras","TN");
INSERT INTO `places` VALUES("TN","TamilNadu","IN");
INSERT INTO `places` VALUES("IN","India","");
INSERT INTO `places` VALUES("CBE","Coimbatore","TN");
INSERT INTO `places` VALUES("KKD","Karaikudi","TN");

# Internal Table

CREATE TABLE `routes_intdb` (
`name_field` varchar(50) NOT NULL default '',
`present_insert_form_field` enum('0','1') NOT NULL default '1',
`present_ext_update_form_field` enum('0','1') NOT NULL default '1',
`present_search_form_field` enum('0','1') NOT NULL default '1',
`required_field` enum('0','1') NOT NULL default '0',
`present_results_search_field` enum('0','1') NOT NULL default '1',
`check_duplicated_insert_field` enum('0','1') NOT NULL default '0',
`type_field` enum('text','textarea','password','insert_date','update_date','date','select_single','select_multiple_menu','select_multiple_checkbox','ID_user','password_record') NOT NULL default 'text',
`separator_field` char(2) NOT NULL default '~',
`content_field` enum('alphabetic','alphanumeric','numeric','web','email','phone','city') NOT NULL default 'alphanumeric',
`select_options_field` text NOT NULL,
`foreign_key_field` text NOT NULL,
`db_primary_key_field` varchar(255) NOT NULL default '',
`select_type_field` varchar(50) NOT NULL default 'exactly/like/>/<',
`prefix_field` text NOT NULL,
`default_value_field` text NOT NULL,
`label_field` varchar(50) NOT NULL default '',
`width_field` char(3) NOT NULL default '',
`height_field` char(3) NOT NULL default '',
`maxlength_field` char(3) NOT NULL default '100',
`hint_insert_field` varchar(100) NOT NULL default '',
`order_form_field` smallint(6) NOT NULL default '0',
PRIMARY KEY (`name_field`)
) TYPE=MyISAM;


INSERT INTO `routes_intdb` VALUES("RCODE","0","1","1","0","1","0","text","~","alphanumeric","","","","exactly/like/>/<","","","Route Code","","","","","1");
INSERT INTO `routes_intdb` VALUES("ORG","1","1","1","0","1","0","select_single","~","alphanumeric","","SQL: SELECT DISTINCT ABR, PLACE FROM places WHERE LENGTH(RTRIM(ABR)) = 3\r\n","","exactly/like/>/<","","","Origin","","","3","","2");
INSERT INTO `routes_intdb` VALUES("DST","1","1","1","0","1","0","select_single","~","alphanumeric","","SQL: SELECT DISTINCT ABR, PLACE FROM places WHERE LENGTH(RTRIM(ABR)) = 3\r\n","","exactly/like/>/<","","","Destination","","","3","","3");
INSERT INTO `routes_intdb` VALUES("VIAPATH","1","1","1","0","1","0","text","~","alphanumeric","","","","exactly/like/>/<","","","Via Path Places","","","","","4");
 
E

Eugenio

Guest
Ap.Muthu wrote:
>
> When a field is made to be a single select combo box derived
> from a SQL statement, the field doesn't retrieve the existing
> value into an edit field - the edit field is blank.
> Incidentally the "Select_Single" SQL statement refers to a
> table that has a character field as the primary key.
>
> Sample dumps are given below for re-creating the said error.
>
> # Primary Table
>
> CREATE TABLE `routes` (
> `RCODE` int(10) unsigned NOT NULL auto_increment,
> `ORG` char(3) NOT NULL default '',
> `DST` char(3) NOT NULL default '',
> `VIAPATH` text,
> PRIMARY KEY (`RCODE`)
> ) TYPE=MyISAM;

That's because you are inserting just the abbreviation (e.g. MAA) and not all the choice (which is MAA - Madras), your ORG/DST is char(3).
So DaDaBIK compare, for example, "MAA" with "MAA - Madras" and, being them different, doesn't select any value in the select field.

Also in this case, the character field as the primary key is not the problem.

(Latest version of DaDaBIK when this message was posted: 1.6)
 
Top