Error inserting in Mysql. There is already an open DataReader associated with this Connection which must be closed first

2

What I want is to make a simple INSERT in C #, but I get this error:

  

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

Code to be inserted:

string QueryCon = "SELECT Id_Categoria FROM Categoria where Nombre='" + Categoria + "'";
connection.Close();
connection.Open();
MySqlCommand ConnCombo = new MySqlCommand(QueryCon, connection);
MySqlDataReader reader = ConnCombo.ExecuteReader();
while (reader.Read())
{
    Id_C = int.Parse(reader.GetString("Id_Categoria"));
}

string query = "INSERT INTO compra (Articulo, Categoria,Importancia,Obligatorio,Costo,Descripcion,Adquirir) VALUES('" + Articulo + "', '" + Id_C + "'," + Importancia + ",'no'," + Costo + ",'" + Descripcion + "','No','" + Mes + "')";

if (aprobar==true) {
    //create command and assign the query and connection from the constructor
    MySqlCommand cmd = new MySqlCommand(query, connection);
    //Execute command
    cmd.ExecuteNonQuery(); ------- Error
    connection.Close();
}
    
asked by David 23.03.2017 в 19:17
source

2 answers

3

The error happens because you did not close the reader before executing your insert . To avoid this type of problems, it is very important to take the habit of using blocks using for this type of objects that implement the interface IDisposable :

using(MySqlCommand ConnCombo = new MySqlCommand(QueryCon, connection))
{
    using(MySqlDataReader reader = ConnCombo.ExecuteReader())
    {
        while (reader.Read())
        {
            Id_C = int.Parse(reader.GetString("Id_Categoria"));
        }
    }
}

string query = "INSERT INTO compra (Articulo, Categoria,Importancia,Obligatorio,Costo,Descripcion,Adquirir) VALUES('" + Articulo + "', '" + Id_C + "'," + Importancia + ",'no'," + Costo + ",'" + Descripcion + "','No','" + Mes + "')";

if (aprobar==true) {
    //create command and assign the query and connection from the constructor
    using(MySqlCommand cmd = new MySqlCommand(query, connection))
    {
        //Execute command
        cmd.ExecuteNonQuery();
    }
}

Simply executing .Close() is not enough, because there is still the possibility that an open data reader remains if an exception is thrown. Using the block using , you have guarantees that the resource will be closed when leaving the block.

Additionally, in the corrected code, I intentionally avoided including the code that you have that handles the connection, because I do not agree with it. I do not know because in general there is an infatuation with global connections, but that is extremely dangerous and can also cause the error you receive and others if you happen to move to a multi-threaded environment. It is better that you obtain a separate connection instance each time you need to communicate with the database, and close it immediately afterwards. Usually, ADO.NET providers are responsible for working with a pool of connections without you noticing so that you are not really opening and closing real connections every time and the performance is good.

So the code, really should look like this, if you want to avoid problems:

using(MySqlConnection connection = new MySqlConnection(connectionString))
{
    connection.Open();

    string QueryCon = "SELECT Id_Categoria FROM Categoria where Nombre='" + Categoria + "'";

    using(MySqlCommand ConnCombo = new MySqlCommand(QueryCon, connection))
    {
        using(MySqlDataReader reader = ConnCombo.ExecuteReader())
        {
            while (reader.Read())
            {
                Id_C = int.Parse(reader.GetString("Id_Categoria"));
            }
        }
    }

    string query = "INSERT INTO compra (Articulo, Categoria,Importancia,Obligatorio,Costo,Descripcion,Adquirir) VALUES('" + Articulo + "', '" + Id_C + "'," + Importancia + ",'no'," + Costo + ",'" + Descripcion + "','No','" + Mes + "')";

    if (aprobar==true) {
        //create command and assign the query and connection from the constructor
        using(MySqlCommand cmd = new MySqlCommand(query, connection))
        {
            //Execute command
            cmd.ExecuteNonQuery();
        }
    }
}

And finally, an additional note, because I did not realize before: it is very important to use parameters when building your queries. Never concatenate the values directly in your SQL. Apart from SQL injection security problems, I can understand that you do not care so much depending on the type of project you use, it also avoids problems with quotes that do not escape correctly, helps the server to improve performance, and much more. It is very important to develop that good habit.

    
answered by 23.03.2017 / 19:21
source
3

The answer to your question is in the same error you sent. It says that not since you already have an open reader you can not do anything else until you close it. If you would like to process in parallel you should open another connection.

If not, just do reader.Close() once you finish using it to work.

    
answered by 23.03.2017 в 19:20