Re: MySqlBinary exception without any MySqlBinary parameter

[Marty Wondergem]

I'm using MySQL 4.1.7-nt and VisStudio 1.1.4322

I'm getting the same error trying to run a dataadapter update on this table:

CREATE TABLE `tblcustomers` (
`CustID` int(11) NOT NULL auto_increment,
`AS400ID` varchar(20) NOT NULL default '',
`CompanyName` varchar(50) NOT NULL default '',
`AllenCust` char(1) NOT NULL default 'O',
`Address1` varchar(255) default '',
`Address2` varchar(255) default '',
`CustCity` varchar(255) default '',
`CustState` varchar(50) default '',
`CustZip` varchar(50) default '',
`CustCountry` int(11) default '1',
`Min Freight Weight` int(11) default '999999',
`MinFreightOverride` tinyint(1) default '0',
`MinTrimBoxes` double default '0',
`Terms` int(11) default '10',
`Shipping Method` int(11) default '1',
`CreditRating` varchar(50) default 'X',
`CreditAppOnFile` tinyint(1) default '0',
`CreditAvailability` int(11) default '10000',
`ActiveDate` datetime default '0000-00-00 00:00:00',
`CustomerNotes` text,
`GrindingCharge` double default '0.06',
`DateModified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`CustID`),
KEY `CustID` (`CustID`),
KEY `AllenCust` (`AllenCust`),
KEY `CompanyName` (`CompanyName`),
KEY `AS400ID` (`AS400ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

with the straight forward code:

custda = new MySqlDataAdapter("SELECT * FROM tblcustomers WHERE custid = " + Convert.ToString(custid), custconn);
custbld = new MySqlCommandBuilder(custda);
custda.Fill(custds,"customers");
<snipped>
custds.Tables["customers"].Rows[0]["companyname"] = newname;
custda.Update(custds, "customers");

<dataset is updated just fine, exception thrown on Update>

Here's the UpdateCommand generated from CommandBuilder:

UPDATE `tblcustomers` SET `AS400ID`=?AS400ID, `CompanyName`=?CompanyName, `AllenCust`=?AllenCust, `Address1`=?Address1, `Address2`=?Address2,
`CustCity`=?CustCity, `CustState`=?CustState, `CustZip`=?CustZip, `CustCountry`=?CustCountry, `Min Freight Weight`=?MinFreightWeight,
`MinFreightOverride`=?MinFreightOverride, `MinTrimBoxes`=?MinTrimBoxes, `Terms`=?Terms, `Shipping Method`=?ShippingMethod, `CreditRating`=?CreditRating,
`CreditAppOnFile`=?CreditAppOnFile, `CreditAvailability`=?CreditAvailability, `ActiveDate`=?ActiveDate, `CustomerNotes`=?CustomerNotes,
`GrindingCharge`=?GrindingCharge, `DateModified`=?DateModified WHERE `CustID` <=> ?Original_CustID AND `AS400ID` <=> ?Original_AS400ID AND
`CompanyName` <=> ?Original_CompanyName AND `AllenCust` <=> ?Original_AllenCust AND `Address1` <=> ?Original_Address1 AND `Address2` <=> ?Original_Address2 AND
`CustCity` <=> ?Original_CustCity AND `CustState` <=> ?Original_CustState AND `CustZip` <=> ?Original_CustZip AND `CustCountry` <=> ?Original_CustCountry AND
`Min Freight Weight` <=> ?Original_MinFreightWeight AND `MinFreightOverride` <=> ?Original_MinFreightOverride AND `MinTrimBoxes` <=> ?Original_MinTrimBoxes AND
`Terms` <=> ?Original_Terms AND `Shipping Method` <=> ?Original_ShippingMethod AND `CreditRating` <=> ?Original_CreditRating AND
`CreditAppOnFile` <=> ?Original_CreditAppOnFile AND `CreditAvailability` <=> ?Original_CreditAvailability AND `ActiveDate` <=> ?Original_ActiveDate AND
`CustomerNotes` <=> ?Original_CustomerNotes AND `GrindingCharge` <=> ?Original_GrindingCharge AND `DateModified` <=> ?Original_DateModified;
SELECT `CustID`, `AS400ID`, `CompanyName`, `AllenCust`, `Address1`, `Address2`, `CustCity`, `CustState`, `CustZip`, `CustCountry`, `Min Freight Weight`,
`MinFreightOverride`, `MinTrimBoxes`, `Terms`, `Shipping Method`, `CreditRating`, `CreditAppOnFile`, `CreditAvailability`, `ActiveDate`, `CustomerNotes`,
`GrindingCharge`, `DateModified` FROM `tblcustomers` WHERE (`CustID`=?Original_CustID)

I attempted to simplify the update command object by limiting the first WHERE to just the primary key, and that didn't work either.

I don't know if this is related, but with my new version of MySQL, I need to initially run the command "SET CHARACTER SET utf8;" to get anything to work. I will be attempting this code on a different version of MySQL soon.

IRONICALLY, the command builder works fine in a another program (using nearly identical code) on this simpler table in the same database:

CREATE TABLE `tlkpterms` (

`TermsID` int(11) NOT NULL auto_increment,
`TermsDesc` varchar(50) default NULL,
PRIMARY KEY (`TermsID`),
KEY `TermsID` (`TermsID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1


Hopefully this sheds light somewhere!
MartinJ