MysqlDataTabel Update

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
AceEmbler
Posts: 26
Joined: Fri 04 Aug 2006 09:45

Post by 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 ??

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by 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.

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

Post by 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()


Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by 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();

Post Reply