Insert data to MySQL-Server with assembly MySql.Data ADO.NET

rallez
rallez Member Posts: 4
in NAV Three Tier
Hello,
I want to write data to a MySQL-Database with C/AL and assembly MySql.Data.

This code works very fine:
...
ADOMySQLCommand.CommandText := 'INSERT INTO t_ado VALUES(NULL,@PLZ)';
ADOMySQLCommand.Prepare();
ADOMySQLCommand.Parameters.AddWithValue('@PLZ', '99999');
ADOMySQLCommand.ExecuteNonQuery();
...

The code above only creats one new record in the table t_ado.
Next, I want to write new records within a "Repeat - Until" loop.
So I tried this:

...
ADOMySQLCommand.CommandText := 'INSERT INTO t_ado VALUES(NULL,@PLZ)';
ADOMySQLCommand.Prepare();
REPEAT
ADOMySQLCommand.Parameters.AddWithValue('@PLZ', '99999');
--> this is the problem: AddWithValue is the wrong function
--> the right one should be "ADOMySQLCommand.Parameters('@PLZ').Value = 'xxxxx';" like here: https://dev.mysql.com/doc/connector-net/en/connector-net-programming-prepared-preparing.html
ADOMySQLCommand.ExecuteNonQuery();
UNTIL ...


The function .Value isn't available for me. So, whats going wrong? How to fix this?
thx! I'm realiy frustrated

Answers

  • rallez
    rallez Member Posts: 4
    No one?
  • AlexDen
    AlexDen Member Posts: 86
    Hello,

    I think that you should initialize SQL Command inside loop or change parameter value inside loop instead of adding a new one.

    Now it looks like you have initialized the command and inside loop you try to add new parameter each time, but your command has only one parameter. Thus, when you try to add parameter second time you get this error.
  • rallez
    rallez Member Posts: 4
    Yes, you are right! That's exactly the problem. I want to change the paramter inside the loop, but the recommended way with "ADOMySQLCommand.Parameters('@PLZ').Value = 'xxxxx';" is not possible, because ".Value" isn't available... Or I'm wrong?
  • AlexDen
    AlexDen Member Posts: 86
    Try 
    ADOMySQLCommand.Parameters.Item('@PLZ').Value
    or 
    ADOMySQLCommand.Parameters.Item(0).Value
  • rallez
    rallez Member Posts: 4
    ADOMySQLCommand.Parameters.Item('@PLZ').Value = '12345';
    Not working. Error (I hope to write the right words): For no overload of method 'Item' are arguments of type '1' are used

Categories