MysqlDataTabel Update

MysqlDataTabel Update

Postby AceEmbler » Thu 28 Sep 2006 10:29

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]
AceEmbler
 
Posts: 26
Joined: Fri 04 Aug 2006 09:45

Postby Alexey » Thu 28 Sep 2006 10:55

Please provide us with your tables definitions and translate error message from Polish into English.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby AceEmbler » Thu 28 Sep 2006 11:15

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);
AceEmbler
 
Posts: 26
Joined: Fri 04 Aug 2006 09:45

Postby Alexey » Thu 28 Sep 2006 11:24

What is the error message in English?
How do you try to update table? Please describe in detail.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby AceEmbler » Fri 29 Sep 2006 08:35

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.
AceEmbler
 
Posts: 26
Joined: Fri 04 Aug 2006 09:45

Postby Alexey » Fri 29 Sep 2006 10:06

Send us small test project to reproduce the problem.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby AceEmbler » Tue 03 Oct 2006 07:13

Alexey wrote: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?
AceEmbler
 
Posts: 26
Joined: Fri 04 Aug 2006 09:45

Postby Alexey » Tue 03 Oct 2006 08:29

No, we haven't received your mail. Please re-send it to me directly (AlexeyI at crlab dot com).
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby AceEmbler » Wed 04 Oct 2006 07:53

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


How about now. ??
AceEmbler
 
Posts: 26
Joined: Fri 04 Aug 2006 09:45

Postby Alexey » Wed 04 Oct 2006 09:57

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.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby AceEmbler » Wed 04 Oct 2006 10:32

Alexey wrote: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.
AceEmbler
 
Posts: 26
Joined: Fri 04 Aug 2006 09:45

Postby Alexey » Wed 04 Oct 2006 10:50

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.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby AceEmbler » Thu 05 Oct 2006 11:54

Alexey wrote:

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.
AceEmbler
 
Posts: 26
Joined: Fri 04 Aug 2006 09:45

Postby Alexey » Fri 06 Oct 2006 06:08

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!)
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby AceEmbler » Fri 06 Oct 2006 07:56

Alexey wrote:
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 ??
AceEmbler
 
Posts: 26
Joined: Fri 04 Aug 2006 09:45

Next

Return to dotConnect for MySQL