error on row update: index -1 for this Parameters

[jchoquet]

I have this error when I use the Update method of the TableAdapter generated for one of my table. Using the bindingNavigator, I click on the disk to save changes to the current row and this error happens all the time.

I use :
Visual Studio 2005 Team edition
CoreLabs component 3.50.12
MySQL 5.0.22 on remote computer

Here is the update query used be the DataAdapter in the TableAdapter:

"UPDATE `Production_Data`.`Inventaire`
SET `# produit` = :__produit,
`Groupe` = :Groupe,
`Description produit` = :Description_produit,
`No de pièce fournisseur` = :No_de_pièce_fournisseur,
`Fournisseur` = :Fournisseur,
`Fabricant` = :Fabricant,
`Location` = :Location,
`Qté stock` = :Qté_stock,
`Qté min` = :Qté_min,
`Qté max` = :Qté_max,
`Qté commande` = :Qté_commande,
`Unité mesure` = :Unité_mesure,
`Délai livraison` = élai_livraison,
`Prix` = :Prix,
`No charge adm` = :No_charge_adm,
`No PO relache` = :No_PO_relache,
`Géré` = :Géré,
`Notes` = :Notes,
`Faire commande` = :Faire_commande,
`Actif` = :Actif
WHERE ((`# produit` = :Original___produit))"

this update query has been auto-generated (along with the 21 parameters). I have dragged the table from the Server Explorer in a dataset generated by the Dataset Wizard.

Does somebody have an idea of what might be the problem? If you need more info, don't hesitate to ask.

Thanks for your help,

Jean

[Alexey]

Please provide us with your table definition.

[jchoquet]

Here is my table definition (generated by phpMyAdmin):

CREATE TABLE `Inventaire` (
`# produit` char(50) NOT NULL,
`Groupe` int(11) default '0',
`Description produit` char(50) default NULL,
`No de pièce fournisseur` char(50) default NULL,
`Fournisseur` double NOT NULL,
`Fabricant` char(50) default NULL,
`Location` char(50) default '0',
`Qté stock` int(11) default '0',
`Qté min` int(11) default '0',
`Qté max` int(11) default '0',
`Qté commande` int(11) default '0',
`Unité mesure` char(50) default NULL,
`Délai livraison` int(11) default '0',
`Prix` double default '0',
`No charge adm` char(50) default NULL,
`No PO relache` char(50) NOT NULL,
`Géré` tinyint(1) default NULL,
`Notes` char(255) default NULL,
`Faire commande` tinyint(1) default '1',
`Actif` tinyint(1) default NULL,
PRIMARY KEY (`# produit`),
KEY `numéro de pièce` (`No de pièce fournisseur`),
KEY `Groupe de produitsInventaire` (`Groupe`),
KEY `LocationInventaire` (`Location`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


The fields "Faire commande" and "Actif" and "Géré", are meant to be used as boolean fields. However, MySQL seems to keep them as tinyint(1) fields. In the "Data sources" tab of the VS 2005 IDE, if I want to use the "drag and drop" feature to put a bound field on a form, and if I want those fields to appear as checkboxes, I have to explicitly change them from System.Int16 to System.Boolean in the dataset designer window. The moment I regenerate the TableAdapter, it reverts back to Int16 though.

The odd symbols and spaces in some field names come from an old "legacy" Access 2000 database that we are converting to MySQL and .NET as a front-end right now. I hope that it's not causing all that trouble.

Thank you for your assistance,

Jean

P.S.: If it can be some help to you, here is the dataset designer generated code for the parameters of the update command:

Me._adapter.UpdateCommand.CommandText = "UPDATE `Production_Data`.`Inventaire` SET `# produit` = :__produit, `Groupe` = :G"& _
"roupe, `Description produit` = :Description_produit, `No de pièce fournisseur` ="& _
" :No_de_pièce_fournisseur, `Fournisseur` = :Fournisseur, `Fabricant` = :Fabrican"& _
"t, `Location` = :Location, `Qté stock` = :Qté_stock, `Qté min` = :Qté_min, `Qté "& _
"max` = :Qté_max, `Qté commande` = :Qté_commande, `Unité mesure` = :Unité_mesure,"& _
" `Délai livraison` = :Délai_livraison, `Prix` = :Prix, `No charge adm` = :No_cha"& _
"rge_adm, `No PO relache` = :No_PO_relache, `Géré` = :Géré, `Notes` = :Notes, `Fa"& _
"ire commande` = :Faire_commande, `Actif` = :Actif WHERE ((`# produit` = :Origina"& _
"l___produit))"
Me._adapter.UpdateCommand.CommandType = System.Data.CommandType.Text
param = New CoreLab.MySql.MySqlParameter
param.ParameterName = "__produit"
param.DbType = System.Data.DbType.AnsiStringFixedLength
param.MySqlType = CoreLab.MySql.MySqlType.[Char]
param.IsNullable = true
param.SourceColumn = "# produit"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New CoreLab.MySql.MySqlParameter
param.ParameterName = "Groupe"
param.DbType = System.Data.DbType.Int32
param.MySqlType = CoreLab.MySql.MySqlType.Int
param.IsNullable = true
param.SourceColumn = "Groupe"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New CoreLab.MySql.MySqlParameter
param.ParameterName = "Description_produit"
param.DbType = System.Data.DbType.AnsiStringFixedLength
param.MySqlType = CoreLab.MySql.MySqlType.[Char]
param.IsNullable = true
param.SourceColumn = "Description produit"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New CoreLab.MySql.MySqlParameter
param.ParameterName = "No_de_pièce_fournisseur"
param.DbType = System.Data.DbType.AnsiStringFixedLength
param.MySqlType = CoreLab.MySql.MySqlType.[Char]
param.IsNullable = true
param.SourceColumn = "No de pièce fournisseur"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New CoreLab.MySql.MySqlParameter
param.ParameterName = "Fournisseur"
param.DbType = System.Data.DbType.[Double]
param.MySqlType = CoreLab.MySql.MySqlType.[Double]
param.IsNullable = true
param.SourceColumn = "Fournisseur"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New CoreLab.MySql.MySqlParameter
param.ParameterName = "Fabricant"
param.DbType = System.Data.DbType.AnsiStringFixedLength
param.MySqlType = CoreLab.MySql.MySqlType.[Char]
param.IsNullable = true
param.SourceColumn = "Fabricant"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New CoreLab.MySql.MySqlParameter
param.ParameterName = "Location"
param.DbType = System.Data.DbType.AnsiStringFixedLength
param.MySqlType = CoreLab.MySql.MySqlType.[Char]
param.IsNullable = true
param.SourceColumn = "Location"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New CoreLab.MySql.MySqlParameter
param.ParameterName = "Qté_stock"
param.DbType = System.Data.DbType.Int32
param.MySqlType = CoreLab.MySql.MySqlType.Int
param.IsNullable = true
param.SourceColumn = "Qté stock"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New CoreLab.MySql.MySqlParameter
param.ParameterName = "Qté_min"
param.DbType = System.Data.DbType.Int32
param.MySqlType = CoreLab.MySql.MySqlType.Int
param.IsNullable = true
param.SourceColumn = "Qté min"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New CoreLab.MySql.MySqlParameter
param.ParameterName = "Qté_max"
param.DbType = System.Data.DbType.Int32
param.MySqlType = CoreLab.MySql.MySqlType.Int
param.IsNullable = true
param.SourceColumn = "Qté max"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New CoreLab.MySql.MySqlParameter
param.ParameterName = "Qté_commande"
param.DbType = System.Data.DbType.Int32
param.MySqlType = CoreLab.MySql.MySqlType.Int
param.IsNullable = true
param.SourceColumn = "Qté commande"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New CoreLab.MySql.MySqlParameter
param.ParameterName = "Unité_mesure"
param.DbType = System.Data.DbType.AnsiStringFixedLength
param.MySqlType = CoreLab.MySql.MySqlType.[Char]
param.IsNullable = true
param.SourceColumn = "Unité mesure"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New CoreLab.MySql.MySqlParameter
param.ParameterName = "Délai_livraison"
param.DbType = System.Data.DbType.Int32
param.MySqlType = CoreLab.MySql.MySqlType.Int
param.IsNullable = true
param.SourceColumn = "Délai livraison"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New CoreLab.MySql.MySqlParameter
param.ParameterName = "Prix"
param.DbType = System.Data.DbType.[Double]
param.MySqlType = CoreLab.MySql.MySqlType.[Double]
param.IsNullable = true
param.SourceColumn = "Prix"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New CoreLab.MySql.MySqlParameter
param.ParameterName = "No_charge_adm"
param.DbType = System.Data.DbType.AnsiStringFixedLength
param.MySqlType = CoreLab.MySql.MySqlType.[Char]
param.IsNullable = true
param.SourceColumn = "No charge adm"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New CoreLab.MySql.MySqlParameter
param.ParameterName = "No_PO_relache"
param.DbType = System.Data.DbType.AnsiStringFixedLength
param.MySqlType = CoreLab.MySql.MySqlType.[Char]
param.IsNullable = true
param.SourceColumn = "No PO relache"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New CoreLab.MySql.MySqlParameter
param.ParameterName = "Géré"
param.DbType = System.Data.DbType.[Byte]
param.MySqlType = CoreLab.MySql.MySqlType.TinyInt
param.IsNullable = true
param.SourceColumn = "Géré"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New CoreLab.MySql.MySqlParameter
param.ParameterName = "Notes"
param.DbType = System.Data.DbType.AnsiStringFixedLength
param.MySqlType = CoreLab.MySql.MySqlType.[Char]
param.IsNullable = true
param.SourceColumn = "Notes"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New CoreLab.MySql.MySqlParameter
param.ParameterName = "Faire_commande"
param.DbType = System.Data.DbType.[Byte]
param.MySqlType = CoreLab.MySql.MySqlType.TinyInt
param.IsNullable = true
param.SourceColumn = "Faire commande"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New CoreLab.MySql.MySqlParameter
param.ParameterName = "Actif"
param.DbType = System.Data.DbType.[Byte]
param.MySqlType = CoreLab.MySql.MySqlType.TinyInt
param.IsNullable = true
param.SourceColumn = "Actif"
Me._adapter.UpdateCommand.Parameters.Add(param)
param = New CoreLab.MySql.MySqlParameter
param.ParameterName = "Original___produit"
param.DbType = System.Data.DbType.AnsiStringFixedLength
param.MySqlType = CoreLab.MySql.MySqlType.[Char]
param.IsNullable = true
param.SourceColumn = "# produit"
param.SourceVersion = System.Data.DataRowVersion.Original
Me._adapter.UpdateCommand.Parameters.Add(param)

[Alexey]

When dropping your table onto the dataset canvas i get significantly different WHERE clause of the Update command. So please send me (AlexeyI at crlab dot com) test project to reproduce the problem; include definition of your all database objects. Do not use third party components.