MysqlDataTabel Update

[AceEmbler]

I'm using this statment to get data from database:

SELECT * FROM cenyIPrezentacja,produkt WHERE cenyIPrezentacja.id_wizyta="1" && cenyIPrezentacja.id_produkt=produkt.id

when I try to Update the table I get this exception:

System.InvalidOperationException was unhandled
Message="Nie można wyświetlić komunikatu o błędzie, ponieważ nie można odnaleźć opcjonalnego zestawu zasobów zawierającego ten komunikat"
StackTrace:
w System.Data.Common.DbCommandBuilder.BuildInformation()
w System.Data.Common.DbCommandBuilder.BuildCache()
w System.Data.Common.DbCommandBuilder.RowUpdatingHandlerBuilder()
w System.Data.Common.DbCommandBuilder.RowUpdatingHandler()
w CoreLab.MySql.MySqlCommandBuilder.e()
w CoreLab.MySql.MySqlDataAdapter.OnRowUpdating()
w System.Data.Common.DbDataAdapter.Update()
w System.Data.Common.DbDataAdapter.UpdateFromDataTable()
w System.Data.Common.DbDataAdapter.Update()
w CoreLab.Common.DbTable.Update()
w PHandlowy.wizytaTableData.UpdateTables()
w PHandlowy.Form1.linkLabelSave_Click()
w System.Windows.Forms.Control.OnClick()
w System.Windows.Forms.LinkLabel.WnProc()
w System.Windows.Forms.Control._InternalWnProc()
w Microsoft.AGL.Forms.EVL.EnterMainLoop()
w System.Windows.Forms.Application.Run()
w PHandlowy.Program.Main()

[edit] And im using mysqldirect net mobile[/edit]

[Alexey]

Please provide us with your tables definitions and translate error message from Polish into English.

[AceEmbler]

Btw There is no problem when it comes to displaying data. Only when i need to Update it Exception occurs.


--
-- Table structure for table `produkt`
--

CREATE TABLE `produkt` (
`id` double NOT NULL auto_increment,
`nazwa` varchar(50) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin2 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `produkt`
--

INSERT INTO `produkt` VALUES (1, 'Nr 27');
INSERT INTO `produkt` VALUES (2, 'Nr 1');
INSERT INTO `produkt` VALUES (3, 'N1 + papier');
INSERT INTO `produkt` VALUES (4, 'Nr 33');
INSERT INTO `produkt` VALUES (5, '2 x nr 1');




--
-- Table structure for table `cenyIPrezentacja`
--

CREATE TABLE `cenyIPrezentacja` (
`id` double NOT NULL auto_increment,
`id_produkt` bigint(20) NOT NULL default '0',
`cena` double NOT NULL default '0',
`dostepnosc` tinyint(1) NOT NULL default '0',
`ilosc` int(11) NOT NULL default '0',
`id_wizyta` bigint(20) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin2 AUTO_INCREMENT=8 ;

--
-- Dumping data for table `cenyIPrezentacja`
--

INSERT INTO `cenyIPrezentacja` VALUES (4, 1, 22, 1, 100, 1);
INSERT INTO `cenyIPrezentacja` VALUES (5, 2, 34.5, 1, 13, 1);
INSERT INTO `cenyIPrezentacja` VALUES (6, 3, 11, 0, 0, 2);
INSERT INTO `cenyIPrezentacja` VALUES (7, 4, 2.45, 1, 10, 2);

[Alexey]

What is the error message in English?
How do you try to update table? Please describe in detail.

[AceEmbler]

The SQL statment that i wrote previously fills MysqlDataTable dtCenyIPrezentacja.


After some changes to the table i call

dtCenyIPrezentacja.Update(); //<-- exception
dtCenyIPrezentacja.Clear();
dtCenyIPrezentacja.Fill();

this exception message is a default message that states that exception message was not found.

[Alexey]

Send us small test project to reproduce the problem.

[AceEmbler]

Send us small test project to reproduce the problem.

I have sent you an email but i didn't get a reply. Can you confirm the delivery?

[Alexey]

No, we haven't received your mail. Please re-send it to me directly (AlexeyI at crlab dot com).

[AceEmbler]

No, we haven't received your mail. Please re-send it to me directly (AlexeyI at crlab dot com).

How about now. ??

[Alexey]

Yes. Debugging your application i've got the error "Dynamic SQL generation is not supported against multiple base tables", which clearly states that you should have set UpdateCommand property of MySqlDataTable component before invoking the Update method.

[AceEmbler]

Yes. Debugging your application i've got the error "Dynamic SQL generation is not supported against multiple base tables", which clearly states that you should have set UpdateCommand property of MySqlDataTable component before invoking the Update method.

Thanks
btw. how did you get this error. ??
And why multiple base table SQL generation is not available.

[Alexey]

how did you get this error. ??

While debugging on the emulator.

And why multiple base table SQL generation is not available.

Multiple base table SQL generation is available, but not dynamically. If you set the UpdateCommand property with this statement:

Code: Select all

UPDATE test.cenyIPrezentacja SET id = :id, id_produkt = :id_produkt, cena = :cena, dostepnosc = :dostepnosc, ilosc = :ilosc, id_wizyta = :id_wizyta WHERE ((id = :Original_id))

you'll have update query for the statement from your first post.

[AceEmbler]

Code: Select all

UPDATE test.cenyIPrezentacja SET id = :id, id_produkt = :id_produkt, cena = :cena, dostepnosc = :dostepnosc, ilosc = :ilosc, id_wizyta = :id_wizyta WHERE ((id = :Original_id))

you'll have update query for the statement from your first post.

Can I update id since it's a primary key ??

consider updating only "dostepnosc" "ilosc" and "cena"
is this statment correct ??

Code: Select all

UPDATE cenyIPrezentacja SET cena=:cena,dostepnosc=:dostepnosc,ilosc=:ilosc WHERE id=:id

becouse it fails when I modify more than one row.

[Alexey]

Code: Select all

UPDATE cenyIPrezentacja SET cena=:cena,dostepnosc=:dostepnosc,ilosc=:ilosc WHERE id=:id

This statement is correct.

it fails when I modify more than one row.

How you can modify more than one row? (id is a primary key!)

[AceEmbler]

Code: Select all

UPDATE cenyIPrezentacja SET cena=:cena,dostepnosc=:dostepnosc,ilosc=:ilosc WHERE id=:id

This statement is correct.

it fails when I modify more than one row.

How you can modify more than one row? (id is a primary key!)

I have connected DataGrid with MysqlDataTable and when i modify more than one row in datagrid, MysqlDataTable fails to Update.

(id is a primary key!)

something is wrong ??