I am doing an insert using ExecuteNonQuery on a table that has a structure like
id bigint(20) NOT NULL, name varchar(45)
There is an insert trigger that sets id if not supplied. An insert such as INSERT test (name) values(@val) works ok in mysqlbrowser but fails when calling executeNonQuery with an error 'Field 'id' doesnt have a default value'. My question is, is the provider doing something that mysqlbrowser isn't in order to cause this error?
Thanks
Ian
ExectureNonQuery gets schema?
Here's the table definition and sample query:
INSERT INTO testtbl SET name = 'cxcc';
SET NAMES utf8;
SET SQL_MODE='';
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
/*Table structure for table `testtbl` */
CREATE TABLE `testtbl` (
`id` bigint(20) NOT NULL,
`name` varchar(14) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*Trigger structure for table `testtbl` */
DELIMITER $$
DROP TRIGGER `ins`$$
CREATE TRIGGER `ins` BEFORE INSERT ON `testtbl` FOR EACH ROW BEGIN
if NEW.id = 0 or NEW.id IS NULL THEN
SET NEW.id = COALESCE((SELECT MAX(id)+1 FROM testtbl), 1);
END IF;
END$$
DELIMITER ;
SET SQL_MODE=@OLD_SQL_MODE;
INSERT INTO testtbl SET name = 'cxcc';
SET NAMES utf8;
SET SQL_MODE='';
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
/*Table structure for table `testtbl` */
CREATE TABLE `testtbl` (
`id` bigint(20) NOT NULL,
`name` varchar(14) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*Trigger structure for table `testtbl` */
DELIMITER $$
DROP TRIGGER `ins`$$
CREATE TRIGGER `ins` BEFORE INSERT ON `testtbl` FOR EACH ROW BEGIN
if NEW.id = 0 or NEW.id IS NULL THEN
SET NEW.id = COALESCE((SELECT MAX(id)+1 FROM testtbl), 1);
END IF;
END$$
DELIMITER ;
SET SQL_MODE=@OLD_SQL_MODE;