-1

This problem is weird and I'm not sure what exactly is causing it. The update query shown in the sample code below works 60% of the time but sometimes it doesn't work at all. There is no exception or error. I tried debugging and when the query doesn't work it simply returns 0 meaning that no rows were updated. However, the row exists and if I try it on phpmyadmin or run this code again it somehow works. I also tried using a simple UPDATE query instead of INSERT ON DUPLICATE KEY UPDATE but still getting the same behavior.

If anyone can suggest me a better approach it'd be very much appreciated.

    MySqlConnection remoteConnection;

    string connectionString = "SERVER=" + server + ";" + "DATABASE=" + db + ";" + "UID=" + username + ";" + "PASSWORD=" + pass + ";";
    remoteConnection = new MySqlConnection(connectionString);
    remoteConnection.Open();
    string query = $"INSERT INTO WebServers (Ip,Status,Error,Visible) VALUES('{serverStatus.Ip}', '{serverStatus.Status}', '{serverStatus.Error}','0') ON DUPLICATE KEY UPDATE Status='{serverStatus.Status}', Error='{serverStatus.Error}', Visible='0'";
    MySqlCommand mySqlCommand = new MySqlCommand(query, remoteConnection);
    int i = mySqlCommand.ExecuteNonQuery();
    remoteConnection.Close();
HN Learner
  • 544
  • 7
  • 19
  • also: to reproduce your bug, some sample data might be helpful, for example one of the rows you're trying to update, and the values you're trying to update it with. – Franz Gleichmann Jul 16 '21 at 06:58
  • Thank you for the advice. It's just that the only person using this app is the person who already has complete access to the database so injection doesn't worry me in this case. But I do appreciate the suggestion :) – HN Learner Jul 16 '21 at 06:58
  • 1
    @HNLearner doesn't really matter. again: there is _no_ reason not to use parameterised statements. and you _should_ be learning to use the right way instead of a method that's frowned upon for basically forever now. (_and_ keep the fever bugs & potentially better performance in mind. _who doesn't want that?_) – Franz Gleichmann Jul 16 '21 at 07:01
  • Thank you. To answer your other question, I don't think data is the problem here since the same data works when tried second time or via phpmyadmin. I even copied the string value of `query` (while debugging) and pasted it in phpmyadmin, it worked there. – HN Learner Jul 16 '21 at 07:03

1 Answers1

-2

try mySqlCommand.Connection.Open(); int i = mySqlCommand.ExecuteNonQuery();

Vytautas L
  • 60
  • 6