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

I advise against such public connections like `DbConnect.Plot`, all the more if they are static. Why don't you create the connection instance where you use it, best by using the `using`-statement to ensure that it gets closed as soon as possible? Maybe related: http://stackoverflow.com/questions/9705637/executereader-requires-an-open-and-available-connection-the-connections-curren — Tim Schmelter, Feb 03 '15 at 20:36
Thanks Tim - what is confusing me now is trying to figure out would I access the ConnectionString (in the class DbConnect). How can I do it from the PlotComponent class (as seen above)? I've uploaded the DbConnect class at http://pastebin.com/GMRN0tWq. Not sure if I can actually get it from inside the try catch. Can you please help? — methuselah, Feb 03 '15 at 20:44
@methusaleh: why can't you access the connection-string in that class? It doesn't hurt if it's `pulic static` since you don't change it, although i'd use app-settings which are also cached. But the connection is `static` which i've feared. Don't do that! — Tim Schmelter, Feb 03 '15 at 20:50
Do you mean `public static MySqlConnection Connection`? Why should it not be static? Plus I don't know how to access it (with regards to the connection-string * covers face * — methuselah, Feb 03 '15 at 20:56
I've posted a link in my first comment which hoepfully explains it better. There is also a sample with a non-static connection and the `using`-statement. — Tim Schmelter, Feb 03 '15 at 21:19

loiti · Accepted Answer · 2015-02-03T20:51:51.870

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.

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

1 Answers1