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");
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");