An exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll but was not handled in user code

Question

I keep getting the following error message:

An exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll but was not handled in user code

Additional information: There is already an open DataReader associated with this Connection which must be closed first.

at da.Fill(dtCounts); each time I run my code. Does anyone know the reason for it?

For some reason, S/O won't let me paste my code, so I've uploaded it here: http://pastebin.com/LCnTBVY4

Edit: Here is it:

public static void RefreshPlot()
{        
    string query = "SELECT Count(*) AS count, plot_id FROM booking WHERE postcode=@postcode AND " +
                   "status='open' GROUP BY plot_id";
    var cmd = new MySqlCommand(query, DbConnect.Connection);
    cmd.Parameters.AddWithValue(("@postcode"), DbConnect.Plot);

    var da = new MySqlDataAdapter(cmd);
    var dtCounts = new DataTable();
    da.Fill(dtCounts);

    if (dtCounts.Rows.Count > 0)
    {
        query = "UPDATE plot SET jobs = @jobCount WHERE plot_id = @plotID AND postcode=@postcode;";

        query += "UPDATE plot " +
                 "LEFT JOIN booking " +
                 "ON plot.plot_id = booking.plot_id " +
                 "SET plot.jobs = 0 " +
                 "WHERE plot.postcode=@postcode " +
                 "AND booking.plot_id IS NULL;";

        query += "update plot p " +
                 "inner join " +
                 "(select sum(case when status = 'Open' then 1 else 0 end) cnt, plot_id " +
                 "from booking group by plot_id) p2 on p.plot_id = p2.plot_id " +
                 "set p.jobs = p2.cnt;";

        cmd = new MySqlCommand(query, DbConnect.Connection);
        foreach (DataRow row in dtCounts.Rows)
        {
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue(("@postcode"), DbConnect.Plot);
            cmd.Parameters.AddWithValue("@jobCount", int.Parse(row["count"].ToString()));
            cmd.Parameters.AddWithValue("@plotID", int.Parse(row["plot_id"].ToString()));
            cmd.ExecuteNonQuery();               
        }
    }
    else if ((dtCounts.Rows.Count == 0))
    {
        query = "UPDATE plot SET jobs=0 WHERE postcode=@postcode;";
        cmd = new MySqlCommand(query, DbConnect.Connection);
        cmd.Parameters.AddWithValue(("@postcode"), DbConnect.Plot);
        cmd.ExecuteNonQuery();
    }
}

Answer 1

Try to use the using statement on every new MySqlCommand and Adapter to dispose the resources.

For example:

var dtCounts = new DataTable();
using (var cmd = new MySqlCommand(query, DbConnect.Connection))
{
     cmd.Parameters.AddWithValue(("@postcode"), DbConnect.Plot);

     using (var da = new MySqlDataAdapter(cmd))
     {
          da.Fill(dtCounts);
     }
}

You have an open datareader in your connection which is not closed.

Maybe you have some code elsewhere which uses the same connection. Use the using statement on commands everywhere, then they should be closed.

To be safe you could create a new connection.