Problem adding TableAdapter using dataset designer in Visual Studio 2008

[richard soppelsa]

Hi All,

I'm looking to port a SQL Server database to MySQL but have run into a problem using the dataset designer in Visual Studio 2008 (Windows Vista).

When I drop a simple table adapter onto the designer the tableadapter wizard runs and its datatable/table adapter is generated correctly.

However, when I create a tableadapter for a table which contains a foreign key the tableadapter wizard fails with the error:

Failed to add TableAdapter.
Column 'REFERENCED_TABLE_CATALOG' does not belong to table.

The SQL to create the simple and foreign key tables is below:

DROP TABLE IF EXISTS `db1_dbo`.`consumer`;
CREATE TABLE `db1_dbo`.`consumer` (
`consumerid` INT(10) NOT NULL,
`name` VARCHAR(50) NULL,
`deletedays` INT(10) NOT NULL,
PRIMARY KEY (`consumerid`)
)
ENGINE = INNODB;

DROP TABLE IF EXISTS `db1_dbo`.`organisation`;
CREATE TABLE `db1_dbo`.`organisation` (
`organisationid` VARCHAR(64) NOT NULL,
`consumerid` INT(10) NOT NULL,
`name` LONGTEXT NULL,
`configuration` LONGTEXT NULL,
`deleteconsumer` TINYINT NOT NULL,
PRIMARY KEY (`organisationid`),
INDEX `icons` (`consumerid`),
CONSTRAINT `FK_organisation_consumer` FOREIGN KEY `FK_organisation_consumer` (`consumerid`)
REFERENCES `db1_dbo`.`consumer` (`consumerid`)
ON DELETE CASCADE
ON UPDATE NO ACTION
)
ENGINE = INNODB;

I'm using MySQL Client version 5.1.11
and Connector/Net version 6.0.3

Having to manually create the datatables/tableadapters is a very length process so it would be good to resolve this.

many thanks
richard