update a mySQL table using C#

Question

I have written some C# to update a MySql table but I get an exception every time I call the method ExecuteNonQuery(). I have researched this on the web and every solution I find produces the same error. I have an open connection to the database and the update query to the database is written correctly. The code that I have so far come up with is :

public int executeUpdate()
{
  int result = 0;
  if (isConnected)
  {
    try
    {
      MySqlConnection cn = new  MySqlConnection(connection.ConnectionString);
      MySqlCommand cmd = new MySqlCommand();

      cmd.Connection = cn; 
      cmd.CommandText = "UPDATE test SET status_id = 1 WHERE test_id = 1";
      int numRowsUpdated = cmd.ExecuteNonQuery(); 
    }
    catch (MySqlException exSql)
    {
      Console.Error.WriteLine("Error - SafeMySql: SQL Exception: " + query);
      Console.Error.WriteLine(exSql.StackTrace);
    }
    catch (Exception ex)
    {
      Console.Error.WriteLine("Error - SafeMySql: Exception: " + query);
      Console.Error.WriteLine(ex.StackTrace);
    }
  }
  else
    Console.Error.WriteLine("Error - SafeMySql: executeQuery failed. Not connected to DB");
}

Answer 1

Change your try section to the code below:

try
{
    using(MySqlConnection cn = new  MySqlConnection(connection.ConnectionString))
    {        
        MySqlCommand cmd = new MySqlCommand();
        cmd.Connection = cn; 
        cmd.CommandText = "UPDATE test SET status_id = 1 WHERE test_id = 1";
        cn.Open();
        int numRowsUpdated = cmd.ExecuteNonQuery();
        cmd.Dispose(); 
     }
}

The connection must be opened before you execute a command. In the example above the command object will immediately be disposed and the connection object will implcitly be closed and disposed when you leave the using section.

Answer 2

I don't see the connection being opened.

Here is an example from MSDN: even inside a using block, they open the connection explicitly

private static void CreateCommand(string queryString,
    string connectionString)
{
    using (SqlConnection connection = new SqlConnection(
               connectionString))
    {
        SqlCommand command = new SqlCommand(queryString, connection);
        command.Connection.Open();
        command.ExecuteNonQuery();
    }
}

Edit: The principle is the same for MySQL as it is for SQL Server:

public void CreateMySqlCommand(string myExecuteQuery, MySqlConnection myConnection) 
{
  MySqlCommand myCommand = new MySqlCommand(myExecuteQuery, myConnection);
  myCommand.Connection.Open();
  myCommand.ExecuteNonQuery();
  myConnection.Close();
}