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.