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");
}

score 4 · Answer 1 · answered Apr 25 '12 at 21:10

4

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.

answered Apr 25 '12 at 21:10

Espen Burud

1,871
10
9

Thanks for posts guys its much apprciated. I have changed my code to what Espen Burud suggested and the code still throws an exception the exception is: '{"Access denied for user 'root'@'localhost' (using password: NO)"}' . This exception is thrown when I pass over the cn.Open(); line. Again I have had a good play and look on the web but can't find a solution. Any help would be much appreciated thanks – user498213 Apr 26 '12 at 09:54
It's many situations that can cause access denied errors, so I think this http://dev.mysql.com/doc/refman/5.1/en/access-denied.html page might help. – Espen Burud Apr 26 '12 at 14:58
@user498213 This question isnt about access problems. You should pick out an answer according to your question. – C4d Jan 23 '15 at 09:17

DOK · Answer 2 · 2012-04-25T21:05:05.793

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();
}

update a mySQL table using C#

2 Answers2

Linked