MysqlDataTabel Update

Postby AceEmbler » Fri 06 Oct 2006 09:09

I think we have got a little misunderstanding here.

Is it possible to update MySqlDataTable with data obtained from more than one table with Update method ??? Or i have to manually update every changed row ??
AceEmbler
 
Posts: 26
Joined: Fri 04 Aug 2006 09:45

Postby Alexey » Fri 06 Oct 2006 09:43

It is possible to update MySqlDataTable with data obtained from more than one table with Update method, but you have to write your own UpdateCommand. We suggest you writing stored procedure for this purpose.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby AceEmbler » Fri 06 Oct 2006 10:48

Alexey wrote:It is possible to update MySqlDataTable with data obtained from more than one table with Update method, but you have to write your own UpdateCommand. We suggest you writing stored procedure for this purpose.


I don't want to write stored procedure.

Going back to my example:

Code: Select all
//this is my UpdateCommand string

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


//My Parameters declarations (dtCenyIPrezentacja) is MysqlDataTable
            dtCenyIPrezentacja.UpdateCommand.Parameters.Add("id", MySqlType.Int, 0, "id");
            dtCenyIPrezentacja.UpdateCommand.Parameters.Add("orginal_id", MySqlType.Int, 0, "id").SourceVersion = DataRowVersion.Original;
            dtCenyIPrezentacja.UpdateCommand.Parameters.Add("ilosc", MySqlType.Int, 0, "ilosc");
            dtCenyIPrezentacja.UpdateCommand.Parameters.Add("dostepnosc", MySqlType.Int, 0, "dostepnosc");
            dtCenyIPrezentacja.UpdateCommand.Parameters.Add("cena", MySqlType.Double, 0, "cena");


Calling Update if more than one row was modified in datagrid that owns this MysqlDataTable fails.

exception:
(btw can you tell me how to change language of this message becouse i was googling and couldn't find the answer)
Code: Select all
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.DbDataAdapter.UpdatedRowStatusErrors()
    w System.Data.Common.DbDataAdapter.UpdatedRowStatus()
    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.UpdateTable()
    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()

AceEmbler
 
Posts: 26
Joined: Fri 04 Aug 2006 09:45

Postby Alexey » Mon 09 Oct 2006 06:41

The following code works fine:
Code: Select all
      MySqlDataTable mySqlDataTable1 = new MySqlDataTable("SELECT * FROM cenyIPrezentacja,produkt WHERE cenyIPrezentacja.id_wizyta=1 && cenyIPrezentacja.id_produkt=produkt.id", mySqlConnection1);
      mySqlDataTable1.UpdateCommand = new MySqlCommand("UPDATE cenyIPrezentacja SET cena=:cena,dostepnosc=:dostepnosc,ilosc=:ilosc WHERE id=:Original_id", mySqlConnection1);
      mySqlDataTable1.UpdateCommand.Parameters.Add("Original_id", MySqlType.Int, 0, "id").SourceVersion = DataRowVersion.Original;
      mySqlDataTable1.UpdateCommand.Parameters.Add("ilosc", MySqlType.Int, 0, "ilosc");
      mySqlDataTable1.UpdateCommand.Parameters.Add("dostepnosc", MySqlType.Int, 0, "dostepnosc");
      mySqlDataTable1.UpdateCommand.Parameters.Add("cena", MySqlType.Double, 0, "cena");
      mySqlDataTable1.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
      mySqlDataTable1.Connection.Open();
      mySqlDataTable1.Clear();
      mySqlDataTable1.Fill();
      mySqlDataTable1.Rows[0].BeginEdit();
      mySqlDataTable1.Rows[0]["cena"] = 111;
      mySqlDataTable1.Rows[0].EndEdit();
      mySqlDataTable1.Rows[1].BeginEdit();
      mySqlDataTable1.Rows[1]["cena"] = 111;
      mySqlDataTable1.Rows[1].EndEdit();
      mySqlDataTable1.Update();
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Previous

Return to dotConnect for MySQL