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

rallez

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

rallez

No one?

AlexDen

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

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

Try

ADOMySQLCommand.Parameters.Item('@PLZ').Value

or

ADOMySQLCommand.Parameters.Item(0).Value

rallez

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